Sorting Database Results

chrisdarby

Hi Everyone,

I've got a slight problem with a query that I am processing, they query is as follows

"select * from categories order by categoryident"

Now... categoryident is a "char" field, and ordering by it is fine, however, the information in the field is in the form of

1
1.1
1.2
2
2.3
etc etc

So logically it should order as i have shown above... however, when I get to 10 and up... it orders it like this.

1
1.1
1.2
11
11.1
11.2
2
2.3
etc etc

how do i get it to treat those values as numbers so that it will order numerically, I can't change the field it a "float" because some of the entries have text in them.

Any ideas ?

niroshan

well to answer your question can you please tell me what are you intent to keep in the 'categoryident' field 😕

I can't change the field it a "float" because some of the entries have text in them

why do you want to store text in that field? can you give us some example texts that you store there.

Regards,
Niroshan

chrisdarby

for example..

1
1.1
1.1a
1.1b
1.2
1.2a

niroshan

I think the easiest way is to keep an additional field and insert only the numeric part into that. So later when you query the database you can order it by that field. Because ordering through SQL statement is much faster and more efficient than try to order it through PHP. 🙂