Mysql Order By Is Not Working As Expected

[kalster]

in this example there are 10 scores starting from 1 to 10. so in this code if the $score was 5, then the mysql would query 10, 9 ,8 ,7, 6 but i would like it to query 6, 7, 8, 9, 10. how can i do this?

 

$query = "SELECT score
FROM users
WHERE score >= $score
ORDER BY score DESC
LIMIT 5";

[Pikachu2000]

Then you need to ORDER BY score ASCending instead of DESCending . . .

[kalster]

yes that works, but when i use php to fetch the mysql query, php stores the $result in the $row starting from the least value to the greatest value. how to reverse this?

 

$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_assoc($result)){...

[Pikachu2000]

What?

[kalster]

i am trying to output the mysql results to a html table using php and starting from the highest value of score to the lowest value. the problem is that with ASC is that it finds the lowest to highest values which is good but how can i tell php to output the mysql results starting from the lowest query to highest.

 

this line of code is outputting the query from lowest to highest because that it what mysql did. now i need to reverse it.

while($row = mysql_fetch_assoc($result)){

[Pikachu2000]

That's the exact opposite of what you said you wanted to do, and is how it should have been to begin with: i would like it to query 6, 7, 8, 9, 10. How about explaining exactly what you're trying to accomplish.

[kalster]

i did manage to find a fix to this problem using sub queries. here is the code that solved this topic. so this code gets the next highest values from score which is exactly what i wanted and then reorders them so that i can output them starting from highest to lowest. see, if there were 1000 users each with a different score, the DESC would take to top five scores in the query. instead i needed the next five scores that were higher than the logged in user. the sub query reorders the list so that i can then output it using php to a html table. i hope you understand and thank you for your help.

 

$query = "SELECT * FROM

(SELECT score

FROM users

WHERE score >= $score

ORDER BY score ASC

LIMIT 5) subq

ORDER BY score DESC";

Edited December 17, 2012 by kalster

[kalster]

i have another problem with mysql and i posted it here since it is similar to this topic.

 

basically i am trying to output the next five highest scores than the user that is logged in and along with the current rank of those users. so if i had a database of 100 users, i would like to output the rank of those users based on the 100 users in the database along with the next highest score than the user logged in. the table outputted would look similar to the following.

 

 

rank user score

34 tom 5601

35 kate 5430

36 bill 4868

37 jill 3976

38 fred 3875

 

the mysql code below works for the fields user and score but not for rank. the php does not output rank for some reason. in the database, there is field for user and score but not for rank. i am thinking that there does not need to be a rank in the database since the mysql query has @rownum := @rownum+1 AS rank.

 

here is the php code below.

 

$query = "SET @rownum := 0;";
$query = "SELECT * FROM
(SELECT @rownum := @rownum+1 AS rank, user, score
FROM users
WHERE score >= $score
ORDER BY score ASC
LIMIT 5) subq
ORDER BY score DESC";

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_assoc($result)){
echo "{$row['rank']} {$row['user']} {$row['score']}";
}

[Barand]

You are not executing the first query, just defining a string

[kalster]

i fixed the problem but now the rank does not number correctly. the rank variable counts from 1 to 5 but what i need is a rank based on the users in the database. so if there is 100 users, then the logged in user would have a rank of lets say 20 and each next score higher than that user would have a rank of 21, 22, ect. how can i get this to display the rank correctly?

Edited December 19, 2012 by kalster

[Barand]

You need an inner subquery which ranks all rows. This will also remove the need for your pre-query to declare @rownum

 

SELECT * FROM
   (
   SELECT rank, user, score
   FROM
    (
    SELECT @rownum := @rownum+1 AS rank, user, score
    FROM users
    ORDER BY score ASC
    ) rankings
    JOIN (SELECT @rownum:=0) initialise
   WHERE score >= $score
   LIMIT 5
   ) subq
ORDER BY score DESC

[kalster]

thank you Barand, the code works great just a note... i did have to use the pre-query to declare @rownum because the join code did not work

[Barand]

Sorry, JOIN in wrong place, should have been

 

SELECT * FROM
       (
       SELECT rank, user, score
       FROM
               (
               SELECT @rownum := @rownum+1 AS rank, user, score
               FROM users
                 JOIN (SELECT @rownum:=0) initialise
               ORDER BY score ASC
               ) rankings                
       WHERE score >= $score
       LIMIT 5
       ) subq
ORDER BY score DESC