Jump to content


Photo

**SOLVED** MySQL sorting errors


  • Please log in to reply
10 replies to this topic

#1 JasonLewis

JasonLewis
  • Members
  • PipPipPip
  • Advanced Member
  • 3,351 posts
  • LocationVictoria, Australia

Posted 23 April 2006 - 11:05 AM

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!
Good luck with your coding.
Jason / ProjectFear / Jaysonic

#2 ypirc

ypirc
  • Members
  • PipPipPip
  • Advanced Member
  • 54 posts

Posted 23 April 2006 - 08:58 PM

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

#3 JasonLewis

JasonLewis
  • Members
  • PipPipPip
  • Advanced Member
  • 3,351 posts
  • LocationVictoria, Australia

Posted 24 April 2006 - 09:31 AM

[!--quoteo(post=367780:date=Apr 24 2006, 06:58 AM:name=ypirc)--][div class=\'quotetop\']QUOTE(ypirc @ Apr 24 2006, 06:58 AM) View Post[/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?
Good luck with your coding.
Jason / ProjectFear / Jaysonic

#4 Yesideez

Yesideez
  • Members
  • PipPipPip
  • Advanced Member
  • 2,334 posts
  • LocationDevon, UK

Posted 24 April 2006 - 09:43 AM

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...
Not a pro just an enthusiast :)

if (empty($coffee)) {$coffee=new coffee();}

Please surround any code using the CODE tags - I rarely look at anything without them

#5 wisewood

wisewood
  • Members
  • PipPipPip
  • Advanced Member
  • 226 posts
  • LocationRotherham, England

Posted 24 April 2006 - 09:52 AM

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.

wisewood: proven fact, I am both wise, and wooden.

#6 Yesideez

Yesideez
  • Members
  • PipPipPip
  • Advanced Member
  • 2,334 posts
  • LocationDevon, UK

Posted 24 April 2006 - 10:06 AM

Without code to see how you're sending it to the browser its tricky to diagnose properly.
$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++;
}
Thats the code I'd use.
Not a pro just an enthusiast :)

if (empty($coffee)) {$coffee=new coffee();}

Please surround any code using the CODE tags - I rarely look at anything without them

#7 JasonLewis

JasonLewis
  • Members
  • PipPipPip
  • Advanced Member
  • 3,351 posts
  • LocationVictoria, Australia

Posted 25 April 2006 - 01:41 AM

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!
Good luck with your coding.
Jason / ProjectFear / Jaysonic

#8 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,023 posts

Posted 25 April 2006 - 10:25 AM

How is the 'arcade' table defined. What column type is score?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#9 JasonLewis

JasonLewis
  • Members
  • PipPipPip
  • Advanced Member
  • 3,351 posts
  • LocationVictoria, Australia

Posted 27 April 2006 - 07:51 AM

the score column is a 'varchar' field. would that have anything to do with it?
Good luck with your coding.
Jason / ProjectFear / Jaysonic

#10 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,023 posts

Posted 27 April 2006 - 11:59 AM

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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#11 JasonLewis

JasonLewis
  • Members
  • PipPipPip
  • Advanced Member
  • 3,351 posts
  • LocationVictoria, Australia

Posted 28 April 2006 - 11:19 PM

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! :)
Good luck with your coding.
Jason / ProjectFear / Jaysonic




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users