Forums

simple(?) php question from noob

Permalink
so there's a line of code

$db->Query("SELECT * FROM btXXX ORDER BY r_dt $ordering, bID $ordering");


but r_dt is in xx/xx/xxxx format, so it's ordering by month then day then year - so how do I change r_dt so it sorts from most recent to oldest? $ordering is set up for ASC or DESC preference.
jleinbaugh
 
WanderingWombat replied on at Permalink Reply
what is the data type that is holding date in your db. Date, datetime, and timestamps are supposed to be held in a YYYY-MM-DD format. Since your date is in mm/dd/yyyy order, it sounds like you are storing the date as a string of some sort, and not as a date. If this is the case, you will be sorting as a string (and continue to encounter the sorting problem that you are) and will not be able to sort it as a date.

My suggestion is to change the data type of your db field to 'date' data type. Then you will be able to sort by the dates chronological order.

Also, I notice that you are sorting by two seperate columns 'r_dt' and 'bID', this may also be causing unexpected results. I assume that your variable $ordering is either 'ASC' or 'DESC', but if it is something else, this too could cause unexpected results.

Hope that helps.

Remember, if you change the data type for the r_dt column, you will have to make sure your update and insert queries format the date correctly (YYYY-MM-DD)
jleinbaugh replied on at Permalink Reply
jleinbaugh
I ended up replacing r_dt with STR_TO_DATE('r_dt', '%m/%d/%Y')