order by date from today, and ignore year

[spiderwell]

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?

[Barand]

example

 

 

SELECT * FROM events
ORDER BY MONTH(thedate), DAY(thedate)

[spiderwell]

thanks barand, i will give it a go now!

[spiderwell]

ok so this works well, but how do i get it to work backwards from todays date, i.e 15th march

[spiderwell]

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

[Barand]

$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";

[spiderwell]

that worked perfect Barand, thanks a bunch!