Math on mysql

[gokihar]

Hi, I'm new here !

I have some problems and hope you can help me a bit :

Got table with :

Product | quantity | price | (quantity*price) AS value

It all works fine but I cannot get the sum of value from all of the products.

 

In first part of the code I got select and table draw. After closing table I try to sum value :

$query2="SELECT sum(quantity*price) as value2 FROM table";
if($query2){echo " Command q2 is successful ";}
else {echo " Command q2 is not successful ";} 
$result2=mysql_query($query2);
if($result2){echo " Command r2 is successful ";}
else {echo " Command r2 is not successful ";} 
$zmienna=mysql_fetch_array($query2);
if($zmienna){echo " Command z is successful ";}
else {echo " Command z is not successful ";} 

echo " Result: ".$query2['value2'];

 

query2 give me "S" as output and I just don't know why. Any hints ?

[eMonk]

I'm new to mysql as well...

 

I don't think you can have a sum function in a query like that.

 

Maybe something along the lines of:

 

$sum_1 = sum(quantity*price)

 

and remove "sum(quantity*price) as value2" from $query2.

[eMonk]

Actually, looks like you can:

 

http://dev.mysql.com/doc/refman/5.0/en/select.html

 

The first comment there might help you:

 

SELECT

SUM(IF(beta_idx=1, beta_value,0)) as beta1_value,

SUM(IF(beta_idx=2, beta_value,0)) as beta2_value,

SUM(IF(beta_idx=3, beta_value,0)) as beta3_value

FROM alpha JOIN beta WHERE alpha_id = beta_alpha_id;

 

[gokihar]

I GOT IT ! 

$query2="SELECT SUM(quantity * price) AS value2 from table";

$results = mysql_query($query2);
$arr = mysql_fetch_row($results);
$results2 = $arr[0]; 

echo $results2;

 

Thanks for help !!

[Maq]

FYI, in your first code, which I don't really get, you would change the last bit to (comments in the code):

$zmienna=mysql_fetch_array($result2); //need to fetch the result2, not query2
if($zmienna){echo " Command z is successful ";}
else {echo " Command z is not successful ";} 

echo " Result: ".$zmienna['value2'];  //need to use zmienna, because it holds the result set (array data), not query2 which is just a string