Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Dec 2008
    Posts
    14
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Problem with mysql update and select SUM

    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.

    PHP Code:
    $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

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,561
    Thanks
    80
    Thanked 4,496 Times in 4,460 Posts
    Ummm...this makes no sense.

    Code:
    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
    Code:
    SELECT SUM(Route_Income) AS SumIncome FROM Routes WHERE True
    or, for that matter, the same as doing
    Code:
    SELECT SUM(Route_Income) AS SumIncome FROM Routes
    So *OF COURSE* it gets the sum for *ALL* airlines.

  • Users who have thanked Old Pedant for this post:

    ryanhami (07-15-2011)

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,561
    Thanks
    80
    Thanked 4,496 Times in 4,460 Posts
    Get PHP out of the way. Do it all in SQL.

    Code:
    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.

  • Users who have thanked Old Pedant for this post:

    ryanhami (07-15-2011)

  • #4
    New Coder
    Join Date
    Dec 2008
    Posts
    14
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Get PHP out of the way. Do it all in SQL.

    Code:
    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!
    Last edited by ryanhami; 07-15-2011 at 12:31 AM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •