pwnuspoints Posted January 28, 2009 Share Posted January 28, 2009 Hey phpfreaks! As always; your help is appriciated- Thanks in advance! I have several dates housed as text strings in a table. Each date is written in date("F j, Y") format [e.g. "January 28, 2009"]. When I display those dates on a php page, it has a tendancy to order them alphabetically-- which I find confusing. So, I'm trying to use the STR_TO_DATE sql function to display these strings in by date; it's not working. My query looks like this: $result = mysql_query("SELECT * FROM table WHERE display LIKE '1' ORDER BY STR_TO_DATE('createdate', 'F j, Y') DESC "); What am I doing wrong? Thanks Again. Quote Link to comment https://forums.phpfreaks.com/topic/142826-using-str_to_date-to-sort/ Share on other sites More sharing options...
Sudden Posted January 28, 2009 Share Posted January 28, 2009 http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date Might help you out. Quote Link to comment https://forums.phpfreaks.com/topic/142826-using-str_to_date-to-sort/#findComment-748686 Share on other sites More sharing options...
severndigital Posted January 28, 2009 Share Posted January 28, 2009 agreed it is a syntax issue just take the quotes out of your first parameter and it should work. Quote Link to comment https://forums.phpfreaks.com/topic/142826-using-str_to_date-to-sort/#findComment-748699 Share on other sites More sharing options...
pwnuspoints Posted January 28, 2009 Author Share Posted January 28, 2009 My query now reads as follows: $result = mysql_query("SELECT * FROM table WHERE display LIKE '1' ORDER BY STR_TO_DATE(createdate, '%F %j, %Y') ASC "); >.> it appears to be ordering them by my primary key instead of by date. This is so frustrating. Is what I'm trying to accomplish even possible? Quote Link to comment https://forums.phpfreaks.com/topic/142826-using-str_to_date-to-sort/#findComment-748737 Share on other sites More sharing options...
PFMaBiSmAd Posted January 28, 2009 Share Posted January 28, 2009 '%F %j, %Y' is not correct. Based on reading the page in the mysql manual, it should be '%M %e, %Y' or '%M %d, %Y' depending on if your days have leading zeros. Doing your query this way is exceedingly slow and the current format you are using takes several times the amount of storage that using a DATE data type would. You should convert to use a DATE data type. Quote Link to comment https://forums.phpfreaks.com/topic/142826-using-str_to_date-to-sort/#findComment-748770 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.