** SQL QUERY **

SELECT
type.menu_name as type_name,
cat.menu_cat_name as cat_name,
item.menu_item_name as item_name,
item.menu_item_price as item_price,
item.menu_item_description as item_description,
FROM
menu_item item
inner join menu_category cat on item.menu_cat_id = cat.menu_cat_id
inner join menu_type type on item.menu_id = type.menu_type
ORDER BY type_name, cat_name, item_name ASC

** PHP PROCESSING **

$lasttype = "";
$lastcat = "";
while ($rec = mysql_fetch_assoc($resource))
{
if ($lasttype!=$rec['type_name'])
{
// render the type name here
$lasttype = $rec['type_name'];
$lastcat = "";
}
if ($lastcat!=$rec['cat_name'])
{
// render the category name here
$lastcat = $rec['cat_name'];
}
// render the menu item details here
}

** SIDE NOTES **

Now the key thing is you probably want a customized order of items (Apetizers, then Main course, then Drinks for example) and the only way to achieve this (by my regular user knowledge) is by having an integer value that symbolizes the order. The common practice is to call it a "weight" - "heavier" items will sink to the lower parts of the menu, while "lighter" will drift on top. So Apetizers will have weight 1 (top of the menu), Main course will have weight 2 etc... So let's tweak your database and query a bit:

MENU_TYPE
===============
MENU_ID (auto increment id, primary, unique)
MENU_NAME (breakfast, lunch, dinner, etc)
MENU_TYPE_WEIGHT (int)

MENU_CATEGORY
==================
MENU_CAT_ID (auto increment id, primary, unique)
MENU_CAT_NAME (soup, pasta, steak, etc)
MENU_CAT_WEIGHT (int)

MENU_ITEM
=================
MENU_ITEM_ID (auto increment id, primary, unique)
MENU_ITEM_NAME
MENU_ITEM_PRICE
MENU_ITEM_DESCRIPTION
MENU_ITEM_WEIGHT (int)
MENU_ID (corresponding with menu type, ie, this soup [menu_item] is for the "dinner" menu [menu_type])
MENU_CAT_ID (corresponding with menu category, ie, this soup [menu_item] goes under "appetizers" [menu_category] in the "dinner" menu [menu_type]

And with that setting we'll have a very similar query - only difference will be the ORDER BY clause, which will look like this:

ORDER BY type.menu_type_weight, cat.menu_cat_weight, item.menu_item_weight ASC


PS: As usual I wrote this from memory and no tests were ran so there might be typos, syntax errors and even major flaws. If you come across any problems - consult MySQL tutorials or the helpful people on this forum.

Wow! I'll have to give it a whirl this weekend when I finally get a few hours free again and I'll get back to you. Thanks for the info and the reminder about the ordering ;) Cheers Furiat, thanks again!

;) No probs.
I found a typo in line 10 of the query. Right now:
inner join menu_type type on item.menu_id = type.menu_type
Should be:
inner join menu_type type on item.menu_id = type.menu_id

So I tried out the sql you provided (again, thanks for the help!) and I'm coming up with an error:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home1/ambercra/public_html/saffron/cms/menu_test.php on line 27

Line 27 in my code is "while ($rec = mysql_fetch_assoc($resource))"


Here's the whole thing as I have it:

$query = "SELECT type.menu_name as type_name, cat.menu_cat_name as cat_name, item.menu_item_name as item_name, item.menu_item_price as item_price, item.menu_item_description as item_description FROM menu_item item inner join menu_category cat on item.menu_cat_id = cat.menu_cat_id inner join menu_type type on item.menu_id = type.menu_id ORDER BY type_name, cat_name, item_name ASC";
$lasttype = "";
$lastcat = "";
while ($rec = mysql_fetch_assoc($resource))
{
if ($lasttype!=$rec['type_name'])
{
// render the type name here
$lasttype = $rec['type_name'];
$lastcat = "";
}
if ($lastcat!=$rec['cat_name'])
{
// render the category name here
$lastcat = $rec['cat_name'];
}
// render the menu item details here
}

Any thoughts what could be causing it?

Where's "$resource"? :-)

