[RESOLVED] MySQL orderby

[perpetualshaun]

I have an e-Commerce system where the client has Product Numbers that contain both letters and numbers. So the field type has to be VARCHAR. Is there something I can add to my SQL query though so that the 51 will some before 101?

[jennifer_ann]

You might want to do something like this ORDER by ASC this will order the numbers in ascending order so that you start with 1 or 51 or whatever and work all the way up to 101 or 201.

[Roger_Ramjet]

The only way to do this is to left-pad the column data with zeros to a fixed length string. Now, you can do this at run time in sql, but it is an unneccessary load on the server.

The pragmatic solution is to add a char column of say 50-100 chars and fill it with the left-padded data on insert and update. You then use that column just for the sort order, but display the real Product Number column. Easy enough to write a php function that left-pads the data with zeros to a fixed length and then use it when building all insert and update queries.