Page 1 of 1

Making Categories with items from database

Posted: Tue May 16, 2006 9:10 am
by sublimenal
Hello,

I am in some great need of help,

I am making a menu administration panel for a restaurant and storing the contents of the menu in a database. I have the following tables:

menu
menu_cat
menu_item

What I'm trying to do is basicly make it look like a regular menu, You have the categories and under each category there will be menu items. like so:

Breakfast

eggs ----- 3.00
toast ----- 3.00
frenchtoast ----3.00

Lunch

sandwich ----- 3.00
something ----- 3.00
hotdog ----3.00

Dinner

ham ----- 3.00
steak----- 3.00
fish ----3.00



But right now the way I have it its not getting categorized, its display like so:

Code: Select all

http://bluewaterinn.dlgresults.com/menu.php?menu=4
which is giving me the catname with each item name and not putting all the same items under one category. I know my code is wrong but im unsure of a way to approach this. Here is the current code I am using to display what I have, Any help would be greatly appreciated. Thanks!

Code: Select all

<?php
 if (isset($menu)) {
  
		
		  $query2 = $database->query("SELECT * FROM menu_cat WHERE menu = '$menu'");
	while ($list2 = mysql_fetch_array($query2)) {
					
						$catid = stripslashes($list2['id']);
						$catname = stripslashes($list2['name']);
						$catmenu = stripslashes($list2['menu']);
						$description = stripslashes($list2['description']);
						$cats .= '<h1>'.$catname.'</h1>
									<p><em>'.$description .'</em></p>';
   	
			
			$query3 = $database->query("SELECT * FROM menu_item WHERE menu = '$menu' and cat = '$catid'");
	while ($list3 = mysql_fetch_array($query3)) {
						
						$itemid = stripslashes($list3['id']);
						$itemname = stripslashes($list3['name']);
						$itemmenu = stripslashes($list3['menu']);
						$itemdescription = stripslashes($list3['description']);
						$itemprice = stripslashes($list3['price']);
						$itemfeature = stripslashes($list3['feature']);
						$itemcat = stripslashes($list3['cat']);
						$items = '<h1>'.$itemname.'</h1>
									<p>'.$itemdescription.'</p>
									<p>'.$itemprice.'</p>';
									
							$parsecat .= '<h1>'.$catname.'</h1>
											'.$items.'';
							  }
						}
		
		
		
		

  
	echo $parsecat;


  
  
}else {

 echo $menus;
 
 }

   ?>

Posted: Tue May 16, 2006 10:19 am
by sublimenal
Also, another idea would be to display the category name only once maybe with a if statement? Right now for every item it displays the category name over the item not sure if this is possible?

if category name has been displayed already then dont display again


Thanks

Posted: Tue May 16, 2006 7:12 pm
by cent
Hi sublimenal,

The problem is that you're echoing the catname in the item loop. Simply take it out like below and you should be fine.

Best,
Cent.

Code: Select all

<?php 

if (isset($menu)) { 

$query2 = $database->query("SELECT * FROM menu_cat WHERE menu = '$menu'"); 

while ($list2 = mysql_fetch_array($query2)) 
{
	$catid = stripslashes($list2['id']); 
	$catname = stripslashes($list2['name']); 
	$catmenu = stripslashes($list2['menu']); 
	$description = stripslashes($list2['description']); 
	
	echo $catname."<br/><br/>";
	
	$query3 = $database->query("SELECT * FROM menu_item WHERE menu = '$menu' and cat = '$catid'"); 
	while ($list3 = mysql_fetch_array($query3)) 
	{
		$itemid = stripslashes($list3['id']); 
		$itemname = stripslashes($list3['name']); 
		$itemmenu = stripslashes($list3['menu']); 
		$itemdescription = stripslashes($list3['description']); 
		$itemprice = stripslashes($list3['price']); 
		$itemfeature = stripslashes($list3['feature']); 
		$itemcat = stripslashes($list3['cat']); 
		$items = '<h1>'.$itemname.'</h1> 
		<p>'.$itemdescription.'</p> 
		<p>'.$itemprice.'</p>';
		
		echo $items;

		} 
	} 
}else { 
	echo $menus; 
} 
?>

Posted: Tue May 16, 2006 7:54 pm
by RobertGonzalez
Have you thought of using two queries instead of a bunch of queries within a loop?

Code: Select all

<?php
if (isset($menu)) {
    $cat_list = array();
    $item_list = array();
    $sql = $database->query("SELECT * FROM menu_cat WHERE menu = '$menu'");
    while ($row = mysql_fetch_array($sql))
    {
        $cat_list[] = $row;
    }
    $cat_count = count($cat_list);

    // If your DB abstraction doesn't do it, free your result here
    $sql = $database->query("SELECT * FROM menu_item WHERE menu = '$menu' and cat = '$catid'");
    while ($row = mysql_fetch_array($sql))
    {
        $item_list[] = $row;
    }
    $item_count = count($item_list);

    for ($a = 0; $a < $cat_count; $a++)
    {
        $catid = stripslashes($cat_list[$a]['id']);
        echo $cat_list[$a]['name'] . "<br/><br/>";
       
        for ($b = 0; $b < $item_count; $b++)
        {
            if ($item_list[$b]['cat'] == $catid)
            {
                echo "<h1>" . stripslashes($item_list[$b]['name']) . "</h1>\n";
                echo "<p>" . stripslashes($item_list[$b]['description']) . "</p>\n";
                echo "<p>" . stripslashes($item_list[$b]['price']) . "</P>\n";
            }
        }
    }
} else {
    echo $menus;
}
?> 
This method might be a little slower (not noticable I would imagine) and it hits the DB twice, instead of God knows how many times within the fetch_array while loop. Just a suggestion.

Posted: Tue May 16, 2006 9:00 pm
by ablaye
I agree with everah. That is not very efficient what is being done.

Posted: Tue May 16, 2006 9:16 pm
by bdlang
Hmm. I see what you're trying to do, and I think you'd be much better off using a proper JOIN rather than even looping through nested arrays, e.g.

Code: Select all

SELECT
 mc.id AS menu_cat_id
 , mc.name AS menu_cat_name
 , mc.menu AS menu_cat_menu
 , mc.description AS menu_cat_description
 , mi.id AS menu_item_id 
 , mi.name AS menu_item_name
 , mi.menu AS menu_item_menu
 , mi.description AS menu_item_description
 , mi.price AS menu_item_price
 , mi.feature AS menu_item_feature
 , mi.cat AS menu_item_cat
FROM menu_cat AS mc
INNER JOIN menu_item AS mi
 ON (mi.cat = mc.id AND mi.menu = mc.menu)
WHERE mc.menu= {$menu}
;
Single query, use a little logic in your while() loop when retrieving the data.
Fiddle with the query a bit in MySQL and see if it returns the resultset you're expecting.
(link): MySQL JOIN syntax

Posted: Wed May 17, 2006 8:28 am
by sublimenal
Thank you everyone for your posts, its working now :) I cant believe the mistake was that little i spent 2 days easily on this problem lol. Well I appreciate the help