[PHP] ORDER BY mySQL problem

[sofajoe]

OK, i have made the user name, scores and time to be recorded into a MySQL table and i have managed to have this data sent back to my swf - but it is showing the lowest score at the top - how to switch it and make the highest take the number 1 postition?

here's my code:
$sql = "SELECT * FROM $table_name ORDER BY total";
$result=mysql_query($sql, $db);

this works - but i tried adding DESC
$sql = "SELECT * FROM $table_name ORDER BY total DESC";
$result=mysql_query($sql, $db);

and this makes it not work!
any suggestions?

[Liammc]

If DESC if having the reverse effect then try ASC?

[sofajoe]

No, DESC makes the data not load in my swf at all.

without it the script loads the scores into my swf fine. once i add DESC i no longer see any data?

[Liammc]

Ah I read it wrong. Odd. Try ordering it by another column, such as name just to see if it works?

I'm no expert here, so I can't help too much, but good luck!

[sofajoe]

thanks Liammc, i order it by time and the data appeared with the top scorse at the top.

cheers

[Liammc]

I see. But what does time record, when the score was set? If so is it possibly coincidental? It does seem slightly odd.

If not, then good for you that the problem is fixed!

[sofajoe]

time recorded the time to complete the tasks, while score was how many tasks you did/time*200. So i was completing all the tasks (as they were very easy as i was just practicing how to get data from & then back into a flash movie). And because time was part of the calculation for the final total, who ever finished quickest had the most points.

But this means my original problem still exists!
it's alright in this instance, but won't always be the case.

any sugesstions to my original queiry?
how to switch the order around?

[Liammc]

What data format is the score being held as?

[sofajoe]

i presume you mean in the mysql databse?
- its being stored as varchar


What data format is the score being held as?

[expensive_pen]

if i understand what your saying correctly...
MySQL is sorting by ascii values instead of number's sense


i presume you mean in the mysql databse?
- its being stored as varchar

you don't want it to be a varchar an int or float or double, im not sure if you can cast it at runtime but maybe hope that help

[expensive_pen]

i figured out a little more information MySQL converts type when needed
but since it doesn't think it needs to convert a literal to a number it doesn't
you can force by performing arithmetic operators
so this should work


$sql = "SELECT * FROM $table_name ORDER BY (total * 1)";
$result=mysql_query($sql, $db);