SQL Statement Help?

[philster]

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

[phptek]

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:

 $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!

[philster]

Worked like a charm!!

Many thanks,
Phil

[philster]

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

[Mincer]

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?

[phptek]

Try reversing the JOIN:

$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....

[philster]

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!!

Here are 2 SQL join tutorials

Understanding SQL Joins

MySQL Table Joins

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

Thanks,
Phil