SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2002
    Location
    Texas
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL query/join question

    I have a question about the best way to join data from two tables. Let's say you have two tables - one of shoe merchants and one of shoe brands - and you want to generate a list of stores in one column with brands stocked by that store in the next column.

    The tables would look like this:

    Shoe Merchants:
    id merchant
    1 Foot Locker
    2 Foot Action
    3 Joe's Palace of Shoes
    etc.

    Shoe Brands:
    id brand
    1 Nike
    2 Reebok
    3 New Balance
    etc.

    Brand Lookup:
    merchant_id brand_id
    1 1
    1 2
    1 3
    2 2
    3 1
    3 3
    etc.

    So how do you generate a list that looks like this:

    Store | Brands Stocked
    Foot Locker | Nike, Reebok, New Balance
    Foot Action | Reebok
    Joe's Palace of Shoes | Nike, New Balance

    In the application I'm working on, I have one query for the stores, but for each store I am doing a separate query for which brands are used in that store. Not a big deal if there's only a few stores, but if there are 30 stores, that's n+1 or 31 queries ... does anyone know of a better way to do this?

    Thanks!

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can do like this:
    select m.merchant, b.brand
    from merchants m
    left join lookup l on m.id = l.merchant_id
    left join brands b on l.brand_id = b.id
    order by 1

    Result:
    merchant, brand
    Foot Action, Reebok
    Foot Locker, Nike
    Foot Locker, Reebok
    Foot Locker, New Balance
    Joe's Palace of Shoes, Nike
    Joe's Palace of Shoes, New Balance

    ...and then you have to check for each row in the result if
    1. brand is null => write nothing in the brand column
    2.1 merchant is the same as on the previous row => add brand on this row
    2.2 merchant is not the same => begin writing new row


    An alternative is this:
    First get all brand names, and build the string
    "sum(case b.brand when 'Nike' then 1 else 0 end) as 'Nike',
    sum(case b.brand when 'New Balance' then 1 else 0 end) as 'New Balance',
    sum(case b.brand when 'Reebok' then 1 else 0 end) as 'Reebok'"
    Then execute the query
    select m.merchant, $the_string_you_just_built
    from merchants m
    left join lookup l on m.id = l.merchant_id
    left join brands b on l.brand_id = b.id
    group by m.merchant
    order by 1

    Result:
    merchant, Nike, New Balance, Reebok
    Foot Action, 0, 0, 1
    Foot Locker, 1, 1, 1
    Joe's Palace of Shoes, 1, 1, 0

    The first alternative is only one db query but a lot of extra php coding, the second alternative is two db queries, but you get a nice grid to display

  3. #3
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Of course you should replace 1 with an image of the shoe brand, and 0 with a transparent image, if you choose my second alternative

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2002
    Location
    Texas
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks jofa. I'll give these a try and let you know how they work out.

  5. #5
    SitePoint Enthusiast
    Join Date
    Apr 2002
    Location
    Texas
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    jofa,

    I tried a variation of your first alternative ... it works great. I guess this was more a PHP question for me than a MySQL question - I knew how to join the tables, but I didn't know what to do when you would have several rows with the same store name repeated. Thanks for the help.

    Here's the code, in case it will help anybody:

    PHP Code:
    $query "select m.id, m.merchant, b.brand
    from merchants m
    left join brand_lookup l on m.id = l.merchant_id
    left join brands b on l.brand_id = b.id
    order by merchant"
    ;

    $result = @mysql_query($query);

    print(
    "<table>\n");
    $ids = array();
    $i 1;
    while (
    $row mysql_fetch_array($result)) {
        if (
    count($ids) == 0) {
            
    $ids[] = $row['id'];
            print(
    "<tr>\n<td>" $row['merchant'] . "</td>\n<td>" $row['brand']);
        } elseif (
    in_array($row['id'], $ids)) {
            print(
    ", " $row['brand']);
        } elseif (!
    in_array($row['id'], $ids)) {
            
    $ids[] = $row['id'];
            print(
    "</td>\n</tr>\n");
            print(
    "<tr>\n<td>" $row['merchant'] . "</td>\n<td>" $row['brand']);
        }
        if (
    $i == mysql_num_rows($result)) {
            print(
    "</td>\n</tr>\n");
        }
        
    $i++;
    }
    print(
    "</table>\n"); 


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
  •