SQL SUM rows in php

Question

I currently have this SQL statement that works if there is only one row in the table but if there is more than one it doesn't work.

$mon = mysql_query("SELECT SUM(amount)AS `ingame` FROM `players` WHERE `playername` = '$player'")or die(mysql_error());
$money = mysql_fetch_assoc($mon);
$moneyingame = $amount['ingame'];

An example of the table

PLAYERNAME AMOUNT
player1    100
player1    100
player2    100
player3    100

so when i run the SQL in the code if player1 it should return 200

Thanks, this is my first time using SQL so just learning. – Liam Armour Nov 01 '15 at 00:52 — Liam Armour, Nov 01 '15 at 00:52

score 0 · Answer 1 · edited May 23 '17 at 11:58

SUM is an aggregate function meaning it works on groups of rows. To use it meaningfully you have to tell SQL what those groups are with a GROUP BY.

SELECT SUM(amount) AS ingame
FROM players
WHERE playername = ?
GROUP BY playername

I'll also note that mysql_query is deprecated. You should be using MySQLi, or better yet, the database agnostic PDO.

You also should never put variables directly into the SQL query. This creates a very common security hole known as a SQL Injection Attack. Instead, use prepared statements and bind parameters.

$sth = $dbh->prepare("
    SELECT SUM(amount) AS ingame
    FROM players 
    WHERE playername = :playername
    GROUP BY playername
");
$sth->execute(array(':playername' => "player1"));
$ingame = $sth->fetchColumn();

This is discussed in more detail in this answer.

SQL SUM rows in php

1 Answers1