[RESOLVED] Adding up Values with PHP

[whobutsb]

Hi Guys-

I was wondering what the best method for doing some simple math. I have a MySQL table with column names id and value. I would like to add up specific values, like add IDs 1+13. Is there a way to write a MySQL query to grab the values and IDs and then using PHP do the math?

Thanks for your help!

[scross]

This is simple:

$ids = array(1, 13);
$values = array();
$query = mysql_query('SELECT `id`, `value` FROM `table` WHERE `id` = "'.$ids[0].'" OR `id` = "'.$ids[1].'"');
while($result = mysql_fetch_assoc($query)){
    $values[] = $result['value'];
}
$num = $values[0] + $values[1]; //might produce errors if one of the ID's wasn't found by mysql
echo $num; //this is your final result produced from the addition of the two values

It's untested, but it should work. You should probably look for ways to optimize it.

[whobutsb]

Is there way to query the table like to grab all the values:

"SELECT id, value FROM report

And then in the PHP write a script to add up the specific values? I'm going to have about 13 different totals.

Here is the page i'm working on : www.newportharbor.com/Steve/test.php
The Total row is the one i'm working on now. Eventually it will be dynamic so that when you add values to the different rows it will automatically update itself.

Thanks for the help.

[scross]

Ok, this is still fairly simple:

$ids = array(1,3,4,13);
$sum = 0;
$values = array();
$query = mysql_query('SELECT id, value FROM report');
while($result = mysql_fetch_assoc($query)){
	$values[] = $result['value'];
	if(in_array($result['id'], $ids)){
		$sum += $result['value'];
	}
}
print_r($values); //$values is your list of values
echo $sum; //your resulting value

[laserlight]

And then in the PHP write a script to add up the specific values? I'm going to have about 13 different totals.

You should not do that. Better is to query the database with IN. To extend scross' example:

$ids = array(1, 13);
$ids = implode(',', $ids); // Note: array contents must be numeric for this example!
$query = "SELECT SUM(`value`) FROM `table` WHERE `id` IN ($ids)";
$result = mysql_query($query);
$num = mysql_result($result, 0);
echo $num; //this is your final result produced from the addition of the two values

[whobutsb]

Hey I think I'm really close to getting this. I was thinking something along these lines:

$query_totalrow = "Select id, value FROM paceReport2";
$result_totalrow = mysql_query($query_totalrow)
	or die(mysql_error());
$row_totalrow = mysql_fetch_array($result_totalrow);
$total_row = array($row_totalrow['id'] => $row_totalrow['value']);

I'm not sure if i'm doing the array correctly. But then to display the value total have something like this:

echo $total_row['1'] + $total_row['13'];

I'm doing something wrong because its not doing the addition correctly. Do you have any pointers? Thanks

[scross]

One problem is that:

mysql_fetch_array($result_totalrow);

...only returns the first row and the indices are numbers. What you need is a loop which uses mysql_fetch_assoc() as was in my examples, though doing it laserlight's way is better, you just place the IDs you want to add into the $ids array.

[whobutsb]

Hi All

I found a good way of doing the addition. To Query the server and build the array I did this:

function getRowTotals()
{
	$query_totalrow = "Select id, value FROM paceReport2";
	$result_totalrow = mysql_query($query_totalrow)
		or die(mysql_error());
$k = 1;
$id = $result_totalrow['id'];
$value = $result_totalrow['value'];
while ($total = mysql_fetch_array($result_totalrow))
{
	foreach ($total as $id => $value)
		{
			$array[$k][$id] = $value;
		}
		$k++;
}
return $array;
}
$rowTotals = getRowTotals();

Then to do the addition in the cells I used this:

echo $rowTotals['1']['value'] + $rowTotals['13']['value'];

Thanks for all your help.

[laserlight]

I found a good way of doing the addition. To Query the server and build the array I did this:

Unfortunately, that is a rather poor way of doing the addition. You are retrieving everything, when you only want two things. Look at my example. I moved the logic into the SQL statement, and was able to have the database engine perform the sum for me. All that I retrieved was the result.

EDIT:
In fact, for just two rows, I can replace your 20 lines of code with a three-liner that is likely to be faster too:

$query = "SELECT SUM(`value`) FROM paceReport2 WHERE `id`=1 OR `id`=13";
$result = mysql_query($query);
echo mysql_result($result, 0);

[scross]

That is indeed a very bad way of doing it, but you could atleast remove these two lines:

$id = $result_totalrow['id']; 
$value = $result_totalrow['value'];

because that is completely unrelated to anything and is completely logically (you're treating a resource as an array) 😛 There is also no need for the $k variable, all it is doing is pushing your indices one up, which is completely unneccessary (it's not hard to add a "-1" when you're getting the value). And, like laserlight has said, if you're not going to use your array later in the script then it is silly taking all of the values out of the table. Oh and that loop that you do for each row is completely unneccessary, just change mysql_fetch_array to mysql_fetch_assoc and use:

$array[] = $total;

which should have the same effect.

[parallax]

Could u post ur final script please?/