MySQL how to add all of a column together where pid= X

Question

Just wondering what would be the best way of adding all the values in a column together where the pid=$pid in a column.

Here's some sample code of what I'm using:

$query = mysql_query("SELECT pid, reputation FROM reputation WHERE pid=\"{$post['pid']}\"");

while($rep = mysql_fetch_assoc($query))
{
    echo $rep['reputation'];
}

That works fine, but when more than one row exists where the pid=X I need the reputation column on those rows to add together and output the result.

[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — j0k, Feb 11 '13 at 09:14

score 2 · Answer 1 · edited May 23 '17 at 10:24

2

use GROUP BY

SELECT pid, SUM(reputation) totalReputation
FROM reputation 
WHERE pid = 0
GROUP BY pid

in php

$query = mysql_query("SELECT pid, SUM(reputation) totalReputation
                      FROM reputation 
                      WHERE pid = " .$post['pid'] ."
                      GROUP BY pid");

and fetch the alias

echo $rep['totalReputation'];

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

How to prevent SQL injection in PHP?

edited May 23 '17 at 10:24

Community

1
1

answered Feb 11 '13 at 08:58

John Woo

258,903
69
498
492

Thanks @JW. I've sanitized the input earlier on in the script. It's adding the total of the column fine now but it's only showing the first PID, not the others. How can I loop through each one? I'm using: while($rep = mysql_fetch_assoc($query)) { echo $rep['totalReputation']; } – user2059565 Feb 11 '13 at 09:12
did you have `WHERE pid = x` in your query? – John Woo Feb 11 '13 at 09:13
Yeah, my query is: `SELECT pid, SUM(reputation) totalReputation FROM mybb_reputation WHERE pid = ".$post['pid']." GROUP BY pid` Thanks :) – user2059565 Feb 11 '13 at 09:15
try removing the `WHERE` cluase – John Woo Feb 11 '13 at 09:15
Works perfectly now. Thanks a load, @JW. :) – user2059565 Feb 11 '13 at 09:18
I see you're new to SO. If you feel an answer solved the problem, please mark it as `accepted` by clicking the white check mark (*around 15 mins to an hour later*). This helps keep the focus on older SO questions which still don't have answers. [*How does accepting an answer work?*](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) – John Woo Feb 11 '13 at 09:21

Dr. Dan · Answer 2 · 2013-02-11T09:12:28.783

1

$query = mysql_query("SELECT SUM(reputation) as rep FROM reputation WHERE pid=\"{$post['pid']}\"");

while($rep = mysql_fetch_assoc($query))
{
    echo $rep['rep'];
}

NOTE: You should not use mysql_* extension since they are now deprecated.

edited Feb 11 '13 at 09:12

answered Feb 11 '13 at 08:59

Dr. Dan

2,288
16
19

echo $rep['reputation']; should be echo $rep['SUM(reputation)']; – Feb 11 '13 at 09:03

MySQL how to add all of a column together where pid= X

2 Answers2