SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    A query question

    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.
    PHP Code:
    $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. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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

    Code:
    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. #3
    SitePoint Enthusiast rajeev13's Avatar
    Join Date
    Nov 2012
    Location
    New Delhi,India
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by brad62 View Post
    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.
    PHP Code:
    $sql "SELECT * FROM stores_tbl, farmers_tbl WHERE store_product=farmer_product";
    $result mysql_query$sql ) or die( mysql_error() ); 
    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. #4
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    So it should be like this first:

    PHP Code:
    $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. #6
    SitePoint Enthusiast rajeev13's Avatar
    Join Date
    Nov 2012
    Location
    New Delhi,India
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by brad62 View Post
    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?
    well,in this Case i need ur table structures and flow of data u r following with ur tables.

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,509
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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
    Code:
    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. #8
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Thanx guido2004 I guess it is what I'm lookng for. I will look at that. Thanx.

  9. #9
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    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.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  10. #10
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    sweden
    Posts
    646
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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... ;-)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •