Need Help With Query

[vincej]

First of all I'm not even sure if it's the query I need help with or with the PHP. Here is the situation. I have built an app. The app has a report which generates a list of all the products sold with the quantity and price for each location. The problem I have is that every line item has it's location repeated, and repeated - this is the consequence of the foreach loop used. so for example:

 

Chicago 200 chickens $2000

Chicago 100 hams $1000

Chicago 300 Beef $6000

 

You can see the problem ... Chicago is repeated on every line. What is even more boring is that the document reports on 50 cities with 50 products each. So there is immense repetition. So I want to get to is:

 

Chicago

 

200 chickens $2000

100 hams $1000

300 Beef $6000

 

Washington

 

200 chickens $2000

100 hams $1000

300 Beef $6000

 

etc etc

 

Here is the SQL I have delivering the flawed outcome:

 

SELECT products.name, order.prodid, sum(order.quantity) as 'Quantity', order.pickupdate, sum(order.ordervalue) as 'Order Value', locations.location, locations.locationid
FROM `products`, `order`, `locations`
where order.prodid = products.id
and locations.locationid = order.deliverylocationid
AND order.status = 'open'
group by order.deliverylocationid, order.prodid
order by locations.location asc;

 

Here is the Foreach Loops which generates the results of the flawed result:

 

<?php

foreach ($ordersbylocation as $key => $value){
?>
<tr>
<td width="150"><?php echo anchor('admin/reports/SpecificLocation/' . $value['locationid'], $value['location']);?></td>
<td width="60" align="center"><?php echo $value['prodid'];?></td>
<td width="60" align="center"><?php echo $value['Quantity'];?></td>
<td width="250"><?php echo $value['name'];?></td>
<td width="150"><?php echo $value['Order Value'] . "<br>";?></td>
</tr>

<?php }
?>

[Psycho]

The solution is in the logic of displaying the results - not the query. You need to create a variable to track when the location changes and only display the location when is changes. Also, in my opinion, breaking in and out of PHP only makes the code harder to maintain. I'm also curious why the results are being generated from an array. That would indicate that you are looping through the query to put the results into an array and THEN using the array to generate the output. That is wasteful. You should just create the output when processing the results of the query. Lastly, you should use proper JOINs on your query, else it is going to be difficult to get more advanced data.

 

$query = "SELECT sum(order.quantity) as quantity, sum(order.ordervalue) as value,
			 products.id, products.name
			 locations.location, locations.locationid
	  FROM order
	  JOIN products
	    ON  order.prodid = products.id
	  JOIN locations
	    ON order.deliverylocationid = locations.locationid
	  WHERE order.status = 'open'
	  GROUP BY order.deliverylocationid, order.prodid
	  ORDER BY locations.location asc;";
$result = mysql_query($query) or die(mysql_error());

//Create variable to track when location changes
$current_location_id = '';
while($row = mysql_fetch_assoc($result))
{
   //Check if city is different thatn last record
   if($current_location_id != $row['locationid'])
   {
    //Display City
    $city_display = anchor("admin/reports/SpecificLocation/{$row['locationid']}", $row['location']);
    echo "<tr><td colspan='4'>{$city_display}</td></tr>\n";
    $current_location_id = $row['locationid'];
   }
   //Display product total
   echo "<tr>\n";
   echo "<td width='60' align='center'>{$row['id']}</td>\n";
   echo "<td width='60' align='center'>{$row['quantity']}</td>\n";
   echo "<td width='250'>{$row['name']}</td>\n";
   echo "<td width='150'>{$row['value']}</td>\n";
   echo "</tr>\n";

}

Edited December 9, 2012 by Psycho