spiderwell Posted March 15, 2013 Share Posted March 15, 2013 Hi all I have a datetime field on my database table, and the rows have dates of events over the last hundred years, and I want to somehow order by month and day from todays date but ignore the year, so i guess that is a group by daymonth type thing. the idea being when you hit the page, you will see anything that happend on this day but in any previous years too, and then work backwards in day/months. so say i had 3 dates, a)1st april 2013 b)1 march 2005 c)1st april 2000 the desired order would be a,c,b , if the query was run on 1st april, but if it was run on 1st march the order would be b,a,c. I hope that makes sense. I tried this: GROUP BY DAYOFMONTH(item_date) ORDER BY `item_date` DESC which gets the order correct, but i then need it to associate with current date, so i tried this but it just threw me an empty record set: WHERE DATE(item_date) = DATE(NOW()) GROUP BY DAYOFMONTH(item_date) ORDER BY `item_date` DESC anyone have any pointers or ideas? Quote Link to comment Share on other sites More sharing options...
Barand Posted March 15, 2013 Share Posted March 15, 2013 example SELECT * FROM events ORDER BY MONTH(thedate), DAY(thedate) Quote Link to comment Share on other sites More sharing options...
spiderwell Posted March 15, 2013 Author Share Posted March 15, 2013 thanks barand, i will give it a go now! Quote Link to comment Share on other sites More sharing options...
spiderwell Posted March 15, 2013 Author Share Posted March 15, 2013 ok so this works well, but how do i get it to work backwards from todays date, i.e 15th march Quote Link to comment Share on other sites More sharing options...
spiderwell Posted March 15, 2013 Author Share Posted March 15, 2013 ok still working on this, I am now at this point, were i want to just have a week span from today, so 15march back to 8th march, but any year WHERE DAYOFYEAR(item_date) <= DAYOFYEAR(NOW()) AND DAYOFYEAR(item_date) >= DAYOFYEAR(NOW()-7) ORDER BY MONTH(item_date), DAY(item_date) DESC it works with 1 WHERE parameter, i.e. less than today, but not with both, I also tried using BETWEEN instead of <= >= and it made no difference. thanks Quote Link to comment Share on other sites More sharing options...
Barand Posted March 15, 2013 Share Posted March 15, 2013 $end = date('m-d'); $start = date('m-d', strtotime("-7 days")); $sql = "SELECT * FROM events WHERE DATE_FORMAT(thedate, '%m-%d') BETWEEN '$start' AND '$end' ORDER BY DATE_FORMAT(thedate, '%m-%d') DESC"; Quote Link to comment Share on other sites More sharing options...
Solution spiderwell Posted March 16, 2013 Author Solution Share Posted March 16, 2013 that worked perfect Barand, thanks a bunch! Quote Link to comment 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.