Jump to content


Photo

**SOLVED** Making Categories with items from database


  • Please log in to reply
11 replies to this topic

#1 asleboeuf

asleboeuf
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 16 May 2006 - 01:09 PM

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:

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!

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

   ?>


#2 haydndup

haydndup
  • Members
  • PipPipPip
  • Advanced Member
  • 42 posts
  • LocationJohannesburg, South Africa

Posted 16 May 2006 - 02:34 PM

If i were you, i would keep all the data in the same table like so:

menu_item | menu_cat | item_price
eggs | breakfast | 3.00
sandwhich | lunch | 3

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

when item breakfast is clicked:

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

that will fetch everyting that has 'breakfast' as the menu category. Hope this helps.
[!--coloro:#660000--][span style="color:#660000"][!--/coloro--]The Gene Pool Could Use A Little Chlorine[!--colorc--][/span][!--/colorc--]

#3 asleboeuf

asleboeuf
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 16 May 2006 - 03:21 PM

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

#4 haydndup

haydndup
  • Members
  • PipPipPip
  • Advanced Member
  • 42 posts
  • LocationJohannesburg, South Africa

Posted 16 May 2006 - 03:41 PM

[!--quoteo(post=374301:date=May 16 2006, 05:21 PM:name=AnthonyL)--][div class=\'quotetop\']QUOTE(AnthonyL @ May 16 2006, 05:21 PM) View Post[/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:

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

}
?>

Then just repeat that process for lunch and dinner
[!--coloro:#660000--][span style="color:#660000"][!--/coloro--]The Gene Pool Could Use A Little Chlorine[!--colorc--][/span][!--/colorc--]

#5 asleboeuf

asleboeuf
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 16 May 2006 - 04:15 PM

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

#6 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 16 May 2006 - 05:23 PM

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] * FROM menu_cat AS mc JOIN menu_item AS mi ON mc.id=mi.menu_cat WHERE mc.menu='$menu' ORDER BY menu_cat [!--sql2--][/div][!--sql3--]

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

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

Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#7 asleboeuf

asleboeuf
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 16 May 2006 - 06:29 PM

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

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

   ?>


#8 kir10s

kir10s
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 16 May 2006 - 06:43 PM

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)



#9 asleboeuf

asleboeuf
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 16 May 2006 - 06:46 PM

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.

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




#10 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 16 May 2006 - 10:38 PM

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.
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#11 kir10s

kir10s
  • New Members
  • Pip
  • Newbie
  • 6 posts

Posted 16 May 2006 - 10:45 PM

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

#12 asleboeuf

asleboeuf
  • New Members
  • Pip
  • Newbie
  • 8 posts

Posted 18 May 2006 - 03:08 AM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users