Order by Sum of 2 queries

Discussion in 'PHP' started by tin2mon, Sep 6, 2010.

  1. #1
    Greatings Dev Shed Community!

    I'm working on a way to organize a table in php querying values from an SQL table.

    Basically the table will display "alignment" for a text based RPG I'm working on, but haven't figured out how to sort the table properly.

    Alignment in this formula is based on (Allies - Enemies), and I've done so by doing this :

    //Shortcuts
    $db = "mysql_query";
    $dbfetch = "mysql_fetch_row";
    $dbnum = "mysql_numrows";
    $dbarray = "mysql_fetch_array";
    //End Shortcuts

    $alliances1 = $db("SELECT `alliance` FROM `alliance` WHERE your_id='$your_id' AND enemy='0' ORDER BY `alliance` ASC LIMIT 3");

    $enemy1 = $db("SELECT `alliance` FROM `alliance` WHERE your_id='$your_id' AND enemy='1' ORDER BY `alliance` ASC LIMIT 3");


    $allies = $dbnum($alliances1);

    $enemies = $dbnum($enemy1);


    $alignment = $allies - $enemies



    What I want to do is sort a table by "$alignment", but to do so I need $alignment to be configured during the query, right?

    If so, how would I arrange the syntax so that I could properly query alliances 1 and enemy1 in the same query and subtract the results from one another?

    I appreciate any help you guys can give me!
     
    tin2mon, Sep 6, 2010 IP
  2. bartolay13

    bartolay13 Active Member

    Messages:
    735
    Likes Received:
    14
    Best Answers:
    1
    Trophy Points:
    98
    #2
    select $your_id as User_id, (select alliance from alliance where enemy = 0 and your_id = User_id) as EnemyZero,(select alliance from alliance where enemy = 1 and your_id = User_id) as EnemyOne, EnemyZero - EnemyOne as differenceOfEnemies from alliance order by alliance asc limit 3
     
    bartolay13, Sep 7, 2010 IP
    stephan2307 likes this.
  3. stephan2307

    stephan2307 Well-Known Member

    Messages:
    1,277
    Likes Received:
    33
    Best Answers:
    7
    Trophy Points:
    150
    #3
    that is a pretty need query. I think I will keep a not of this one and I am sure I can use it myself somewhere.
     
    stephan2307, Sep 7, 2010 IP