Soldato
- Joined
- 24 Nov 2002
- Posts
- 16,378
- Location
- 38.744281°N 104.846806°W
In my php/mysql, the order is coming literally: 1, 10, 11, 2, 3.... Is there an easy way to prevent this? it is most annoying.
10 before 2
- Thread starter meghatronic
- Start date
- Joined
- 24 Nov 2002
- Posts
- 16,378
- Location
- 38.744281°N 104.846806°W
ORDER BY won't return a natural order.
Worked. I had to change the order of my functions though, cheers!Augmented said:
Last edited:
For reference, you might want to check the type of the number field as Adz suggested. Won't make a massive difference, but should offer some speed optimisation over using natsort() instead of MySQL's native sorting.
It appears that your numbers coming out of mysql are being treated as strings. If it's not an integer type (smallint, int etc.) then ORDER BY won't sort it numerically - change the field type to INT or such, and try ORDER BY again
.
It appears that your numbers coming out of mysql are being treated as strings. If it's not an integer type (smallint, int etc.) then ORDER BY won't sort it numerically - change the field type to INT or such, and try ORDER BY again
.
Yeah, you're ID column defo shouldn't be a string! Make sure it's an integer number. Also, I think the best you can do with a string is a full-text search (correct me if I'm wrong, I've never tried to put anything else on a string).
But with an Integer number, you can set it as a unique key which will speed up searching through the database for things when using the ID field Then you can also set it to auto_increment and not need to worry about checking whether the key exists before adding it to the table 
But with an Integer number, you can set it as a unique key which will speed up searching through the database for things when using the ID field
Then you can also set it to auto_increment and not need to worry about checking whether the key exists before adding it to the table
- Joined
- 24 Nov 2002
- Posts
- 16,378
- Location
- 38.744281°N 104.846806°W
It's not an ID column, it's a string e.g. sausage7, I'm sorting by the numbers on the end - but thanks though!
Oh, right 
I know you got a solution now but I'm going to ramble on anyway
You could have 'name' and 'occurance' in the table, so Sausage in the name field an 7 in the occurance field. Or when you put it into the database, you could use php to format it so it's sausage0007 which would allow for 9999 occurances of 'sausage', and the 0's would keep them in the correct order.
Doing one of those would mean you won't need to do any phpness, could keep all the ordering in the mysql query But it would also mean changing the way your database works, and seeings you already have a solution there's probably not much point...
But yeah, could be handy knowledge for future reference
I know you got a solution now but I'm going to ramble on anyway
You could have 'name' and 'occurance' in the table, so Sausage in the name field an 7 in the occurance field. Or when you put it into the database, you could use php to format it so it's sausage0007 which would allow for 9999 occurances of 'sausage', and the 0's would keep them in the correct order.
Doing one of those would mean you won't need to do any phpness, could keep all the ordering in the mysql query
But it would also mean changing the way your database works, and seeings you already have a solution there's probably not much point...But yeah, could be handy knowledge for future reference
- Joined
- 24 Nov 2002
- Posts
- 16,378
- Location
- 38.744281°N 104.846806°W
No - I'm already working on a restructuring, this was just an interim solution. As you pointed out, the more mysqlness... the less phpness which is better and quicker.