...

View Full Version : using php to add column values in a db?



LJackson
02-08-2010, 12:22 AM
Hi All,

I Am trying to add the values of four columns in my db table together and divide it by a number, then update another table with the total.

here is what i have

//INSERT NEW TOTALS INTO STORES TABLE
$gettotals = "SELECT * FROM rating2 WHERE storeID = '$id'";
$query = mysql_query($gettotals)or die(mysql_error());
$row = mysql_fetch_array($query);
$totalRating = $row['storeValue'] + $row['storeEase'] + $row['storeDelivery'] + $row['storeCharge'] /$row['totalVotes'];
$updateStores = "Update stores
SET rating = '$totalRating'
WHERE storeID = '$id'";
$runupdate = mysql_query($updateStores)or die(mysql_error());


but its not adding the expected results to the other table?

is my $totalRating wrong? i have checked the values to be added and they have been inserted correctly and i have cross checked the results with the expected results and they dont match.

any ideas
thanks
Luke

LJackson
02-08-2010, 01:10 AM
ok sorted out this little problem :D

was updating the old obsolite file not the new version :(

so i have this code

$totalRating = ($row['storeValue'] + $row['storeEase'] + $row['storeDelivery'] + $row['storeCharge'])/$row['totalVotes'];

how to i get the result to two decimal places???
thanks

hinch
02-08-2010, 01:39 AM
why not do it in query ?


UPDATE stores
SET rating = (SELECT SUM(SUM(storeValue,storeEase,storeDelivery,storeCharge)/totalVotes) AS SUM FROM rating2 WHERE storeID = '$id')

something like that you'll have to look up correct syntax but you should be able to do the entire calc in query including the update and the select.

LJackson
02-08-2010, 04:56 PM
thanks mate, will try that :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum