SQL Statement help - Sum - Resource ID #81

[aggiefan]

I'm coding a page in a new hack I'm building and am trying to sum a column using a where condition. Here's the code I'm using:

$pledgeamount=$vbulletin->db->query("SELECT SUM(donation_amount) from " . TABLE_PREFIX . "donations where donation_sport='$football'");
$pledge=$pledgeamount['donation_amount'];

Essentially, I want the sum of all values in the donation_amount column where the donation_sport column is football. I just want to display the result -- no other special use for it. I'm getting a "RESOURCE ID #81" when I print $pledgeamount, so thought maybe I had to clean it up and so did the $pledge code part...that just comes up blank.

I've done hacks before for Vbulletin, but never had to use a Sum statement. Is there something special I need to do?

[Adrian Schneider]

Instead of using the query() function, use query_first() which will also fetch the results as an array.

[aggiefan]

Thanks for the reply. I was able to find a work-around (make it work) by looking at the code for vbBookie...i figured they were adding something in there. With that said, here's code that works (maybe not efficient, but a starting point for somebody in the same position I was).

$pledgetotals=$db->query_read("SELECT SUM(donation_amount) pledges FROM " . TABLE_PREFIX . "donations where donation_sport='$football'");
$totalpledges = $db->fetch_array($pledgetotals);
$finalamount=$totalpledges[pledges];

PRINT "$finalamount" shows the correct sum, so that's about all I needed to be functional here. I may need to expand off this, but it's a start.

[Adrian Schneider]

query_first() basically calls query_read() and fetch_array() to save time.

[Dismounted]

And calls a free_result as well.

[turnipofdoom]

The reason the first query does not work is because the result field name ends up something like SUM(donation_amount), not donation_amount as you expected. With SUM you generally use AS.

SELECT SUM(my_field) AS SomeName FROM X GROUP BY Y;