Query Issue (problem with sorting)

[ajs83]

I have a number of items in a database and I group them together by each's series number (ranges from 1-20). The query then sorts them by series ascending from 1-20, but does so by sorting all the ones that begin with 1, then 2, etc where the 10-19 series show up before 2-9. I know why it's happening but is there a way around it?

[sned]

What data type is the series number being stored as? If it is integer, I would think that it would sort just fine, but if it were text or varchar or something, I think it might sort like that.

-sned

[ajs83]

It is stored as text because there are some bits on different sections that have text in as well.

[Burner]

I'm not really familiar with other databases, but if you're using MySQL, it's definitely the datatype of your field in your MySQL table. CHAR, VARCHAR, TEXT type fields sort like this:

28
29
3
30
31

If you can't change your datatype and you're not retrieving a huge dataset, then I'd load them into a PHP array and then sort the array. Not the most efficient way to do it, but it works for me... If it was a larger dataset, I'd redo my table.

Burner

PS if it's not MySQL, I apologize...

[ajs83]

Is there another type that I could use text with that would work?

[grandpa]

Is it possible to use a leading zero on your numbers below 10? I think that will solve the sort problem.

[Burner]

I'm not following how your items are identified, but in text type fields sorting happens alpha from left to right, hence 2 is greater than 19 but less than 20 because 2 is only 1 character long and 2 is greater than the 1 in the first position of 19 and one less character than 20.

The fix is to set the datatype of your field to one of the numeric types like DECIMAL, DOUBLE, FLOAT, INT instead of a text type field. then MySQL will sort according to number rules instead of alpha rules.

Or if you don't retrieve large datasets you could just retrieve the data from the MySQL into an array and sort the array. Most easily done for me using the following two functions:

[php.net...]
[php.net...]

The most efficient always IMHO is always to let the MySQL do it if possible. MySQL data structure makes or breaks a good website.

My 2 cents...

Burner

[ajs83]

In the database most items are set up as a text entry where a number between 1-20 entered.

The reason I set it up as "text" was occassionally there are items that require a text entry instead of the 1-20 number.

Is it possible to use a leading zero on your numbers below 10? I think that will solve the sort problem.

Yes, it's possible, but I'd prefer to use a different method.

[digitalv]

A better solution to this may be to re-evaluate the way you've designed your database. I would suggest making a separate table with two fields:

ID (int/primary key)
IDText (varchar, text, whatever you're using)

Now go ahead and insert the numbers 1 through 20 in order where both ID and IDText are the same. Then continue onward with your text-based after that starting with 21. If you think you'll ever use more than 1-20, start your text-based ID's at a higher number like 1,000.

Then modify your original table - replace the TEXT values with the corresponding ID number from the new table, and change the field type to int. From now on when you do a select, you can do a join to the new table to be able to read the value but when you're sorting the data you can just order by the fieldname and now, as a numeric field, everything will be in order.

[Burner]

/agree digitalv

You need to use a numeric field to sort the way you're looking to and that solution will definitely work great for you without too much rewrite.

Database design is so crucial that I've actually dumped and rewritten entire projects I wasn't completely happy with.

Burner