SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Group by in query

    I am working on a menu page for a restaurant for which I use the following 3 tables:
    Code:
    CREATE TABLE IF NOT EXISTS `menu_categories` (
      `menu_category_id` smallint(2) NOT NULL auto_increment,
      `menu_category` varchar(255) default NULL,
      PRIMARY KEY  (`menu_category_id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
    Code:
    CREATE TABLE IF NOT EXISTS `menu_groups` (
      `menu_group_id` smallint(2) NOT NULL auto_increment,
      `menu_category_id` smallint(2) NOT NULL,
      `menu_group` varchar(255) default NULL,
      PRIMARY KEY  (`menu_group_id`),
      FOREIGN KEY (`menu_category_id`) REFERENCES `menu_categories` (`menu_category_id`) ON DELETE CASCADE ON UPDATE NO ACTION
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
    Code:
    CREATE TABLE IF NOT EXISTS `menu_items` (
      `menu_item_id` smallint(2) NOT NULL auto_increment,
      `menu_group_id` smallint(2) NOT NULL,
      `menu_item` varchar(255) default NULL,
      `description_dut` text,
      `description_eng` text,
      `price` varchar(20) default NULL,
      PRIMARY KEY  (`menu_item_id`),
      FOREIGN KEY (`menu_group_id`) REFERENCES `menu_groups` (`menu_group_id`) ON DELETE CASCADE ON UPDATE NO ACTION
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
    Where the table menu_categories is representing the 5 main categories (Soups. Appetizers, Main Dishes etc), menu_groups the different groups in each category (e.a. Salads and Hot Appetizers in Appetizers) and menut_items the different dishes within a menu_group(e.a. Tempura Prawn and Pancake Duck in Hot Hot Appetizers).

    In 3 of the 5 main categories there are more then 1 menu_groups so in the echo I need to group them by menu_group. I tried to do that the following way:
    Code:
    $category_id = filter_input(INPUT_GET, 'category_id', FILTER_SANITIZE_NUMBER_INT);
    $qryMenu = " SELECT M.menu_group, C.menu_item, C.description_dut, C.description_eng, C.price 
    FROM menu_groups M 
    INNER JOIN menu_items C
    ON M.menu_group_id = C.menu_group_id
    WHERE menu_category_id = $category_id
    GROUP BY menu_group;";	
    						 
    if ($result = $mysqli->query($qryMenu)) {
      while ($row = $result->fetch_assoc()) {
       echo "<dl>
    		<dt>{$row['menu_group']}</dt>
    		<dd>{$row['menu_item']}<span>€ {$row['price']}</span></dd>
    		<ul>
    		   <li>{$row['description_dut']}</li>
    		   <li>{$row['description_eng']}</li>
    		</ul>
    		</dl>";   
    	}
     }
    But that is not the way to go. I think I need an nested echo but have no idea how to approach that?.

    I hope this is a bit clear

    Thank you in advance
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  2. #2
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi guys. I tried the following:
    Code:
          $category_id = filter_input(INPUT_GET, 'category_id', FILTER_SANITIZE_NUMBER_INT);
          $qryGroup = " SELECT menu_group_id, menu_group FROM menu_groups WHERE menu_category_id = $category_id;";
    													
          if ($result = $mysqli->query($qryGroup)) {
              while ($row = $result->fetch_assoc()) {
                  echo "<h1>{$row['menu_group']}</h1>";
    
                  $qryMenu = "SELECT menu_item, description_dut FROM`menu_items WHERE menu_group_id = 1";
    
                  if ($result = $mysqli->query($qryMenu)) {
                      while ($row = $result->fetch_assoc()) {
                          echo "<h2>{$row['menu_item']}</h2>"; 
                      }
                  }
              }
          }
    I don't get an error, but I don't get results either, except for the first echo:
    Code:
     echo "<h1>{$row['menu_group']}</h1>";
    but I don't get the results from the second query $qryMenu while there are three items in the database?

    Can anyone tell me what I am doing wrong?
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  3. #3
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,447
    Mentioned
    45 Post(s)
    Tagged
    13 Thread(s)
    Hi donboe,

    If I understand correctly what you're trying to do, then this should do the trick:

    PHP Code:
    $category_id filter_input(INPUT_GET'category_id'FILTER_SANITIZE_NUMBER_INT);
    $qryMenu " SELECT M.menu_group, C.menu_item, C.description_dut, C.description_eng, C.price 
    FROM menu_groups M 
    INNER JOIN menu_items C
    ON M.menu_group_id = C.menu_group_id
    WHERE menu_category_id = 
    $category_id
    ORDER BY menu_group;"
    ;

    $current_group null;
                             
    if (
    $result $mysqli->query($qryMenu)) {
        while (
    $row $result->fetch_assoc()) {
            if (
    $row['menu_group'] !== $current_group) {
                
    $current_group $row['menu_group'];
                echo 
    "<h1>{$row['menu_group']}</h1>";
            }
            
    // Output item description, price, etc.
        
    }
     } 
    Note that I've replaced the GROUP BY clause with an ORDER BY clause which will sort the items by their menu group, in alphabetical order - if that's not what you're after, you'll have to change the sort criteria.

    Basically I'm initialising a variable $current_group with a value of null, and for each iteration of the while loop I'm checking if the current item's menu group matches that of $current_group - if it doesn't, we output the menu group heading and replace the value of $current_group.

  4. #4
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi fretburner. That was indeed where I was after. You made my day . There is one last thing I am trying to figure out. When switching from on group to the other the height from the output will vary as you wil see here: After clicking on Voorgerechten / Appetizers and then switch between Salades/Salads and Warme Hapjes/Hot Appetizers! The change in height is quite abrupt as will you see. I try to figure out a way to make this change going smooth, for example by using the .animate function. Right now for the group change I am using the following function for the menu items:
    Code:
      $(".menu").on("click", "a", function(e) {
        e.preventDefault();
        $(".kaart").load(this.href); 	
      });
    With this function I make sure that the right menu group is loaded when a certain menu group in the menu was clicked ( $(".kaart").load(this.href). Is it somehow possible to change this part of the function into a function on its own and then add a jQuery animate, so that the transition between menu groups is done true a smooth transition? I have been googling for the animate function but what I have found so far is all based on fixed height of the div, where in my case the content will vary in height depending on the menu group!

    Many thanks
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •