**SOLVED** MySQL sorting errors

[JasonLewis]

Hey there! My first topic. Anyway... i have a member system, which is nearly complete and its kickass. but thats not the point. i have been killing myself over the arcade for members. when i get the scores i am sorting it by the score from highest to lowest...

but the thing is that it does that, but it does it by the username of the member first.

When i insert the data into the table i have a field for the game id, a field for the username, and field for the score and a field for the date. (i also have id so i can delete later on if i want to). now it sorts by the score but it groups the usernames together...

WHY IS IT DOING THIS!!!!???? please help someone cuz its really annoying...

thankyou in advance!

[ypirc]

Give us sample data, expected output, and the query you are currently using that is giving you unexpected output.

[JasonLewis]

[!--quoteo(post=367780:date=Apr 24 2006, 06:58 AM:name=ypirc)--][div class=\'quotetop\']QUOTE(ypirc @ Apr 24 2006, 06:58 AM) [snapback]367780[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Give us sample data, expected output, and the query you are currently using that is giving you unexpected output.
[/quote]

ok. sorry... well here is the query that i am running:
$query = mysql_query("SELECT * FROM `arcade` WHERE `game_id`='$game_id' ORDER BY `score`");

this is what it is showing:
Rank Member Score Date
1 The One 860 23 April 2006
2 The One 57 23 April 2006
3 saiko 365 23 April 2006

this is what it should be showing:
Rank Member Score Date
1 The One 860 23 April 2006
2 saiko 365 23 April 2006
3 The One 57 23 April 2006

but for some reasona s u can c above it is grouping the usernames first, then it orders the score!!! anybody have any ideas?

[Yesideez]

Can you show the piece of code from the query that is sending the data to the browser? I have a feeling the problem lies there...

[wisewood]

do you have a timestamp in the table where your scores are kept, or even better, an auto-increment integer field used as the primary key?

If you have an auto-increment (we'll call this 'IDfield') do this;

$query = mysql_query("SELECT * FROM `arcade` WHERE `game_id`='$game_id' ORDER BY score, IDfield DESC");

This will select the scores highest to lowest, but will display them in order of when they were entered into the database table, rather grouping them by username.

[Yesideez]

Without code to see how you're sending it to the browser its tricky to diagnose properly.
[code]$position=1;
$query=mysql_query("SELECT * FROM `arcade` WHERE `game_id`='$game_id' ORDER BY `score` DESC");
while ($fetch=mysql_fetch_array($query)) {
  echo 'Position '.$position.': '.$fetch[name].' - '.$fetch[score].' - '.$fetch[date]."\n";
  $position++;
}[/code]
Thats the code I'd use.

[JasonLewis]

this is the code that is inserting into the table. i am getting the score from flash, which is all working properly.

here is the code that inserts it:

$insert = "INSERT INTO `arcade` (`game_id`, `player`, `score`, `date`) VALUES ('$game_id', '$player', '$score', NOW())";
$result = mysql_query($insert);

and Yesideez, how your doing it is pretty much exactly the same except mine is in a table. anyone? i need this fixed? thanks!

[Barand]

How is the 'arcade' table defined. What column type is score?

[JasonLewis]

the score column is a 'varchar' field. would that have anything to do with it?

[Barand]

As varchar, any preceding spaces are going to affect the sort order. Also you get 1, 10, 15, 2 if it is a text sort.

INT would be lot better

[JasonLewis]

ok thanks, i'll give it a go and update this when i have finished. hopefully it works.

EDIT:
omg, thanks so much barand it worked. i'll remember this... thanks to everyone else as well! :)