$resource = mysql_query($query) or die(mysql_error());
while ($rec = mysql_fetch_assoc($resource))
{

The die would have echoed any direct mysql errors to the screen, but you want to remove it in live apps (or add an error handler that doesn't reveal database information.)

haha oh man...

Ok so the error is gone but the code's not showing anything at all now.

Hmmm... Did you by any chance confuse the comments I inserted for real rendering commands?
You know that you have to replace the following three lines with your code, right?

// render the type name here
// render the category name here
// render the menu item details here

I cannot help you with that, cause I don't know how exactly you want to have it shown - and besides my example was to be a skeleton, not a fully made, paid-for project.
Also: do you have data in the database?

If you feel that the questions I ask offend you, bear in mind that you said "Here's the whole thing as I have it" and I'm judging from that perspective.

PS: If you're having trouble with writing the php code for rendering - please go to the PHP forum - I'm sure someone there will help.

No you're absolutely right, my heads just in the game right now. Thanks so much for your help with this :)

So probably more like this then:

$query = "SELECT type.menu_name as type_name, cat.menu_cat_name as cat_name, item.menu_item_name as item_name, item.menu_item_price as item_price, item.menu_item_description as item_description FROM menu_item item inner join menu_category cat on item.menu_cat_id = cat.menu_cat_id inner join menu_type type on item.menu_id = type.menu_id ORDER BY type_name, cat_name, item_name ASC";
$lasttype = "";
$lastcat = "";
$resource = mysql_query($query);
while ($rec = mysql_fetch_assoc($resource))
{
if ($lasttype!=$rec['type_name'])
{
// render the type name here
echo '<p>Menu Type: '. $rec['type_name'] .'</p>';

$lasttype = $rec['type_name'];
$lastcat = "";
}
if ($lastcat!=$rec['cat_name'])
{
// render the category name here
echo '<p>Menu Category: '. $rec['cat_name'] .'</p>';
$lastcat = $rec['cat_name'];
}
// render the menu item details here
echo '<p>'. $rec['menu_item_name'] .'</p>';
}

This will pull up one menu and the associated categories but no menu items.

Correction, it pulls up one menu and does display all the items ($rec['menu_item_name'] was a typo). So it should just be a matter of iterating the menu types through a loop and using this code to parse the associated data right?

This is brilliant, I just don't quite understand SQL yet - something to work on I guess. Thanks again for the help, I really appreciate it!

I aliased the query: (...) item.menu_item_name as item_name (...)
So you will have to replace:
echo '<p>'. $rec['menu_item_name'] .'</p>';
With:
echo '<p>'. $rec['item_name'] .'</p>';

EDIT:
Since you figured out the above...
The thing is the loop you are currently using will iterate over ALL types and ALL categories in one go, so no need to add more loops.
To rephrase - currently the data you get from the database will look like this:

Breakfast | Main course | Egg omlette
Breakfast | Main course | Egg toast
Dinner | Apetizers | Salad
Dinner | Apetizers | Shrimp
Dinner | Apetizers | Garlic toast
Dinner | Main course | Steak
Dinner | Main course | Fish soup

And the loop will go through all of this and display it.

[edited by: Furiat at 8:06 pm (utc) on Jun 1, 2010]

Hmm, my database has a full set of test data in it but I'm still only pulling up one menu type (the first entry in the menu_type table)...

Check the data.
Doublecheck the data.
It is possible that you have assigned all the menu items with one menu type. The thing is, which I should have said at the very begining, that Types and Categories which have no menu items WILL NOT DISPLAY. If you want those that's gonna be tougher.

Yup, data was wrong *ashamed*. Yet again, my error. I should really just take a break and have a go at it again tonight LOL

It's all working as intended, thank you SO much. I'm amazed at that query, it's pretty complex but it works exactly as intended which is badass. I'll add the ordering in tonight or tomorrow and plug the sorting into jquery to slickify it.

Thanks again Furiat, I wish I could buy you a beer! ;)

Furiat, I know this is pretty much resolved at this point but I just wanted to say thanks again - I've got the ordering all sorted out (no pun intended) and everything is working like a charm! Cheers for all the help, the good advice and the patience with me :)

Ha! No problem.
It wasn't actually that hardcore of a problem, but I was glad to help. And the real genious minds on this forum would have probably ignored such a petty problem. ;)

@Furiat, I'm hoping maybe you can lend me another hand regarding the use of JOIN. When I display all the menu items at once, it creates sorting issues among the dishes and the list becomes a bit long and unwieldy. It would be much better to pare down the list so it only displays (and sorts by) one menu_type at a time. For example, when the output page, say menu.php, is opened the user can choose which menu they want to view and I could just have it use the appropriate query depending on their choice.

Any chance you can help me out again? I'm having a bit of trouble wrapping my head around how I'd need to JOIN the tables.

Thanks in advance :)

Actually I don't think you need JOINS to achieve what you want. I'm guessing you want to select a single menu type, correct?

If that's the case then a simple WHERE is all it takes.
Simply after all the joins and before ORDER BY add:
WHERE type.menu_id = X

A simple query to get all the menu types and an HTML dropdown selector is surely within your reach so I won't talk about those.
One thing that's very important is that you have to process the input. So if your dropdown is named 'type', then...

$type = 1;
if (isset($_POST['type'))
{
$type = (int) $_POST['type'];
}
$query = "SELECT type.menu_name as type_name, cat.menu_cat_name as cat_name, item.menu_item_name as item_name, item.menu_item_price as item_price, item.menu_item_description as item_description FROM menu_item item inner join menu_category cat on item.menu_cat_id = cat.menu_cat_id inner join menu_type type on item.menu_id = type.menu_id WHERE type.menu_id = $type ORDER BY type_name, cat_name, item_name ASC";

Of course your query might look different now, so simply import the change I made into your current version. The "$type = 1" is simply a default value. Choose whatever suits your needs.
The "$type = (int) $_POST['type']" line is to prevent SQL injection. I won't go into details on how it works - all you need to know is that without this kind of protection a malicious user can gain full control over your database and mess it up to the point of no recovery (or simply delete it for kicks and giggles). So whenever you're accepting integer values from the oustide - it's always a good idea to forcefully convert them to integers in PHP befor using them in SQL queries.
Of course not only integers are dangerous - all values from the outside have to be checked. Look around and I'm sure you'll find some good tutorials on protecting your queries from SQL injection.

Thanks again Furiat, that works great and I appreciate the advice. I believe I've got all the output working as intended now. You've been a big help! Cheers :)