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
Category 2 Name
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.