 |
Part 1 |
In part one we looked at how to add the Date picker by Jason Moon to the php form.
Now we look at converting the date string format into a DATE_FORMAT that MySQL can use. I can’t take all the credit for this code as the lovely people at StackOverflow helped with my dilema of turning a string DATE into a sortable MySQL DATE_FORMAT.
// Query the database.
$query = “SELECT u.url_id, url, title, description, pub_date, STR_TO_DATE(pub_date, ‘%d-%b-%Y’) AS sortdate, DATE_FORMAT(STR_TO_DATE(pub_date, ‘%d-%b-%Y’), ‘%d.%b.%Y’) FROM urls AS u, url_associations AS ua WHERE u.url_id = ua.url_id AND ua.url_category_id=$type AND ua.approved = ‘Y’ORDER BY sortdate DESC”;
$result = mysql_query ($query);
$num_records = mysql_num_rows($result);
This is the code to extract the date and allow the date field to be sorted as a date, my original problem was that it would sort the date by the day but not the month, I was using only the line of code: (STR_TO_DATE(pub_date, ‘%d-%b-%Y’), ‘%d.%b.%Y’) originally in my query, but June would end up below May when sorting Descending.
The formats for dates such as %d.%b.%Y can be found at the MySQl site under DATE_FORAMT.
But it was Jon Skeet at Stack Overflow that helped me out by providing the missing link. See my My Post at Stackoverflow. I had many issues relating to Date formatting from a string and now it is fine. The above code will also permit you to create an RSS feed because the date can be made to comply with RFC-822 date format required for pub_Date in RSS Feeds. Sweet!!!
If you wish to join stack overflow, then your Gmail account user id will be adequate for registering if you have one. I am so glad I found them.
|
Part 1 |
