Jump to content

Archived

This topic is now archived and is closed to further replies.

JasonLewis

**SOLVED** MySQL sorting errors

Recommended Posts

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!

Share this post


Link to post
Share on other sites
Give us sample data, expected output, and the query you are currently using that is giving you unexpected output.

Share this post


Link to post
Share on other sites
[!--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?

Share this post


Link to post
Share on other sites
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...

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
How is the 'arcade' table defined. What column type is score?

Share this post


Link to post
Share on other sites
the score column is a 'varchar' field. would that have anything to do with it?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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! :)

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.