[RESOLVED] Local grouped results not working correctly, need a suggestion

northstjarna

HI there,

I am trying to group my results in PHP by Menu_Type like this

Menu_Type Menu_Description Menu_Price

breakfast

                   Bacon & Eggs                  £2.50
                  Toast                               £3.50

Lunch

                   Salad                               £3.00
                   Sandwich                        £3.50

Mains
Starvation £free o😉 only kidding

etc

I tried to apply the code from searching the forums here -

http://phpbuilder.com/board/showthread.php?t=10325181&highlight=grouping

However could not get it to work like this, my query is built like this

mysql_select_db($database_Bar163, $Bar163);
$query_MenuVariables = "SELECT menu_tbl.MenuID, menu_tbl.Bar163_LocationID, menu_tbl.Menu_Order, menu_tbl.Menu_Type, menu_tbl.Menu_Description, menu_tbl.Price FROM menu_tbl WHERE (((Menu_tbl.Bar163_LocationID) LIKE '$menuURL')) ORDER BY menu_tbl.Menu_Order";
$MenuVariables = mysql_query($query_MenuVariables, $Bar163) or die(mysql_error());
$row_MenuVariables = mysql_fetch_assoc($MenuVariables);
$totalRows_MenuVariables = mysql_num_rows($MenuVariables);

My results echo'd into the page look like this: (I thought that perhaps two loops would work, one inside the other, but it only groups the first Menu_Type and then all the records.

<tr> 

    <td id="menu_header3"><?php echo $row_MenuVariables['Bar163_LocationID']; ?></td>
	<td></td>
	<td></td>
</tr>

<?php do { ?>	 
	  <tr id="menu_table"> 
        <td align="left"><?php echo $row_MenuVariables['Menu_Type'];?></td>
	  </tr>
<?php do { ?>
	 	<tr>
	  	<td></td>
        <td align="left" colspan="2"><?php echo $row_MenuVariables['Menu_Description']; ?></td>
		<!--<td align="left"></td>-->
        <td align="left">£<?php echo $row_MenuVariables['Price']; ?></td>
      </tr>
<?php } while ($row_MenuVariables = mysql_fetch_assoc($MenuVariables)); ?> 	  

	  <tr> 
        <td colspan="3"><br /> </td>
      </tr>
<?php } while ($row_MenuVariables = mysql_fetch_assoc($MenuVariables)); ?>

The question is how can I get it to return all the results and return the menu_type only once.

Any help much appreciated.

THanks

Andi

liquorvicar

The reason your code isn't working as the inner loop is going through the whole result set, meaning the outer loop runs just the once (by the time you get to the end of the outer loop, you're also at the end of the result set).

You want something like this:

<tr> 

    <td id="menu_header3"><?php echo $row_MenuVariables['Bar163_LocationID']; ?></td>
	<td></td>
	<td></td>
</tr>

<?php 
$menuType = '';
do { 
     if ($menuType!==$row_MenuVariables['Menu_Type']) {
           echo('<tr id="menu_table"><td align="left">');
           echo $row_MenuVariables['Menu_Type'];
           echo('</td></tr>'); 
          $menuType = $row_MenuVariables['Menu_Type'];
     }
 ?>
	 	<tr>
	  	<td></td>
        <td align="left" colspan="2"><?php echo $row_MenuVariables['Menu_Description']; ?></td>
		<!--<td align="left"></td>-->
        <td align="left">£<?php echo $row_MenuVariables['Price']; ?></td>
      </tr>
<?php} while ($row_MenuVariables = mysql_fetch_assoc($MenuVariables)); ?>

northstjarna

Hi there,

Thanks for that, that worked. Almost there...

I am now trying to require the next step above the Menu_Type which is the menu Locations.

I am trying to show

Bar163_LocationID (Egham Lunch)
Menu_Type (Starters)

starter1 price1
starter2 price2

Menu_Type (Mains)

Mains1 price1
Mains2 price2

Bar163_LocationID (Egham Dinner)
Menu_Type (Starters)

starter1 price1
starter2 price2

Menu_Type (Mains)

Mains1 price1
Mains2 price2

Using this code below almost works... I just need to require the header in brown text to require once.

see here... http://www.bar163.com/menu/variable_menu.php?UrlVariable=Chertsey%

here's the code that got me that far...
I have tried various form of loops, but maybe I need to use a for 'each loop ' method...

Any other suggestion would be greatly appreciated.
Thanks Andi

<?php 

$location = '';
$menuType = ''; 

do { 
if ($location!==$row_MenuVariables['Bar163_LocationID']) {
if ($menuType!==$row_MenuVariables['Menu_Type']) {	

	   echo('<tr id="menu_table"><td align="left" id="menu_header3">');
	   echo $row_MenuVariables['Bar163_LocationID'];
	   echo('</td></tr>'); 
	$menuType = $row_MenuVariables['Bar163_LocationID']; 

	   echo('<tr id="menu_table"><td align="left">'); 
       echo $row_MenuVariables['Menu_Type']; 
	   echo('</td></tr>'); 
     $menuType = $row_MenuVariables['Menu_Type']; 
}
}
?> 

<tr> 
		<td></td>
        <td align="left" colspan="2"><?php echo $row_MenuVariables['Menu_Description']; ?></td> 
        <!--<td align="left"></td>--> 
        <td align="left">£<?php echo $row_MenuVariables['Price']; ?></td> 
</tr> 

<?php } while ($row_MenuVariables = mysql_fetch_assoc($MenuVariables)); ?>

liquorvicar

Yes, nearly there, but the top of the loop is wrong, try this:

do {
if ($location!==$row_MenuVariables['Bar163_LocationID']) {
           echo('<tr id="menu_table"><td align="left" id="menu_header3">');
           echo $row_MenuVariables['Bar163_LocationID'];
           echo('</td></tr>');
        $location = $row_MenuVariables['Bar163_LocationID'];
}
if ($menuType!==$row_MenuVariables['Menu_Type']) {    

           echo('<tr id="menu_table"><td align="left">');
           echo $row_MenuVariables['Menu_Type'];
           echo('</td></tr>');
         $menuType = $row_MenuVariables['Menu_Type'];
    }

You need to check your 'location' and 'menuType' separately and only print the relevant header row when the relevant info changes.

Incidentally, a better design might be to stick all the SQL results into a multi-dimensional array and then use foreach() to loop through the results. It's easy to separate your DB access and your display code that way.

// get data from DB
$menuData = array();
while ($row_MenuVariables = mysql_fetch_assoc($MenuVariables)) {
    $menuData[$row_MenuVariables['Bar163_LocationID']][$row_MenuVariables['Menu_Type']][] = array('description'=>$row_MenuVariables['Menu_Description'],'price'=>$row_MenuVariables['Price']);
}

// display data
foreach ($menuData as $location=>$locationData) {
    // output your location row header here
    foreach ($locationData as $menuType=>$menuData) {
         // output your menuType row header here
         foreach ($menuData as $menuItem) {
               // output menu item description and price
         }
     }
}

northstjarna

Hey there thanks for that!!! - This worked like a dream. 🆒

you can see the results here...

http://www.bar163.com/menu/index.php?UrlVariable=Egham%

🙂

Thanks again

Andi