assume this table structure:
users {
user_id INT,
user_name varchar(64),
birthday date
}
i want a query that will give me a list of 10 birthdays including birthday from the past 7 days onwards. if multiple people have a birthday on the last listed birthday date, it's ok to cut them off the list.
right now i have this:
SELECT user_id, user_name, birthday
FROM monkeys
WHERE
(MONTH(birthday) = MONTH(NOW()) AND (DAYOFYEAR(birthday)+7) >= DAYOFYEAR(NOW()))
OR MONTH(birthday) > MONTH(NOW())
OR (MONTH(birthday)=1 AND MONTH(NOW())=12)
ORDER BY DAYOFYEAR(birthday) ASC
LIMIT 10
but obviously, when you're in december and wrapping around to january, the sorting will be off.
how can i sort this result set correctly so if we were in december, the result set might look like this:
dec 27th
dec 31st
jan 2nd
jan 6th
etc....
the birthday field is a date field with the year of the birthday as well.
thanks for any help.
