not displaying results correctly

[scmeeker]

I'm trying to create a list that groups information by username. Only part of it is working. The first query ($get_item_sql) is grouping the information perfectly but the second query ($get_sold) is lumping the $item_price and $item_amount_due as one total for each one and outputting the same amounts into every username. I'm stuck on this and would appreciate your help.

 

For example:  Username  item fees  image fees item sales  item price  total due

                      Jim                  2              $0.40      $100.00        $3.00    $3.40

                      Kelly                5              $1.00      $100.00        $3.00    $4.00

 

This example shows the columns in red as being the problem where Kelly didn't sell anything so her "item sales" and "item price" should be $0.00 but is carrying Jim's totals into hers.

 

Hope this helps!  Thank you!   

 

$get_item_sql = mysql_query("SELECT id, username, date, ROUND(price,2) AS price, SUM(item_fee) AS fee, item_fee, SUM(sold) AS sales, SUM(ROUND(price,2)) AS total FROM product WHERE MONTH(date) = MONTH(DATE_ADD(CURDATE(),INTERVAL -1 MONTH)) GROUP BY username" )
or die(mysql_error());

if (mysql_num_rows($get_item_sql) < 1) {
   //invalid item
   $display_block .= "<p><em>Invalid item selection.</em></p>";

  } else {
   //valid item, get info
   while ($item_info = mysql_fetch_array($get_item_sql)) {
   $item_username = $item_info['username'];
   $item_date = $item_info['date'];
   $item_price = $item_info['price'];
   
   $item_fee = $item_info['fee'];
   $image_fees = $item_fee * .20;
   $item_sold = $item_info['sales'];
   
  
$get_sold = mysql_query("SELECT SUM(ROUND(price,2)) AS total, SUM(ROUND(sold,2)) AS sales, date, username FROM product WHERE sold = '1' AND MONTH(date) = MONTH(DATE_ADD(CURDATE(),INTERVAL -1 MONTH)) GROUP BY username")
or die(mysql_error());

if (mysql_num_rows($get_sold) < 1) {
   //invalid item
   $display_block .= "<p><em>Invalid item selection.</em></p>";

  } else {
   //valid item, get info
   while ($item_sold2 = mysql_fetch_array($get_sold)) {
   
   
   $item_sales = $item_sold2['total'];
   $item_price = ($item_sold2['total']) * .03;
  
$item_amount_due = $image_fees + $item_price; 

  
  

$content .= "<form action=\"add_artist.php\" method=\"post\"><table class=\"anotherfont\" width=\"670\" border=\"0\">

			<tr><td width=\"201\">{$item_username}</td>
                    <td width=\"109\">{$item_fee}</td>
				<td width=\"109\">{$image_fees}</td>
				<td width=\"109\">  {$item_sales}</td>
				<td width=\"109\">  {$item_price}</td>
				<td width=\"109\"><input name=\"balance_due\" type=\"text\" value=\"{$item_amount_due}\" /></td>
				</tr><br /></table></form>"; 

   }
  }
  }
  }

[jcbones]

Second query is wrong.

 

$get_sold = mysql_query("SELECT SUM(ROUND(price,2)) AS total, SUM(ROUND(sold,2)) AS sales, date, username FROM product WHERE (sold = '1' AND username = '$item_username') AND MONTH(date) = MONTH(DATE_ADD(CURDATE(),INTERVAL -1 MONTH)))

 

[scmeeker]

When I made that change, it only would list one user and their information, not every user.

[scmeeker]

Okay...got it to work.  I didn't remove the GROUP BY at the end of the second query like you said.  Sorry!

 

Thank you for your help!