Ordering results by self defined parameter

[mrchuckles2002]

Hi,

I'm not entirely new to this forum, I had another account but I can't remember the username!

Just wondered if anyone can help me with this poser.

My site has loads of links on it and I want to order the links by the average vote. (i.e to create a highest rated links page) However to get the average vote I need to divide the rating (row called 'link_rating') by the number of votes ('link_num_votes').

I have used the following code, with no joy. Any help or guidance will be most appreciated. I'm sure there is a simple solution.

Thanks in advance.

$query = "SELECT * FROM `links` WHERE `link_type` = 'unofficial' AND `link_status` != 'pending'";

$result = mysql_query($query) or die ('Unable to show links.');

while ( $rows = mysql_fetch_array($result) ) {
$link_id = $rows['link_id'];
$link_type = $rows['link_type'];
$link_title = $rows['link_title'];
$link_description = $rows['link_description'];
$link_url = $rows['link_url'];
$link_num_votes = $rows['link_num_votes'];
$link_rating = $rows['link_rating'];
$link_status = $rows['link_status'];
$link_out = $rows['link_out'];

if ($link_num_votes || $link_rating == "") {
$average_vote = $link_rating / $link_num_votes;
$average_vote = number_format($average_vote, 2);
} else {
$average_vote = "0";

$query1 = "SELECT * FROM `links` WHERE `link_type` = 'unofficial' AND `link_status` != 'pending' ORDER BY '$average_vote' desc LIMIT 25";
$result = mysql_query($query1) or die ('Unable to show links.');


echo ("$link_title - $average_vote<br>"); }
 

[Oersoep]

Try something like this:

$query = "SELECT *, IF( link_num_votes = 0, 0,  link_rating / link_num_votes ) as average_vote FROM `links` WHERE `link_type` = 'unofficial' AND `link_status` != 'pending' order by average_vote desc";

The IF() is important to prevent divisions by zero.

[mrchuckles2002]

Yep that works. thanks