Order by a new variable.

Inersha · 06-21-2010, 12:15 PM

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.

guelphdad · 06-21-2010, 02:13 PM

Code:

SELECT * FROM table ORDER BY score * age

Inersha · 06-21-2010, 03:34 PM

Quote:

Originally Posted by guelphdad

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?

Old Pedant · 06-21-2010, 07:20 PM

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.