SELECT * and calculation and ORDER BY calculated value?

[atw]

Hi,

I am trying to make a database for my pool (billiards) team, it looks like this:
http://img132.imageshack.us/img132/1841/pooldb7wc.gif (http://imageshack.us)

I am currently using a "left join" to pull the data out of the db (which works perfectly) however, I want to calculate each player's average AND sort by that value!

The average is calculated like this:
$average = $matches_won - $matches_lost;
How would I go about carrying out the left join AND the calculation AND the sort in one query?

I am using MySQL 4.1.11 / PHP 5.1.2 on my dev server & MySQL 3.23.58 / PHP 4.3.10 on my "live" server.

All help gratefully received! :)

[chazzy]

why don't you post your sql? if you're calculating the average outside of the query, there's no way you can sort it. Why is your development server 2 generations ahead of the live server? i would imagine you have a ton of incompatibilities.

[atw]

Oops, good point; my SQL is a follows (I am using Dreamweaver):

<?php
mysql_select_db($database_anchor_pool, $anchor_pool);
$query_rs_Player = "SELECT * FROM player ORDER BY id ASC";
$rs_Player = mysql_query($query_rs_Player, $anchor_pool) or die(mysql_error());
$row_rs_Player = mysql_fetch_assoc($rs_Player);
$totalRows_rs_Player = mysql_num_rows($rs_Player);

mysql_select_db($database_anchor_pool, $anchor_pool);
$query_rs_Result = "SELECT * FROM results";
$rs_Result = mysql_query($query_rs_Result, $anchor_pool) or die(mysql_error());
$row_rs_Result = mysql_fetch_assoc($rs_Result);
$totalRows_rs_Result = mysql_num_rows($rs_Result);

mysql_select_db($database_anchor_pool, $anchor_pool);
$query_rs_PlayerResult_Order_id = "SELECT * FROM player, results WHERE player.id = results.id ORDER BY results.won DESC";
$rs_PlayerResult_Order_id = mysql_query($query_rs_PlayerResult_Order_id, $anchor_pool) or die(mysql_error());
$row_rs_PlayerResult_Order_id = mysql_fetch_assoc($rs_PlayerResult_Order_id);
$totalRows_rs_PlayerResult_Order_id = mysql_num_rows($rs_PlayerResult_Order_id);
?>

The following is the important bit:
SELECT * FROM player, results WHERE player.id = results.id ORDER BY results.won DESC
No, I don't want to calculate the average outside the query--I want to do it inside the query (though I am more interested to know how this kind of thing is normally done)...

Also, surely that's 1 generation (and no problems so far--though I haven't done much SQL on the live server!)?

[chazzy]

first, rewrite your query


SELECT * FROM player, results WHERE player.id = results.id ORDER BY results.won DESC

to include a list of all the fields you want to return. I have no idea where you are storing the wins and losses. Add in addition to all of those columns, this column:

(wins - losses) AS average

Where wins and losses are the names of the respective wins and losses columns in the table. I have no idea if mysql 3 will support this query - it is not a supported platform.

and no, it's 2 generations on each platform as php when 4.3 -> 4.4 -> 5 and mysql went 3 -> 4.0 -> 4.1, both as major builds. you will run into many issues using mysql 3 as it only had limited support for joining tables.

[atw]

Awesome, it worked:
SELECT *, player.id AS pid, results.id AS rid, (results.won - results.lost) AS average, (results.won + results.lost) AS played FROM player, results WHERE player.id = results.id ORDER BY $order $sort
PS. After double checking the MySQL version (this time with phpMyAdmin rather than phpinfo()) I have found out it is MySQL 4.0.* (can't remember the sub-number)...

PPS. Thank you very much chazzy :)