Group by in query

PHP
1

I am working on a menu page for a restaurant for which I use the following 3 tables:


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;



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;



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:


$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

2

Hi guys. I tried the following:


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

 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?

3

Hi donboe,

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


$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

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:


  $(".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