A query question

PHP
1

I have something I’m am building. To make things simple to understand I will change it all to something everybody knows.

Let’s say I have a website for farmers. One table called farmers_tbl with the fields farmer_id, farmer_name, farmer_product
I have one table with a lot of grocery stores that is called stores_tbl with the following fields: store_id, store_name, store_product

The farmers can send newsletters to all the stores that are selling their product.
So, if a farmer is selling eggs, he can send the newsletter to all stores that sell eggs.


$sql = "SELECT * FROM stores_tbl, farmers_tbl WHERE store_product=farmer_product";
$result = mysql_query( $sql ) or die( mysql_error() );

Then I can send the newsletter to all the stores that want to know about the egg offer from this farmer.

But, a couple of stores don’t want the newsletter from this farmer, so I set up a new table called unsub_tbl with the fields unsub_id, farmer_id, store_id

Now some stores can unsub from only this farmers newsletter, but continue to get the other farmers newsletter.
But, now I’m a little confused. How should I setup the query to check that I will send to all farmers that are selling eggs but not the ones that have unsubscribed to this user?
Any ideas how a query like that should look to work?

2

I have one table with a lot of grocery stores that is called stores_tbl with the following fields: store_id, store_name, store_product

Real world experience suggests that stores sell more than one product. so product to store ( n:n )

Then I can send the newsletter to all the stores that want to know about the egg offer from this farmer.

This infers that there is a relationship between each farmer and each store (n : n)

Sorry to not answer your direct question, but I think you table structure needs looking at.

Here is a first poke before I have to go out:

Using the format:

Table

sample data

// kind of query these tables could supply


products
=======
1 | eggs

// list all products

stores
=====
101 | Safeway Tuscon

// list all stores

farmers
======
22 | Giles

// list all farmers

farmer_product
===========
22 | 1

// which farmers sell eggs?
// Farmer Giles provides eggs

store_product_farmer
===========
101 | 1 | 22 | 'Y'

// Safeway Tuscon is supplied with eggs by Farmer Giles, and does want his newsletter updates ...
3

According to ur query u r selecting all the records from both the table.
if u have to send newsletters to all store on behalf of farmers then select only those stores those sales the eggs. then for each farmer who sell eggs notify the the stores to those stores who sell eggs.

4

CUPS. Well, this was just an example. My tables are far more complicated than this.
I just wanted to know how to check both tables for the data.
I want to know how to write a query that checks if one farmer is selling eggs and also that he didn’t unsubscribe to this users newsletter.
Since he want a newsletter from all the other egg farms. That’s why I built a separate table to hold the unsubscriptions in. But, how do I check both places?

5

So it should be like this first:


$news_product = 'eggs';
$sql = "SELECT * FROM stores_tbl WHERE $news_product=store_product";
$result = mysql_query( $sql ) or die( mysql_error() );

But, how do I check if they unsubscribed from this farmer?

6

well,in this Case i need ur table structures and flow of data u r following with ur tables.

7

So you want to select only those shops that are not present in the unsub table for that farmer?
Do a LEFT OUTER JOIN of the unsub table


SELECT * 
FROM stores_tbl s
INNER JOIN farmers_tbl f
ON s.store_product = f.farmer_product
LEFT OUTER JOIN unsub_tbl u
ON u.farmer_id = f.farmer_id
AND u.store_id = s.store_id
WHERE u.farmer_id IS NULL

The where condition will have the query return only those farmer/shop combinations that aren’t present in the unsub table

8

Thanx guido2004 I guess it is what I’m lookng for. I will look at that. Thanx.

9

For future reference; you’ll get better answers if you give us the actual schemas you’re trying to use. We’re big boys and girls, we can handle it. :wink:

10

StarLion, you are right, but sometimes you don’t want to give away an idea. This was a project I have big plans for. Not sure I want anyone to pick up the idea.
I have a lot of great web ideas, so I try to work with a lot of them and then put together bits and pieces and see if it works the way I want it.
If I would show the real tables, I guess someone could get the idea and figure out that it was something great… :wink: