...

View Full Version : Problem with mysql update and select SUM



ryanhami
07-14-2011, 05:42 PM
Hello,

I am currently making a php game which is an aircraft management simulation game. I have a cron job that runs a lot of different mysql inserts, updates and deletes that run to change the table every game day.

However I have hit a problem. I am trying to select a sum of route incomes for each airline from a routes table and then update each of them total incomes into the airline table. But what my current code seems to sum the total amount of route incomes without sorting them into different airlines and then it submits the same income into each total airline income in the airline table. Below is my current code to try do this.


$query6 = ("SELECT SUM(Route_Income) AS SumIncome FROM Routes WHERE Airline_ID = Airline_ID");
$row6 = (mysql_fetch_array(mysql_query($query6)));

$sumincome = $row6['SumIncome'];

$airlineincomeupdate = ("UPDATE Airlines SET Airline_Income = '" . $sumincome . "' WHERE Airline_ID = Airline_ID");

Any help would be much appreciated!

Thanks,

Ryan

Old Pedant
07-14-2011, 10:57 PM
Ummm...this makes no sense.



SELECT SUM(Route_Income) AS SumIncome FROM Routes WHERE Airline_ID = Airline_ID

Airline_ID will *ALWAYS* be equal to Airline_ID.

So that query is that same as doing


SELECT SUM(Route_Income) AS SumIncome FROM Routes WHERE True

or, for that matter, the same as doing


SELECT SUM(Route_Income) AS SumIncome FROM Routes

So *OF COURSE* it gets the sum for *ALL* airlines.

Old Pedant
07-14-2011, 11:08 PM
Get PHP out of the way. Do it all in SQL.



UPDATE Airlines,
( SELECT Airline_ID, SUM(Route_Income) AS SumIncome
FROM Routes
GROUP BY Airline_ID ) AS R
SET Airlines.Airline_Income = R.SumIncome
WHERE Airlines.Airline_ID = R.Airline_ID;

Presto.

ryanhami
07-15-2011, 12:07 AM
Get PHP out of the way. Do it all in SQL.



UPDATE Airlines,
( SELECT Airline_ID, SUM(Route_Income) AS SumIncome
FROM Routes
GROUP BY Airline_ID ) AS R
SET Airlines.Airline_Income = R.SumIncome
WHERE Airlines.Airline_ID = R.Airline_ID;

Presto.

Cheers, that worked like a treat!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum