Select SUM() as a variable in a for Loop

Question

I'm trying to generate XML from database and need to gather a specific amount of data based on the average from a column. This can vary from anywhere between 5 to 30 queries for the $numItems variable.

I need to execute a for loop and assign the column name in the SUM($variable) but I'm not getting any data (but no errors either).

Here is my code:

for ($t = 1; $t <= $numItems; $t++){

$query = mysql_fetch_assoc(mysql_query("SELECT SUM(column'".$t."') AS value_sum FROM scoring WHERE ID='" . $userID . "' AND name ='" . $name . "'")); 

$q = $query['value_sum'] / $totalUsers;

echo "<output".$t.">" . $q . "</output".$t.">\n";

}

The problem is assigning the SUM(column1) variable name for the column I'm getting data from, when I write the queries individually it works, but assigning the variable within the statement is causing a problem. Can any one give me any pointers?

Thanks in advance.

In this situation, echo that SQL and try to execute it on the MySQL terminal directly. You will soon be able to get an idea, I'm sure. — Lion, Feb 05 '13 at 18:49

score 4 · Answer 1 · answered Feb 05 '13 at 18:47

4

It looks like you might have extra single quotes in your query. I think it should be:

"SELECT SUM(column".$t.")..."

You should also consider doing a single select. Doing multiple database calls inside a for loop will be a huge performance problem. You could write a single select like this:

"SELECT SUM(column1), SUM(column2), SUM(column3),..."

answered Feb 05 '13 at 18:47

Stephen Ostermiller

23,933
14
88
109

Or a `GROUP BY`. Salient comment, fewer SQL calls the better. – ficuscr Feb 05 '13 at 19:15

score 1 · Accepted Answer · edited May 23 '17 at 12:27

1

Looks like bad escaping/concatenation around the column name...

"SELECT SUM(column{$t}) AS value_sum FROM scoring WHERE ID='{$userID}' AND name ='{$name}'"

Is that what you want?

Also use PDO!

edited May 23 '17 at 12:27

Community

1
1

answered Feb 05 '13 at 18:45

ficuscr

6,975
2
32
52

Thanks Ficuscr, works perfectly. I'll read up on PDO, thanks for the feedback. – Paul Feb 05 '13 at 19:08

Select SUM() as a variable in a for Loop

2 Answers2