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
    Jun 2008
    Posts
    50
    Thanks
    18
    Thanked 0 Times in 0 Posts

    Order by a new variable.

    Let's say that in a mysql table I have two fields called "score" and "age". I am outputting this information from the database table in to a html table.

    I can easily order these in a html table using a mysql_query like:

    PHP Code:
    $result mysql_query("SELECT * FROM table ORDER BY score"
    However, for this table I am creating a new variable from both of these pieces of data. For example:

    PHP Code:
    $score $row['score'];
    $age $row['age'];

    $score_age $score $age
    Is it possible to order my html table by this new $score_age variable that is not included in the mysql database? Would it be best to put this $score_age in to the mysql table first? Is it possible to have this $score_age variable automatically inputted in to my mysql table without me having to work it out and input it manually?

    I'm new to working with PHP and Mysql and this is my first test project, so I apologize in advance for my poor explanations and coding.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Code:
    SELECT * FROM table ORDER BY score * age

  • Users who have thanked guelphdad for this post:

    Inersha (06-21-2010)

  • #3
    New Coder
    Join Date
    Jun 2008
    Posts
    50
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by guelphdad View Post
    Code:
    SELECT * FROM table ORDER BY score * age
    Awesome. You sir are a gent. Didn't realize you could put equations in to a mysql_query. Much appreciated.

    My equation was a little more complicated than a straight multiplication, but I managed to get it working fine. Was I right to assume that normal brackets "(" and ")" were the correct ones to use for more complex calculations in a mysql_query?

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,857
    Thanks
    78
    Thanked 4,417 Times in 4,382 Posts
    On top of that, you could also do:
    Code:
    SELECT score, age, (score * age) AS score_age
    FROM table
    ORDER BY score_age;
    Yes, use normal parentheses for expressions in SQL.

    The keyword "AS" is optional, but with or without it the name following the expression is called an "alias" and you *can* use aliased names in the ORDER BY clause.

    When you get there: You can also use aliased names in a HAVING clause, but you can't use them in WHERE or GROUP BY (because they aren't created before or at the point in time that the WHERE or GROUP BY would need them). So as ugly as it might seem, you have to repeat the expressions:
    [code]
    Code:
    SELECT score, age, (score * age) AS score_age
    FROM table
    WHERE (score * age) > 132
    ORDER BY score_age;
    Even though you have to repeat the expression, be assured that any good query processor will manage to optimize the code so that the expression is really only evaluated once.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Inersha (06-21-2010)


  •  

    Posting Permissions

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