Simple Math

[ririe44]

Hey, how would you go about putting this together...

 

database:

abcdef

151820351375

 

I want to add a, and f (which would give me 90), then I want to subtract the rest of them from that amount (90-(18+20+35+13))=90-86=4

 

Do I have to create a variable for every single value in my table and then do something like this?  $total=$a+$f-($b+$c+$d+$e) ??

 

My problem will be that the structure in my table may change in the future... but my a and f columns will be constant, so I'd like to maybe have a variable for the sum of a and f, then have it subtract all other values in the table...

 

Any ideas?  Thank you!

[Mchl]

Why not do this in SQL query?

[Maq]

Make an associative array:

 

$letters = array("a" => 15, "b" => 18, "c" => 20, "d" => 35, "e" => 13, "f" => 75);

foreach($letters AS $letter => $value) {
   $$letter = $value;
}

$total=$a+$f-($b+$c+$d+$e);
echo $total;

?>

 

EDIT: didn't see this was in a database...  A query would be so much easier 

[ririe44]

Yes, it will have to be done in sql query, I'm just not sure where to start...

[Mchl]

SELECT a,b,c,d,e,f, (a+f-(b+c+d+e)) AS sum FROM table WHERE ?

[Maq]

Use a function, something like:

 

function doMath($equation) {
   $sql = "SELECT a, b, c, d, e, f, $equation AS sum FROM table";
   echo $sql;
}

$theMath = "(a+f-(b+c+d+e))";
doMath($theMath);
?>

[ririe44]

Okay, thinking through it some more, I'm thinking a table like this would be better:

 

categoryamount

a15

b18

c20

d35

e13

f75

 

so, with the new table structure, could you do something like this:?

 

$total = mysql_query("SELECT 'amount' from `$tbl_name2` AS sum WHERE 'category' = 'a', 'category' = 'f'");
$total2 = mysql_query("SELECT 'amount' from `$tbl_name2` AS sum WHERE 'category' = 'b', 'category' = 'c', 'category' = 'd', 'category' = 'e'");

$grand_total = $total+$total2

[Mchl]

More like this:

 

$result1 = mysql_fetch_row(mysql_query("SELECT SUM('amount') AS sum from `$tbl_name2` WHERE 'category' IN('a','f') "));
$result2 = mysql_fetch_row(mysql_query("SELECT SUM('amount') AS sum from `$tbl_name2` WHERE 'category' IN ('b','c','d','e') "));

$sum1 = $result1['sum'];
$sum2 = $result2['sum'];

$grand_total = $sum1 - $sum2;

 

[ririe44]

Hmmm... I'm just getting 0 as my result. 

 

$result1 = mysql_fetch_row(mysql_query("SELECT 'amount' AS sum FROM `$tbl_name2` WHERE 'category' IN ('a','k','l') "));
$result2 = mysql_fetch_row(mysql_query("SELECT 'amount' AS sum FROM `$tbl_name2` WHERE 'category' IN ('b','c','d','e','f','g','h','i','j') "));
$sum1 = $result1['sum'];
$sum2 = $result2['sum'];
$grand_total = $sum1 - $sum2;

 

Isn't the "IN" in the select from where mean 'or'?  I guess I don't understand how this would total all the amounts specified?  Also, what is happening with the $result['sum']? 

[Mchl]

Should be

SELECT SUM('amount') AS sum ...

 

if you select only 'amount' you're not summing up the rows.

[Daniel0]

And even worse, doing SELECT 'amount' AS sum will select the string literal amount, not the field value amount.

 

That's also why you get zero. You have two strings amount. Converting them to an integer (which is implicitly done using the arithmetic subtraction operator) gives you int 1. So 1-1=0.

[ririe44]

I'm still getting 0 with SUM('amount')...

 

$result1 = mysql_fetch_row(mysql_query("SELECT SUM('amount') AS sum FROM `$tbl_name2` WHERE 'category' IN ('a','k','l') "));
$result2 = mysql_fetch_row(mysql_query("SELECT SUM('amount') AS sum FROM `$tbl_name2` WHERE 'category' IN ('b','c','d','e','f','g','h','i','j') "));
$sum1 = $result1['sum'];
$sum2 = $result2['sum'];
$grand_total = $sum1 - $sum2;

 

is the $result1['sum'] coming from the 'AS sum'? 

[Daniel0]

You need to check up on data types. It's one of the fundamental concepts of computer science.

 

You are still using a string literal. You need to replace 'amount' with either amount or `amount`. The regular quotes won't work because these signify strings in SQL.

[Mchl]

Huh... missed that...

 

Same with 'category'

[Daniel0]

Oh yeah that too. It's like checking if the string category equals the string a which is obviously false.

[ririe44]

So... the code below should return a value besides 0?  Because it still is. 

 

When I print $result1 I get "Array", when I print $sum1 I get nothing... I still don't understand $result1['sum']... it seems that this is where my error is coming from.

 

$result1 = mysql_fetch_row(mysql_query("SELECT SUM(`amount`) AS sum FROM `$tbl_name2` WHERE `category` IN ('a','k','l') "));
$result2 = mysql_fetch_row(mysql_query("SELECT SUM(`amount`) AS sum FROM `$tbl_name2` WHERE `category` IN ('b','c','d','e','f','g','h','i','j') "));
$sum1 = $result1['sum'];
$sum2 = $result2['sum'];
$grand_total = $sum1 - $sum2;

 

 

[Daniel0]

Try to run the queries directly on the database and check your results.