Database-SQL Problems

[Benny Brando]

I know this is probably not the place to post this but on the techie forum I posted nobody knew how to rectify so I was hoping one of the techies on here might be able to help.

Basically in my MySql Database I am having problems sorting the products in alphabetical order.

Basically I have many similar products and want them sorted in order but they aren't and are sorted like this:-

Product 1
Product 10
Product 11
Product 12
Product 2
Product 20
Product 21
Product 3
Product 30
..........................

What I want is for them to be sorted like this:-

Product 1
Product 2
Product 3
Product 4
Product 5
Product 6
.........................

Anybody know how to solve this?

Thanks in advance.

[Jacques]

Not too good with SQL (as you'll see, still learning) but try a SELECT statement ORDER BY clause.

Something like

SELECT prod_name
FROM Products
ORDER BY prod_code;

Here you assign a product code to your product in a column in your table (prod_code) and then the output is sorted from that column. I think that this is legal??

Probably the wrong way to do it and Cenobitez is probably your man, so ignore everything I just said but then again I might just be right. lol!

[andy at handiwork]

Would naming them 01,02,03....10,11,12.....20 etc be any good?

[KayJay]

Why include Product in every field. That uses space in the database. Instead use a numeric reference 1, 2, 3... and sort on that. Store them as integers instead of char.

As has been suggested 01, 02, 03... will fix it.

Add the product bit when you display the field.

[Benny Brando]

Thanks for the replies and for the help. I already have the products listed as #1, #2, #3, #4 etc etc but I was just hoping there was another way to do it and obviously there isn't.

Thanks again.

[Letch]

My only comment is.

Did you set your database with a "Primary Key" with the numbers adjusted in your table to Auto Number. Click on a properties tag and check what is happening behind. To me it sounds also that your colum is set on text not number.

I mainly use Access for all my database tables.

Regards

Letch

[Cenobitez]

Benny in the PHP Code just throw the array to php:natsort()

natsort($sqlproductresult);

Then output as usual

Natsort also maintains the arrays keys and values, so no problems there

[Benny Brando]

Thanks mate and I'll have a go at it when I get back to work tomorrow.

Thanks again to all that posted.

[Cenobitez]

Hey Benny did it work ?