SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member philster's Avatar
    Join Date
    Jul 2003
    Location
    Belfast
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question SQL Statement Help?

    Hi,

    Just received Kevin's book on PHP & MySQL and I'm finding it a great help as I try to teach myself some code!!

    I did a wee site for a sandwich bar using UltraDev and Access a few years ago, and I've decided to recode it in PHP/MySQL with Kevin's book to help. I've got everything working, but need help with one wee thing...

    I have two tables: "sarnies" and "category". The "sarnies" table contains the name of the sandwich, the price and the category ID it belongs to, and the "category" table contains the category name and category ID.

    At the minute, you first choose the type of sandwich you want, and he next page shows all sarnies from that category as defined in the querystring. Easy peesy.

    But I want to be able to display the ENTIRE menu, and have it listed as follows....

    Category 1 Name
    Sarnie 1
    Sarnie 2
    Sarnie 3
    Sarnie 4

    Category 2 Name
    Sarnie 1
    Sarnie 2
    Sarnie 3
    Sarnie 4

    and so on...

    My SQL statement to bring back all the relevant data is:


    select * from sarnies
    inner join category
    on category.CatID = sarnies.CatID

    But how do I break it into the little chunks that I need? In my UltraDev days, I'd have been REALLY bad and just done another database query for each category to get back the sarnies in that category. I now know this is naughty, but can't for the life of me think how else to do it. I had thought that the GROUP BY function would have done, but this lumps everything in the same CatID into one result, which isn't what I want.

    Any help would be greatly appreciated.
    Phil

  2. #2
    SitePoint Addict phptek's Avatar
    Join Date
    Jun 2002
    Location
    Wellington, NZ
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Use a flag

    Hi Philster:

    While you're iterating thru your result set you need some way of flagging the loop to say if Category title has been displayed, don't display it again until a different one occurs:

    PHP Code:
     $sql "SELECT THIS FROM THAT LEFT JOIN THIS ON THAT";
    $query mysl_query($sql) or die(mysql_error());
     
    echo 
    "<ul>";
    while(
    $row mysql_fetch_assoc($query))
    {
     
    extract($row);
     if(
    $flag != $row['CatName'])
     {
      echo 
    $row['CatName'];
     }
     
    $flag $row['CatName'];
     echo 
    "<li>".$row['SarnieName']."</li>";
    }
    echo 
    "</ul>"
    This *should* display the category name once and all sarni names underneath it in a bulleted list.

    Good luck!

  3. #3
    SitePoint Member philster's Avatar
    Join Date
    Jul 2003
    Location
    Belfast
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Worked like a charm!!

    Many thanks,
    Phil

  4. #4
    SitePoint Member philster's Avatar
    Join Date
    Jul 2003
    Location
    Belfast
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by phptek
    This *should* display the category name once and all sarni names underneath it in a bulleted list.

    Good luck!
    What if there are no sarnies within the category? The SQL statement works great if there are sandwhiches within the category, but if there aren't it won't display the category name either.

    How would I get it to print the category name, then any sandwiches that are in that category underneath the cat name, and if there aren't any sandwiches in that category, have it still print the cat name and then "No sandwiches at present" underneath??

    Any help would be VERY much appreciated!!

    Phil

  5. #5
    SitePoint Wizard Mincer's Avatar
    Join Date
    Mar 2001
    Location
    London | UK
    Posts
    1,140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by philster
    What if there are no sarnies within the category? The SQL statement works great if there are sandwhiches within the category, but if there aren't it won't display the category name either.

    How would I get it to print the category name, then any sandwiches that are in that category underneath the cat name, and if there aren't any sandwiches in that category, have it still print the cat name and then "No sandwiches at present" underneath??

    Any help would be VERY much appreciated!!

    Phil
    You need to use a LEFT JOIN. This will get all the categories, and if there are no sandwiches, you get a NULL value in the sandwich column. Simply test for the NULL value, and display your message as appropriate.

    Here are 2 SQL join tutorials

    Understanding SQL Joins

    MySQL Table Joins

    Matt.

    Edit:

    Shouldn't this thread be in the SQL forum?

  6. #6
    SitePoint Addict phptek's Avatar
    Join Date
    Jun 2002
    Location
    Wellington, NZ
    Posts
    363
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try reversing the JOIN:

    PHP Code:
    $sql "SELECT s_sarnies.s_sarniesName AS Sanger,s_cats.s_catsName AS CAT 
      FROM s_cats
      LEFT JOIN s_sarnies ON s_catsID=s_sarnies_FK"
    ;
    $query mysql_query($sql) or die(mysql_error());
    echo 
    "<ul>";
    while(
    $row mysql_fetch_assoc($query))
    {
     
    extract($row);
     if(
    $flag != $row['CAT'])
     {
      echo 
    "<br />".$row['CAT'];
     }
     
    $flag $row['CAT'];
     echo 
    "<li>".$row['Sanger']."</li>";
    }
    echo 
    "</ul>"
    This displays an empty bullet under the category with no associated sarnies though. All you need to do is play about a bit and stuff starts to make sense. - Not very XHTML compliant tho....

  7. #7
    SitePoint Member philster's Avatar
    Join Date
    Jul 2003
    Location
    Belfast
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mincer
    You need to use a LEFT JOIN. This will get all the categories, and if there are no sandwiches, you get a NULL value in the sandwich column. Simply test for the NULL value, and display your message as appropriate.
    Yup, that worked. I really should read Kevin's book more thoroughly!!

    Quote Originally Posted by Mincer
    Here are 2 SQL join tutorials

    Understanding SQL Joins

    MySQL Table Joins
    And they're next on the list to read too...

    Thanks,
    Phil


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
  •