i need to sort a group of rows of data from mysql
say i do:
$i = select * from tablex where register=1

and it comes back as an unsorted bunch of data. say there is a field called, age, and i would like to sort all the rows by age, and then display the rows in a simple table or the like.

i am looking into using php sort(); but it seems like there may be a simpler way.

anyone here have any suggestions for me?

thanks, everyone on these forums is extremely helpful! thanks everyone

EDIT btw upon further research it seems i can ORDER BY, however this will still not work, as the data is stored like this:
XXXX_age
and i usually just explode("_",$row["age"])

Take a look at usort() -> http://ca3.php.net/manual/en/function.usort.php

EDIT btw upon further research it seems i can ORDER BY, however this will still not work, as the data is stored like this:
XXXX_age
and i usually just explode("_",$row["age"])
What is the "XXXX"? You might look towards restructuring your database table a little so that you can sort by age in the SQL instead of having to sort in PHP.

Actually, you can use ORDER BY here -- you just need to get a bit fancy with your SQL.

This works :


CREATE TABLE string_test (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, tkey VARCHAR(8));

INSERT INTO string_test (id,tkey) VALUES ('','XXX_10'), ('','XXX_15'), ('','XXX_30'),('','XXX_1');


Note that I deliberately inserted values in a "random" order

Now, to get the results ordered by the digit after XXX_ you use the following query :

SELECT * FROM string_test ORDER BY CAST(SUBSTRING(tkey FROM 5) AS SIGNED);

This will return the rows ordered by the digit after XXX_

i am gonna not sort by age, i am going to do by registration time instead.
the way the data is stored is:

second_minute_hour_day_month_year_dayofyear(out of 365)_unixtimestamp.

i dont know enough about the use of date, but is it possible to convert that unixtimestamp into second_minute_hour_day_month_year_dayofyear data? then all i would need is to store the unixtimestamp, and could sort very easily with mysql order by command.

uh, wow, i am ignorant of the function of date. i am just going to use unixtimestamp, and use date to convert that into second_minute_hour_day_month_year_dayofyear.

that way i can orgnaize everything with unixtimestamps.

seems simply enough, cant beleive i overlooked that, i only thought date returned the current time.

thanks everyone

Why would you want to store the time, down to the second -- and the day of the year AND the timestamp is beyond me. Just use the TIMESTAMP field type.

ya, i just didnt know you could use date to convert any time other then the current time. i know now though,

This may help with sorting the results after you've pulled them into PHP...

http://us3.php.net/manual/en/function.array-multisort.php

You can also easily switch into a timestamp by using strtotime(thedate);