[SOLVED] Aggregate Functions

[mwl707]

Hi Can anyone help please .

 

I am trying to write a function. The code is passed a variable which is used in a mysql query. (Code below) In the second to last line I always get 0 as a result when I know the result should be 3. If i use the following code I get the correct result

 

$screen_size = "i100s" ;

$result = mysql_query("SELECT SUM($screen_size) FROM event WHERE jobstatus = 'confirmed' ")  ;

 

while($row = mysql_fetch_array($result)){

$res = $row['SUM($screen_size)'] ;

 

echo "Total ". $row['jobstatus']. " = ". $res ;

echo "<br />";

 

In the second to last line I always get 0 as a result when I know the result should be 3. If i use the following code I get the correct result

 

$screen_size = "i100s" ;

$result = mysql_query("SELECT SUM($screen_size) FROM event WHERE jobstatus = 'confirmed' ")  ;

 

while($row = mysql_fetch_array($result)){

 

$res = $row['SUM(i100s)'] ;

 

echo "Total ". $row['jobstatus']. " = ". $res ;

echo "<br />";

 

Can anyone offer some advice ?

 

Thanks Mick

 

[Ken2k7]

What is i100s? Is that a column name?

[DarkWater]

Doesn't matter.  Probably is.  The problem is that you're using single quotes (' ') around your array key ($row['SUM($screen_size)']) so variables don't interpolate.  Use double quotes.

[Ken2k7]

Doesn't matter.  Probably is.  The problem is that you're using single quotes (' ') around your array key ($row['SUM($screen_size)']) so variables don't interpolate.  Use double quotes.

That's not it. You can't run SUM outside of a query. o.O Well, unless it's a function.

[DarkWater]

If it's in quotation marks, it wouldn't matter.

 

<?php
echo "substr('test', 0, 10);";

 

He could (and probably should) be using an alias in the query, but in reality, it doesn't matter.  Just more typing if he doesn't. xD

[Ken2k7]

Well no, if $screen_size is not a table column, then returning 0 makes sense.

[mwl707]

Ken2k7

 

Sorry for being vague, Yes i100s is a column in my table called event

 

Thanks for your help

[Ken2k7]

Try this

$result = mysql_query("SELECT SUM(`i100s`) AS `sum` FROM event WHERE jobstatus = 'confirmed'");
while($row = mysql_fetch_assoc($result)) {
     echo $result['sum'];
}