Jump to content

Archived

This topic is now archived and is closed to further replies.

asleboeuf

**SOLVED** Making Categories with items from database

Recommended Posts

Hello,

Great website you have here :)

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]http://bluewaterinn.dlgresults.com/menu.php?menu=4[/code]

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]<?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;

}

   ?>[/code]

Share this post


Link to post
Share on other sites
If i were you, i would keep all the data in the same table like so:

[b]menu_item | menu_cat | item_price[/b]
eggs | breakfast | 3.00
sandwhich | lunch | 3

Something like that. Then fetch everything from the db like so:

when item breakfast is clicked:

[code]
$menu_cat = $_GET['menu_cat'];
$query = $database->query("SELECT * FROM menu WHERE menu_cat = '$menu_cat'");
[/code]

that will fetch everyting that has 'breakfast' as the menu category. Hope this helps.

Share this post


Link to post
Share on other sites
Well the problem with that is the category is part of a menu and the menu as well as the category has its own description and image.


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

Share this post


Link to post
Share on other sites
[!--quoteo(post=374301:date=May 16 2006, 05:21 PM:name=AnthonyL)--][div class=\'quotetop\']QUOTE(AnthonyL @ May 16 2006, 05:21 PM) [snapback]374301[/snapback][/div][div class=\'quotemain\'][!--quotec--]
if category name has been displayed already then dont display again
[/quote]
You would need to use some sort of a loop:

[code]
<?php
echo 'Breakfast';

$result = $database->query("SELECT * FROM menu WHERE menu_cat = 'breakfast'");
while ($row = mysql_fetch_array($result)) {

//echo the menu stuff here
echo $row['menu_item'];
//echo rest of the info, etc

}
?>
[/code]

Then just repeat that process for lunch and dinner

Share this post


Link to post
Share on other sites
Sorry i should've said, the Categories ( breakfast, lunch, dinner) are being pulled from a database because breakfast lunch and dinner wont always be constant. So the category is $catname

Does that stil work with your code?


Thanks for the replies man :)

Share this post


Link to post
Share on other sites
you should go for a join to minimize the number of queries.

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] * [color=green]FROM[/color] [color=orange]menu_cat[/color] [color=green]AS[/color] mc [color=green]JOIN[/color] menu_item [color=green]AS[/color] mi ON mc.id[color=orange]=[/color]mi.menu_cat [color=green]WHERE[/color] mc.menu[color=orange]=[/color][color=red]'$menu'[/color] [color=green]ORDER BY[/color] menu_cat [!--sql2--][/div][!--sql3--]

Then, loop through the records with if/else
[code]$prevCat = '';
while($row = mysql_fetch_assoc($query))
{
    if($prevCat != $row['menu_cat']) echo $row['menu_cat'];
    * * *

    $prevCat = $row['menu_cat'];
}[/code]

Share this post


Link to post
Share on other sites
hmm I sat here for about an hour trying to figure the solution out im affraid i havent been able to :(

I'm getting this error:
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]SELECT * FROM menu_cat AS mc JOIN menu_item AS mi ON mc.id=mi.menu_cat WHERE mc.menu='4' ORDER BY menu_catcould not be completed.
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/blue/public_html/menu2.php on line 47[/quote]


this is what i have for code now:

Any idea? thanks again :)

[code]    <?php
if (isset($menu)) {
  
  
        
        $query2 = $database->query("SELECT * FROM menu_cat AS mc JOIN menu_item AS mi ON mc.id=mi.menu_cat WHERE mc.menu='$menu' ORDER BY menu_cat");
    $prevCat = '';
    while ($list2 = mysql_fetch_array($query2)) {
                    
                        //display from menu_cat
                        $catid = stripslashes($list2['mc.id']);
                        $catname = stripslashes($list2['mc.name']);
                        $catmenu = stripslashes($list2['mc.menu']);  //same as $itemmenu
                        $description = stripslashes($list2['mc.description']);
                        
                        //display from menu_item
                        $itemname = stripslashes($list2['mi.name']);
                        $itemmenu = stripslashes($list2['mi.menu']);  //same as $catmenu
                        $itemdescription = stripslashes($list2['mi.description']);
                        $itemprice = stripslashes($list2['mi.price']);
                        $itemfeature = stripslashes($list2['mi.feature']);
                        $itemcat = stripslashes($list2['mi.cat']);
                        
                         if($prevCat != $list2['menu_cat']) {
                        
                        
                         echo $list2['menu_cat'];

                            $prevCat = $list2['menu_cat'];    
                         }

                            $parsecat .= '<h1>'.$catname.'</h1>
                                    <h2>'.$itemname.'</h2>
                                    <p>'.$itemdescription.'</p>
                                    <p>'.$itemprice.'</p>';
                                    
                        
                        }
        
        
        
        

  
    echo $parsecat;



}else {

echo $menus;

}

   ?>[/code]

Share this post


