Echoing the sum of a table in PHP

Question

I have 2 columns in a table called Points. The 2 columns are UserPoints and UserID.

I want to be able to echo the total amount of points a user has.

I've got something like this but I dont think its right.

$getTotalPoints = mysql_query("SELECT SUM(UserPoints) FROM `Points` WHERE `UserID` = '1'") or die(mysql_error()); 
$totalPoints = mysql_fetch_array($getTotalPoints);

When i echo the above statement by echoing "$totalPoints" i get "Array".

Anyone know the correct query to do this ?

Answer 1

You're getting Array because that's what's stored in $totalPoints. Look closely at your code and you'll see you used the mysql_fetch_array() function, which retrieves a row of results from the results set as an array. If you do var_dump() on $totalPoints you'll see the following:

Array
(
    [0] => 12345
    [SUM(UserPoints)] => 12345
)

The sum you're looking for is at index 0 or the column name, in this case SUM(UserPoints), so you can output it using echo $totalPoints[0] or echo $totalPoints['SUM(UserPoints)'].

Alternatively, you could use the mysql_result() function. I think this is more in-line with the behavior you were expecting. It fetches a single value from the row from the result set. So, instead of mysql_fetch_array() you'd wrote:

$totalPoints = mysql_result($result, 0);

For more information on mysql_result(), check out the PHP documentation for it.

As an aside, I would recommend not using mysql_* functions if you have the option. A newer interface like PDO, or at least mysqli, would be better. This will depend on your project of course... if you're working with a large legacy code base it may be difficult to change. But if you're starting out now, I think you'd benefit from the newer libraries. You can see my opinion and some guidance on transitioning extensions in this article I wrote.

Hope this helped... and good luck!