...

View Full Version : Adding up mysql table values



kJasso
07-30-2008, 05:42 PM
I have a list of values in a mysql table that I need to add up and find their total. I have tried:


$a = array_sum(mysql_query("SELECT value FROM table"));

and


$a = "SELECT sum(value) FROM table");

Neither of which returned an error, but neither gave the result I wanted. (Errors are enabled on php)

The script has to work with on two conditions, first it checks if the year is equal to a user selected year. Then gathers all the values for a selected location. From there if the balance is >= 0 it adds them up. At the same time if the balance is <= 0 it adds up a different value in the same row. At the end it needs to add the two values together to be printed later.

This is what I have now:


if ($fiscal != $fisyr) {
} else if ($loc == "Location") {
if ($balance >= 0) {
$a = "SELECT sum(balance) FROM accounts";
$a_res = mysql_query($a,$conn) or die(mysql_error());
} else if ($balance <= 0) {
$b = "SELECT sum(bbalance) FROM accounts";
$b_res = mysql_query($b,$conn) or die(mysql_error());
}
$dvalue = ($a_res + $b_res);
}

Daniel Israel
07-30-2008, 05:57 PM
I have a list of values in a mysql table that I need to add up and find their total. I have tried:


$a = array_sum(mysql_query("SELECT value FROM table"));

and


$a = "SELECT sum(value) FROM table");

Neither of which returned an error, but neither gave the result I wanted. (Errors are enabled on php)

The script has to work with on two conditions, first it checks if the year is equal to a user selected year. Then gathers all the values for a selected location. From there if the balance is >= 0 it adds them up. At the same time if the balance is <= 0 it adds up a different value in the same row. At the end it needs to add the two values together to be printed later.

This is what I have now:


if ($fiscal != $fisyr) {
} else if ($loc == "Location") {
if ($balance >= 0) {
$a = "SELECT sum(balance) FROM accounts";
$a_res = mysql_query($a,$conn) or die(mysql_error());
} else if ($balance <= 0) {
$b = "SELECT sum(bbalance) FROM accounts";
$b_res = mysql_query($b,$conn) or die(mysql_error());
}
$dvalue = ($a_res + $b_res);
}


mysql_query returns a mysql_result. You need to get the data out of the result.

kJasso
07-30-2008, 07:29 PM
Tried it as a result and as fetch_array, neither worked. Though I'm probably doing something wrong, I'm fairly new to php coding.

oesxyl
07-30-2008, 07:57 PM
I have a list of values in a mysql table that I need to add up and find their total. I have tried:


$a = array_sum(mysql_query("SELECT value FROM table"));
as Daniel Israel said, mysql_query don't return the values. You must use mysql_fetch_assoc, mysql_fetch_array or other methods.


and

$a = "SELECT sum(value) FROM table");

Neither of which returned an error, but neither gave the result I wanted. (Errors are enabled on php)

The script has to work with on two conditions, first it checks if the year is equal to a user selected year. Then gathers all the values for a selected location. From there if the balance is >= 0 it adds them up. At the same time if the balance is <= 0 it adds up a different value in the same row. At the end it needs to add the two values together to be printed later.

This is what I have now:


if ($fiscal != $fisyr) {
} else if ($loc == "Location") {
if ($balance >= 0) {
$a = "SELECT sum(balance) FROM accounts";
$a_res = mysql_query($a,$conn) or die(mysql_error());
} else if ($balance <= 0) {
$b = "SELECT sum(bbalance) FROM accounts";
$b_res = mysql_query($b,$conn) or die(mysql_error());
}
$dvalue = ($a_res + $b_res);
}

the construct have problems, see comments:


if ($fiscal != $fisyr) {
// you are here if $fiscal != $fisyr
} else if ($loc == "Location") {
// you are here if $fiscal == $fisyr
if ($balance >= 0) { // <- $balance must exist and have a value
$a = "SELECT sum(balance) FROM accounts";
$a_res = mysql_query($a,$conn) or die(mysql_error());
} else if ($balance <= 0) {
$b = "SELECT sum(bbalance) FROM accounts";
$b_res = mysql_query($b,$conn) or die(mysql_error());
}
// only one of the blocks of the previous if are executed so
// $a_res or $b_res must have some previous values
// which one depend of $balance value
$dvalue = ($a_res + $b_res);
}


I guess you can use this kind of query:


$query = "select sum(balance) as ares, sum(bbalance) as bres from accounts";
$result = mysql_query($query,$conn);
if($result){
$a_res = mysql_result($result,0,'ares');
$b_res = mysql_result($result,0,'bres');
if($a_res && $b_res){
$dvalue = $a_res + $b_res;
// do something with $dvalue
}
}else{
print mysql_error();
}


or this:


$query = "select (sum(balance) + sum(bbalance)) as dvalue from accounts";
$result = mysql_query($query,$conn);
if($result){
$dvalue = mysql_result($result,0,'dvalue');
// do something with $dvalue
}else{
print mysql_error();
}


both not tested and I'm not pretty sure about mysql_result since I don't use it often. See the manual.

regards

kJasso
07-31-2008, 10:41 PM
I guess you can use this kind of query:


