sorting problem

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Mobic
Forum Newbie
Posts: 5
Joined: Wed Feb 12, 2003 3:03 am

sorting problem

Post by Mobic »

if I use:

Code: Select all

$ranktable = "SELECT  number
FROM users ORDER BY number DESC";
I get this result:

12
11
105
10

but I would like it to be:

105
12
11
10
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

What kind of field do you have that number stored in? VARCHAR or an INT type?

Mac
Mobic
Forum Newbie
Posts: 5
Joined: Wed Feb 12, 2003 3:03 am

Post by Mobic »

varchar
pootergeist
Forum Contributor
Posts: 273
Joined: Thu Feb 27, 2003 7:22 am
Location: UK

Post by pootergeist »

varchar will sort it as though it is a string - as you have noticed. int will sort it as an integer 2 is lower than 10 sort of thing.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

[Edit: Oops too slow - but the links may still be useful]

That's your problem then, the number is being sorted that way because VARCHAR fields are intended for text so whatever's in them will be sorted as a text string. If you want to get the sorting right try a numeric type column like SMALLINT or INT,

More info on INT fields:
http://www.mysql.com/doc/en/Numeric_types.html
http://www.mysql.com/doc/en/Choosing_types.html

Mac
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

if for some (very wreid) reason you cannot change the field type anymore order by CONVERT(number, unsigned) desc might help. But consider it the last resort.
Mobic
Forum Newbie
Posts: 5
Joined: Wed Feb 12, 2003 3:03 am

Post by Mobic »

Thanks, it's working now.

One more question;

I want to collect data for a mysql database, add them together and print the result.

Example:

user1 got 23 dollars
user2 got 2 dollars
user3 got 5 dollars
....
user3453 got 6 dollars


what do you do when you should add those numbers, when they are stored in the same field in the database.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

change the database structure ;)
Post Reply