sorting times

[ripken204]

im grabbing values from a mysql db using php.

ex times:
10:11.60
8:19.91
9:43.66

when i query the values i simpply order by time which is does, but incorrectly. if you look above thats the order it prints in. but obviously 10 in higher then 8. if i had values from 1-9 is sorts perfectly. but its thinking of the ten as just a 1, completely disregarding teh 0 right next to it. now how would i go about fixing this?

[Suhas Dhoke]

Use the ORDER BY keyword at the end of query, with the order (ascending or descending)
ex.
ORDER BY 'db_field_name' ASC/DESC

[buntine]

Of which datatype is the field that you are storing the time in? Hopefully it is a datetime compatible type (and not a string literal).

From what you have explained, I would double-check the datatype of the field.

Andrew Buntine

[ripken204]

i already said that i us "order by"

and it is a string... see i have different things that arnt times in there too, such as distances. and 10 feet will appear shorter than 2 feet... so its not just time that i need this done for. basically i just need to check the 1st 2 values, or sort up until the semicolon in the times.. i really hope that there is a way to do this but im feeling kind of hopeless here.

[buntine]

It's not going to happen then! You need to reconsider the structure of your database. This is what datatypes are for.

Your only other option is to first extract all the data and then perform string handling to find the times, add them all to array, and then sort the array.

Andrew Buntine.

[ripken204]

ya i thought of that possibility also, but imagine if i have 100,000 times, adding all of those to an array....

so i guess i could just redo the time format, but what about the distance format? theres no point in having one if i cant have the other...

[buntine]

It is best to seperate this data into seperate fields with appropriate data types. This way complex arithmatic can be performed at the data level (e.g no massive array-sorting).

Andrew Buntine.

[ripken204]

well all i had to do was put a 0 in front of it if it only has a single digit for the min, such as 9:00 would be 09:00

[ripken204]

perfect, i did that with the distances too and its all working perfectly. and when i output the results i have it removing the 0 if it has one so that it doesnt look ugly :)