Link to post
Share on other sites
I think your error message is saying you do not have a connection established to your database. Look at your query on line 47, it should end with something like mysql_query($sql, $conn)

Share this post


Link to post
Share on other sites
No, i have a proper connection. You can see with the full line of code im using a database class which works fine for the rest of the site.

[code]<?php

    require('includes/config.php');
    require('core/database.php');

    $database = new database();
    $database->connect();
    
              $query = $database->query("SELECT * FROM menu ORDER by name");
    while ($list = mysql_fetch_array($query)) {
        $menuid = stripslashes($list['id']);
        $name = stripslashes($list['name']);
        $type = stripslashes($list['type']);
        $menus .= '<li><a href="menu2.php?menu='.$menuid.'">'.$name.'</a></li>';
    
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title><?php echo $title; ?>Blue Water Inn</title>
<meta name="description" content="<?php echo $description; ?>" />
<link href="main.css" rel="stylesheet" type="text/css" />
<link rel="stylesheet" href="sIFR-screen.css" type="text/css" media="screen" />
<link rel="stylesheet" href="sIFR-print.css" type="text/css" media="print" />
<script src="sifr.js" type="text/javascript"></script>
<script src="sifr-addons.js" type="text/javascript"></script>
</head>
<body>
<div id="wrapper">
  <div id="header">
    <div id="top"></div>
    <div id="innerheader"> <img src="images/headercenter.jpg" alt="Blue Water Inn"/> </div>
    <div id="menu">
      <?php include 'includes/menu.php'; ?>
    </div>
  </div>
  <div id="content">
    <?php
if (isset($menu)) {
  
  
        
        $query2 = $database->query("SELECT * FROM menu_cat AS mc JOIN menu_item AS mi ON mc.menu = mi.menu WHERE mc.menu='$menu'");
    $prevCat = '';
    while ($list2 = mysql_fetch_array($query2)) {
                    
                        //display from menu_cat
                        $catid = stripslashes($list2['mc.id']);
                        $catname = stripslashes($list2['mc.name']);
                        $catmenu = stripslashes($list2['mc.menu']);  //same as $itemmenu
                        $description = stripslashes($list2['mc.description']);
                        
                        //display from menu_item
                        $itemname = stripslashes($list2['name']);
                        $itemmenu = stripslashes($list2['menu']);  //same as $catmenu
                        $itemdescription = stripslashes($list2['description']);
                        $itemprice = stripslashes($list2['price']);
                        $itemfeature = stripslashes($list2['feature']);
                        $itemcat = stripslashes($list2['cat']);
                        
                         if($prevCat != $list2['menu_cat']) {
                        
                        
                         echo $list2['menu_cat'];

                            $prevCat = $list2['menu_cat'];    
                         }

                            $parsecat .= '
                                    <h2>'.$itemname.'</h2>
                                    <p>'.$itemdescription.'</p>
                                    <p>'.$itemprice.'</p>';
                                    
                        
                        }
        
        
        
        

  
    echo $parsecat;



}else {

echo $menus;

}

   ?>
  </div>
  <div id="footer">
    <?php include 'includes/footer.php'; ?>
  </div>
</div>
<script type="text/javascript">
//<![CDATA[
if(typeof sIFR == "function"){
    sIFR.replaceElement(named({sSelector:"body h1", sFlashSrc:"francine.swf", sColor:"#7179a9", sLinkColor:"#485819", sBgColor:"#FFFFFF", sHoverColor:"#000000", nPaddingTop:0, nPaddingBottom:0, sFlashVars:"0"}));
        
    sIFR.replaceElement(named({sSelector:"body h2", sFlashSrc:"francine.swf", sColor:"#7179a9", sLinkColor:"#485819", sBgColor:"#FFFFFF", sHoverColor:"#000000", nPaddingTop:0, nPaddingBottom:0, sFlashVars:"0"}));
    
    IFR.replaceElement(named({sSelector:"houses h2", sFlashSrc:"francine.swf", sColor:"#7179a9", sLinkColor:"#485819", sBgColor:"#FFFFFF", sHoverColor:"#000000", nPaddingTop:0, nPaddingBottom:0, sFlashVars:"0"}));
    
    sIFR.replaceElement(named({sSelector:"body h3", sFlashSrc:"francine.swf", sColor:"#7179a9", sLinkColor:"#485819", sBgColor:"#FFFFFF", sHoverColor:"#000000", nPaddingTop:0, nPaddingBottom:0, sFlashVars:"0"}));
    

};
//]]>
</script>
</body>
</html>
[/code]


Share this post


Link to post
Share on other sites
well, that error means that your query string is wrong. first thing you should do is check to make sure all of your column/table names are spelled right.

Share this post


Link to post
Share on other sites
I just encountered the same error message.

I echo'd the query string, so I could see it exactly as the code saw it.

Then I copied and pasted the code into the sql window of my phpAdmin panel. It pointed out the mistakes quite quickly

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.