homepage Welcome to WebmasterWorld Guest from 54.243.12.156
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
sorting by multiple field associations
generic




msg:4141995
 3:54 pm on May 27, 2010 (gmt 0)

I have a menu system I'm working on for a restaurant. I'd like to be able to grab menu dishes from mySQL and sort them by different types and associations (by menu_type and further, by menu_category) and I'm hoping there is a way to do this purely in the SQL query - I'm sure there is but I'm a super noob ;)

So here's how I have the relevant tables set up in mySQL:

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


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


MENU_ITEM
=================
MENU_ITEM_ID (auto increment id, primary, unique)
MENU_ITEM_NAME
MENU_ITEM_PRICE
MENU_ITEM_DESCRIPTION
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]



So when a visitor goes to menu.php, it should sort like this:

|- DINNER [db: menu_type]
..|- Appetizers [db: menu_category]
....|- menu item one [db: menu_item]
....|- menu item two [db: menu_item]
..|- Mains [db: menu_category]
....|- menu item three [db: menu_item]
....|- menu item four [db: menu_item]

|- LUNCH [db: menu_type]
..|- Soups [db: menu_category]
....|- menu item five [db: menu_item]
....|- menu item six [db: menu_item]
..|- Salads [db: menu_category]
....|- menu item seven [db: menu_item]
....|- menu item eight [db: menu_item]


and so on through the menu.

Thanks in advance for any help or ideas, it's much appreciated!

-gen

 

Furiat




msg:4142250
 8:04 pm on May 27, 2010 (gmt 0)

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

generic




msg:4142256
 8:15 pm on May 27, 2010 (gmt 0)

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!

Furiat




msg:4142301
 9:10 pm on May 27, 2010 (gmt 0)

;) 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

generic




msg:4145008
 4:18 pm on Jun 1, 2010 (gmt 0)

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?

rocknbil




msg:4145011
 4:23 pm on Jun 1, 2010 (gmt 0)

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

generic




msg:4145019
 4:30 pm on Jun 1, 2010 (gmt 0)

haha oh man...

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

Furiat




msg:4145113
 7:07 pm on Jun 1, 2010 (gmt 0)

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.

generic




msg:4145137
 7:41 pm on Jun 1, 2010 (gmt 0)

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

generic




msg:4145146
 7:51 pm on Jun 1, 2010 (gmt 0)

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.

generic




msg:4145150
 7:57 pm on Jun 1, 2010 (gmt 0)

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!

Furiat




msg:4145151
 7:59 pm on Jun 1, 2010 (gmt 0)

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]

generic




msg:4145154
 8:05 pm on Jun 1, 2010 (gmt 0)

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

Furiat




msg:4145156
 8:08 pm on Jun 1, 2010 (gmt 0)

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.

generic




msg:4145161
 8:19 pm on Jun 1, 2010 (gmt 0)

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

generic




msg:4145812
 6:50 pm on Jun 2, 2010 (gmt 0)

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 :)

Furiat




msg:4145845
 7:38 pm on Jun 2, 2010 (gmt 0)

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. ;)

generic




msg:4148252
 5:25 pm on Jun 7, 2010 (gmt 0)

@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 :)

Furiat




msg:4148415
 9:37 pm on Jun 7, 2010 (gmt 0)

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.

generic




msg:4148894
 3:18 pm on Jun 8, 2010 (gmt 0)

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 :)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved