CODEWITHSUNDEEP
phpmysqlvariablesfor-loopsum
Paul
Paul

Reputation: 359

Select SUM() as a variable in a for Loop

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.

Upvotes: 0

Views: 445

Answers (2)

Stephen Ostermiller
Stephen Ostermiller

Reputation: 25575

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),..."

Upvotes: 4

ficuscr
ficuscr

Reputation: 7063

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!

Upvotes: 1

Related Questions