Making Categories with items from database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
sublimenal
Forum Newbie
Posts: 23
Joined: Sun May 16, 2004 2:17 pm
Location: Massachusetts

Making Categories with items from database

Post 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;
 
 }

   ?>
sublimenal
Forum Newbie
Posts: 23
Joined: Sun May 16, 2004 2:17 pm
Location: Massachusetts

Post 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
cent
Forum Newbie
Posts: 16
Joined: Wed Nov 16, 2005 2:23 pm

Post 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; 
} 
?>
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
ablaye
Forum Newbie
Posts: 13
Joined: Sun May 14, 2006 9:26 pm

Post by ablaye »

I agree with everah. That is not very efficient what is being done.
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post 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
sublimenal
Forum Newbie
Posts: 23
Joined: Sun May 16, 2004 2:17 pm
Location: Massachusetts

Post 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
Post Reply