SOLVED Making Categories with items from database

asleboeuf · May 16, 2006

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]

haydndup · May 16, 2006

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.

asleboeuf · May 16, 2006

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

haydndup · May 16, 2006

[!--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

asleboeuf · May 16, 2006

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

ryanlwh · May 16, 2006

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]

asleboeuf · May 16, 2006

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]

kir10s · May 16, 2006

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)

asleboeuf · May 16, 2006

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]

.josh · May 16, 2006

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.

kir10s · May 16, 2006

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

asleboeuf · May 18, 2006

Thanks everyone for your help, The problem has been solved. :)

Sign In

**SOLVED** Making Categories with items from database

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information

SOLVED Making Categories with items from database