GROUP BY php syntax needed

[ro1960]

I'm having trouble figuring out the PHP syntax to display GROUP BY results.

Here's my MySQL query:

$query = "SELECT DATE_FORMAT(date_transac_f, '%m/%d/%Y') AS Fdate_transac_f, name, currency, gross, fee, net, from_email, transac_id, item_title, subscription_number, receipt_id, SUM(gross) AS gross_total, SUM(fee) AS fee_total, SUM(net) AS net_total FROM tablename WHERE currency = '$currency' GROUP BY currency ORDER BY date_transac_f";

Here's how I print all the returned rows:

while ($row = mysql_fetch_array($result))
{
extract($row);
echo "<tr class=\"textbox_grey\"><td class=\"t13\">".$Fdate_transac_f."</td><td class=\"t13\">".$name."</td><td class=\"t13\">".$currency."</td><td align=\"right\" class=\"t13_red\">".$gross."</td><td align=\"right\"class=\"t13\">".$fee."</td><td align=\"right\"class=\"t13_red\">".$net."</td><td class=\"t13\">".$from_email."</td><td class=\"t13\">".$transac_id."</td><td class=\"t13\">".$item_title."</td><td class=\"t13\">".$subscription_number."</td><td class=\"t13\">".$receipt_id."</td></tr>\n";
}

What would be the syntax to print at the bottom of the table, the sums of the columns gross, fee and net?

[dreamcatcher]

Hi ro1960,

Did you try:

$row['gross_total'];
$row['net_total'];
$row['fee_total'];

dc

[ro1960]

I tried but I am not sure about the code that goes around. Do I need some sort of while loop?

[Habtom]

I think I know what you want:

while ($row = mysql_fetch_array($result))
{
extract($row);
echo "<tr class=\"textbox_grey\"><td class=\"t13\">".$Fdate_transac_f."</td><td class=\"t13\">".$name."</td><td class=\"t13\">".$currency."</td><td align=\"right\" class=\"t13_red\">".$gross."</td><td align=\"right\"class=\"t13\">".$fee."</td><td align=\"right\"class=\"t13_red\">".$net."</td><td class=\"t13\">".$from_email."</td><td class=\"t13\">".$transac_id."</td><td class=\"t13\">".$item_title."</td><td class=\"t13\">".$subscription_number."</td><td class=\"t13\">".$receipt_id."</td></tr>\n";

$btm_gross = $btm_gross + $gross;
$btm_fee = $btm_fee + $fee;
$btm_net = $btm_net+ $net;
}

echo "<tr class=\"textbox_grey\"><td class=\"t13\">&nbsp;</td><td class=\"t13\">&nbsp;</td><td class=\"t13\">&nbsp;</td><td align=\"right\" class=\"t13_red\">".$btm_gross."</td><td align=\"right\"class=\"t13\">".$btm_fee."</td><td align=\"right\"class=\"t13_red\">".$btm_net."</td><td class=\"t13\">&nbsp;</td><td class=\"t13\">&nbsp;</td><td class=\"t13\">&nbsp;</td><td class=\"t13\">&nbsp;</td><td class=\"t13\">&nbsp;</td></tr>\n";

Habtom

[vincevincevince]

Habtom - that solution looks just fine. The MySQL alternative is to use "SELECT SUM(...) as total...," etc.

[Habtom]

vince, infact he/she has used SUM in the query while grouping the rows. The result needed was the sum of the sums, and I wasn't sure how to do that on the query. You have the query there, you can let me know if you can.

Habtom