Please Help

mpar612

I am building an app and need some assistance with constructing an SQL query. My app is for a restaurant. The restaurant has multiple menus. When the user comes to a page for a specific menu I need to pull all item data from multiple database tables. There are multiple categories of items, each item falls under a category. Some categories have items that fall into sub categories.

I posted my database tables and a basic sample of the output below. I know how to do this and it is easy if I create one huge table, but due to the amount of data I am working with I need to make sure that this is put together as efficiently as possible.

Is it possible to combine all of this into one SQL statement? Any thoughts would be greatly appreciated. Thanks in advance.

I have three tables:

items
itemId (key)
itemName
itemDescription
price
menu
category
subCategory (could be '0' if item does not have subCategory)

menus
menuId (key)
menuName
menuDescription

menuCategories
categoryId (key)
category
menu
description

menuSubCategory
subCategoryId (key)
subCategory
menuId
categoryId

I want to print the output on the page in the following format:

Category
[INDENT]itemName, item description, price (if item does not have subCategory)[/INDENT]
[INDENT]subCategory[/INDENT]
[INDENT][INDENT]itemName, item description, price[/INDENT][/INDENT]
[INDENT]subCategory[/INDENT]
[INDENT][INDENT]itemName, item description, price[/INDENT][/INDENT]

Repeat until all items in the query have been printed.

Krik

Not sure how familiar you are with quieries but the "LEFT JOIN" may help you a bunch if you wish to do all the work in a query.

mpar612

Thanks for the response. I am having difficulty with making this query work.

The query does not print anything out to the screen, but when I run the query at the command line it returns the proper results.

I am also a little confused on how to make this print out the entire lists of categories, sub categories and items, per my previous post.

Does anyone have any thoughts on this? Thanks!

Here is the code I used:

$itemsSQL = "SELECT menuCategories.* FROM menuCategories LEFT JOIN menuSubCategories ON menuCategories.categoryId = menuSubCategories.categoryId WHERE menuCategories.menu = '$menu'";
$itemsRows = $db->getAll($itemsSql); 

foreach ($itemsRows as $itemRow) {

echo "<table border=\"0\" width=\"541\"id=\"table29\">
<tr>
<td><b><font face=\"Times New Roman\" color=\"#993333\">$itemRow[category]</font></b></td>
</tr>
<tr>
<td>
<font face=\"Times New Roman\" size=\"2\" color=\"#993333\">$itemRow[description]</font></td>
</tr>
<tr>
<td><span style=\"font-size: 4pt\">&nbsp;</span></td>

</tr>
</table>";
}

Krik

Here is a post on using LEFT JOIN I ran into the other day in these forum. It is likely first time I got my head around how it works exactly.

http://phpbuilder.com/board/showthread.php?t=10357258

If you need more help I will try to take a closer look later at what you have.