mySQL problem: ORDER BY (link_totalrate / link_numvotes)

[Skunk]

I've just released version 1.21 of my open source PHP links script ssLinks. Unfortunately there are still reports of a bizzare bug in the "Top Rated" function. I have been unable to recreate the problem some people are having and I have a suspicion that the people who are having the problem may be using an earlier version of mySQL.

Here's the SQL that is causing the problem:

$links_sql = "SELECT * FROM sslinks WHERE link_validated = 'yes' AND link_numvotes > $minvotes ORDER BY (link_totalrate / link_numvotes) DESC LIMIT 0,$nol";

It's meant to display the top $nol (usually set to 10) rated links from the links database. A link's rating is calulated using link_totalrate / link_numvotes. $minvotes is the minimum number of votes needed for a link to show up on the top rated list (to prevent links with only one vote coming top).

A better example of what this does can be seen here:

http://www.tfc-central.co.uk/sslinks...p?action=rated

The code works fine on the machines I have tested it on, but some of my users are reporting that they get a database error on that page.

I'm sure the thing that's throwing it is the ORDER BY clause, but I'm not sure how to achieve the same effect without using that syntax...

Any ideas?

[freakysid]

I don't know if this is your problem, it looks interesting. From the Manual (Chapter 7.4):

Note that if you are using MySQL Version 3.22 (or earlier) or if you are trying to
 follow ANSI SQL, you can't use expressions in GROUP BY or ORDER BY clauses. You
 can work around this limitation by using an alias for the expression: 

mysql> SELECT id,FLOOR(value/100) AS val FROM tbl_name
           GROUP BY id,val ORDER BY val;

http://www.mysql.com/documentation/m...Reference.html

Just to add to that, I believe what it is saying is that in older versions you must use a column name or an alias in your ORDER BY clause.

[Skunk]

freakysid I'm certain that's the problem. I looked through the mySQL manual but completely missed that vital paragraph. Thanks