Problem sorting alphanumeric

ywickham

Hello,

I have the following query (scaled down version, showing only pertinent fields):

"SELECT manufacturer_name, model_name
FROM camera_general
ORDER BY model_name"

As is, a sampling of the results I receive, with the results displayed being the model_name field, are:

Canon PowerShot A10
Canon PowerShot A100
Canon PowerShot A30
FujiFilm FinePix 1300
FujiFilm FinePix 1400
FujiFilm MX-1200
FujiFilm MX-1700 Zoom
FujiFilm MX-600 Zoom

I would like to display in the following order:

Canon PowerShot A10
Canon PowerShot A30
Canon PowerShot A100
FujiFilm FinePix 1300
FujiFilm FinePix 1400
FujiFilm MX-1200
FujiFilm MX-600 Zoom
FujiFilm MX-1700 Zoom

Suggestions?

Thanks!

Kinneh

"SELECT manufacturer_name, model_name
FROM camera_general
ORDER BY model_name"

put a DESC (descend) on the end of the query

"SELECT manufacturer_name, model_name
FROM camera_general
ORDER BY model_name DESC"

ywickham

Thank you for the reply, but adding the DESC keyword to my ORDER BY clause only causes my listing to be displayed in reverse order, like so:

Canon PowerShot A30
Canon PowerShot A100
Canon PowerShot A10

And still does not solve the problem of listing by numerical weight.

Any other ideas, anyone?

Vex

You need to do a 'natural order' sort...

PHP can do this... http://www.php.net/manual/en/function.natsort.php

But I don't think MySQL offers this functionality, so you'd have to read all your values from the database into an array and then use PHP to sort the array, and then output the values...

HTH,

ywickham

Now that's a start. Thanks Vex, as I had just found it in one of my books. Do you know how loading an array may affect the download speed of my page?

stolzyboy

i would suggest splitting up the model name field into manufacturer, model name, letter, number, then you can order by all, none, or whatever ones you wish

$query = "select * from table order by manufacturer, model, letter, number asc";

hope this helps

ywickham

Thanks stolzyboy, but that's not an option for me.

I appreciate your post!

Vex

Originally posted by ywickham
Now that's a start. Thanks Vex, as I had just found it in one of my books. Do you know how loading an array may affect the download speed of my page?

Well, It's all relative really.... If there's only 20 rows then you probably won't notice any difference.... 20 million rows, could be a problem... 🙂

Best suggestion is to try it.. It shouldn't take more than 15 minutes to knock up a test...

Sxooter

You could always do it by creating a new field called "ordernum" or something, and doing the sort there with an external program that runs on a cron job or something, then just use 'order by ordernum' or something like that