Display unique values in a table -- foreach loop, or something else?

Trying not to display lots of duplicate data

[shs_cmcl]

I've got some data (customer orders) in a MySQL table that I want to display grouped by the order number, with totals for each order. The difficulty I'm having is that I want the output to look like this:

Order ID...Customer Name...Product......Qty...Price 
3..........John Doe........Item One.....1.....15.50 
...........................Item Three...2.....5.75 
...........................Item Five....1.....3.50 
................................Order Total...47.50 
5..........Mike Brown......Item One.....2.....10.00 
...........................Item Two.....1.....5.00 
................................Order Total...15.00 
6..........Jane Doe........Item Four....1.....12.00 
................................Order Total...12.00 

And so on. I can easily display everything without totals in one table with a simple while loop:

while ($row = mysql_fetch_array($result)) {
 echo '<TR><TD>'.$row['order_id'].'</TD>';
 echo '<TD>'.$row['customer_name'].'</TD>';
 echo '<TD>'.$row['product_name'].'</TD>';
 echo '<TD>'.$row['product_quantity'].'</TD>';
 echo '<TD>'.$row['total_value'].'</TD></TR>';
 }

But of course it displays everything repeatedly, like this:

Order ID...Customer Name...Product......Qty...Price 
3..........John Doe........Item One.....1.....15.50 
3..........John Doe........Item Three...2.....5.75 
3..........John Doe........Item Five....1.....3.50 
5..........Mike Brown......Item One.....2.....10.00 
5..........Mike Brown......Item Two.....1.....5.00 
6..........Jane Doe........Item Four....1.....12.00 

What would be the best way to get what I'm looking for here? Some sort of foreach loop? The query I'm using here is a very basic SELECT query to just pull all the orders from the table. If I used GROUP BY order_id in the query, that would of course eliminate the duplicates, but I still want to display each individual product, quantity, and price for each order (plus the order total). Drawing a blank, any pointers?

[cameraman]

I'm assuming by 'duplicates' you mean that you only want to print "John Doe" on one line?
With a 'very basic' query you're going to run into problems when the table starts getting out of order, and the only way to circumvent that would be to read the whole thing into an array to get it all sorted out. Easier (I think) is to let mySQL do the work - I suggest beefing up your query to something like:
SELECT orderID,custName,prodName,qty,price, SUM(qty*price) FROM table GROUP BY orderID,lineNumber WITH ROLLUP

Before the while loop, set a flag, say $newOrder, to true.
Then in your while loop if $newOrder is true then print custName and set it ($newOrder) to false, otherwise don't. Then examine lineNumber - if it's null, it's a row with total so print a total row & set $newOrder back to true (for the next iteration). If lineNumber isn't null, print prodName, qty, and price.

[Oliver Henniges]

Something like this?

$lastorderid = '';
while ($row = mysql_fetch_array($result)) {
$thisorderid=$row['order_id'];
echo '<TR>';
if($thisorderid<>$lastorderid){
echo '<TD>'.$row['order_id'].'</TD>';
echo '<TD>'.$row['customer_name'].'</TD>';
}
else {
echo '<td></td><td></td>';
}
echo '<TD>'.$row['product_name'].'</TD>';
echo '<TD>'.$row['product_quantity'].'</TD>';
echo '<TD>'.$row['total_value'].'</TD></TR>';
}

I did not test the snippet, so be careful. but the logic should work in your case.

[Oliver Henniges]

oops:

$lastorderid = '';
while ($row = mysql_fetch_array($result)) {
$thisorderid=$row['order_id'];
echo '<TR>';
if($thisorderid<>$lastorderid){
echo '<TD>'.$row['order_id'].'</TD>';
echo '<TD>'.$row['customer_name'].'</TD>';
$lastorderid=$thisorderid; //forgot this line, lol

}
else {
echo '<td></td><td></td>';
}
echo '<TD>'.$row['product_name'].'</TD>';
echo '<TD>'.$row['product_quantity'].'</TD>';
echo '<TD>'.$row['total_value'].'</TD></TR>';
}

[shs_cmcl]

Thanks Oliver, that works great and it's almost exactly what I'm looking for. I'll need to fiddle with it a little to get per-order totals in there -- maybe split it out into a separate table per order ID somehow. Thanks very much for your help!

[Oliver Henniges]

Yepp, in that case this is bad and lazy programming style, because the logic will presumably skip your last order's sum.

if you drew your order sums from a single mysql-request using the sum()-function (group by order id) you might probably get the number of items in the same breath using the count()-function. Something like

$sql = "select orderid, count(price) as numberofitems, sum(price) as ordertotal from orders group by orderid";

That would be a second query to the database, but I think such a solution is best, because of the one-to-many relation between your orders-table and your produts-sold-table. You might connect this query to the same query by means of which you got the customer-data using some join on customerid.

Just think about it a while: Your order sums and customer data have a set-level different from your products-sold.

You may easily embed two sql-queries like this:

echo '<table>;
$sql1 = "query customer data and order sums according to orderid"
....read out variables
foreach ($orderid as thisid){
echo "<tr>customerdata-headerline<tr>";
$sql2 = "query products-data for thisorderid";
....read out variables
while ($row = mysql_fetch_array($result)) {//this is your script
echo '<TR><TD></TD>';
echo '<TD></TD>';
echo '<TD>'.$row['product_name'].'</TD>';
echo '<TD>'.$row['product_quantity'].'</TD>';
echo '<TD>'.$row['total_value'].'</TD></TR>';
}
echo "<tr>ordersums baseline</TR>";
}
echo "</table>";

This code is of course not complete and usable.
It should only illustrate the logic.