$query = "select sum(balance) as ares, sum(bbalance) as bres from accounts";
$result = mysql_query($query,$conn);
if($result){
$a_res = mysql_result($result,0,'ares');
$b_res = mysql_result($result,0,'bres');
if($a_res && $b_res){
$dvalue = $a_res + $b_res;
// do something with $dvalue
}
}else{
print mysql_error();
}


both not tested and I'm not pretty sure about mysql_result since I don't use it often. See the manual.

regards

That bit works well, and I was able to manipulate it as best as i could to do what i want. But now if there's only 1 value per location it doesnt show the value. I tried using mysql_num_rows but it returned the wrong value.

I have this now:


//Total Department of Revenue
$querya = "SELECT sum(balance) as ares
FROM accounts
WHERE loc='Department of Revenue' && balance >= 0 && fiscalyr ='$fisyr'";
$resulta = mysql_query($querya,$conn);

$queryb = "SELECT sum(bbalance) as bres
FROM accounts
WHERE loc='Department of Revenue' && balance <= 0 && fiscalyr ='$fisyr'";
$resultb = mysql_query($queryb,$conn);


if(mysql_num_rows($resulta) <= 1) {
if($balance <= 0) {
$dvalue = $bbalance;
}else if($balance >= 0) {
$dvalue = $balance;
}
}else if(mysql_num_rows($resultb) <= 1) {
if($balance <= 0) {
$dvalue = $bbalance;
} else if($balance >= 0) {
$dvalue = $balance;
}
} else {
if($resulta && $resultb){
$a_res = mysql_result($resulta,0,'ares');
$b_res = mysql_result($resultb,0,'bres');
if($a_res && $b_res){
$dvalue = $a_res + $b_res;
}else{
print mysql_error();
}
}
}

//Total OPRD
$queryc = "SELECT sum(balance) as cres
FROM accounts
WHERE loc='OPRD' && balance >= 0 && fiscalyr ='$fisyr'";
$resultc = mysql_query($queryc,$conn);
$queryd = "SELECT sum(bbalance) as dres
FROM accounts
WHERE loc='OPRD' && balance <= 0 && fiscalyr ='$fisyr'";
$resultd = mysql_query($queryd,$conn);

if($resultc && $resultd){
$c_res = mysql_result($resultc,0,'cres');
$d_res = mysql_result($resultd,0,'dres');
if($c_res && $d_res){
$ovalue = $c_res + $d_res;
}else{
print mysql_error();
}
}

//Total Collections
$querye = "SELECT sum(balance) as eres
FROM accounts
WHERE loc='Collections' && balance >= 0 && fiscalyr ='$fisyr'";
$resulte = mysql_query($querye,$conn);
$queryf = "SELECT sum(bbalance) as fres
FROM accounts
WHERE loc='Collections' && balance <= 0 && fiscalyr ='$fisyr'";
$resultf = mysql_query($queryf,$conn);

if($resulte && $resultf){
$e_res = mysql_result($resulte,0,'eres');
$f_res = mysql_result($resultf,0,'fres');
if($e_res && $f_res){
$cvalue = $e_res + $f_res;
}else{
print mysql_error();
}
}

The database has this:


+---+-----------------------+----------+---------+
|id | Location | Bbalance | balance |
+---+-----------------------+----------+---------+
| 1 |Department of Revenue | 20 | -1 |
| 2 |Department of Revenue | 10 | 10 |
| 3 |Collections | 30 | -1 |
| 4 |Collections | 20 | -1 |
| 5 |OPRD | 10 | -1 |
| 6 |OPRD | 10 | -1 |
| 7 |OPRD | 10 | 5 |
+---+-----------------------+----------+---------+

And it outputs this:


+------------------------+-----+
| Department of Revenue: |5 |
+------------------------+-----+
| OPRD |25 |
+------------------------+-----+
| Collectons: | |
+------------------------+-----+


Any ideas why or how to fix it?

oesxyl
07-31-2008, 11:13 PM
let's take only collectons:


//Total Collections
$querye = "SELECT sum(balance) as eres
FROM accounts
WHERE loc='Collections' && balance >= 0 && fiscalyr ='$fisyr'";
$resulte = mysql_query($querye,$conn);
$queryf = "SELECT sum(bbalance) as fres
FROM accounts
WHERE loc='Collections' && balance <= 0 && fiscalyr ='$fisyr'";
$resultf = mysql_query($queryf,$conn);

if($resulte && $resultf){
$e_res = mysql_result($resulte,0,'eres');
$f_res = mysql_result($resultf,0,'fres');
// mysql_result return FALSE if can't retrive the value
// or the value, so to be sure that if the value is 0 or ''
// will get a correct result we test
if($e_res !== FALSE && $f_res !== FALSE){
$cvalue = $e_res + $f_res;
}else{
// here you can't have a mysql_error because
// both $resulte and $resultf are true
print "$e_res or $f_res is false";
}
}else{
print mysql_error();
}


test this and post results

regards

kJasso
07-31-2008, 11:35 PM
oesxyl you are my hero. That worked perfectly. Thanks a ton for all your help.

oesxyl
07-31-2008, 11:39 PM
oesxyl you are my hero. That worked perfectly. Thanks a ton for all your help.
I'm glad it work, :)

regards



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum