Returning MYSQL results with limits and conditions

[upperbid]

Hi, I am returning mysql rows of multiple users and purchase data which sometimes have the same user more than once because they have purchased more than one item at different times.  What I would like to do is return the rows with each user only appearing once and the quantity column and the amount column of all the rows of that user being calculated into that one row that appears so I have one row per user with the total quantity and total amount. I am using the ID of the transaction in my query to get the results.  With the code below, a column appears for each purchase of all users, but I need only one row for each user.

$result = mysql_query("select * from Purchases WHERE ID = $number");
$numrows=mysql_num_rows($result);

while($r=mysql_fetch_array($result))
{
   $id = $r["ID"];
   $buyer = $r["User_ID"];
   $quant = $r["Win_Qty"];
   $price = $r["Bid"];
   $price = number_format($price, 2);
   
  //display the row
   echo "<tr><td><B>$buyer</B>, $quant, $price</BR></td></tr>";
}

Any help would be  appreciated. Thanks.

[Maq]

Where's your query?

[flyhoney]

<?php

$users = array();

while ($row = mysql_fetch_array($result))
{
// If the user already exists in the array,
// update values
if (isset($users[$row["ID"]]))
{
	$users[$row["ID"]]['quant'] += $row["Win_Qty"];
	$users[$row["ID"]]['price'] += number_format($row["Bid"], 2);
}
// Add the user to the array
else
{
	$users[$row["ID"]] = array(
		'id'    => $row["ID"],
		'buyer' => $row["User_ID"],
		'quant' => $row["Win_Qty"],
		'price' => number_format($row["Bid"], 2)
	);
}
}

foreach ($users as $user)
{
echo "<tr><td><strong>".$user['buyer']."</strong>, ".$user['quant'].", ".$user['price']."<br /></td></tr>";
}

[upperbid]

Hi flyhoney,

 

This doesn't quite work. Now, it takes the first user (who happens to have multiple purchases) and only prints the line with that user, while totaling the quantity and amount from all the users to that shown user.  Please advise.  Thanks.