Selecting members who's birthday is in the next month

[denno020]

In my database I have a field for DOB for each of the records. This field is of mysql type 'date'. I would like to have a script to select only the members who's birthday it will be within the next month.. I'm very unsure as to how to approach the comparison of the current date and the date that is stored in the database...

 

Anyone able to help me?

 

Thanks

Denno

[silkfire]

First let PHP give you the next month:

 

$month = date('n', strtotime('first day of this month next month'))

 

Then use in MYSQL query: WHERE MONTH(birthday_date) = '$month'

[denno020]

wow that is one confusing line; "first day of this month next month".. What does that convert into?

 

I've been Googling and I've come up with this code that will select everyone who's birthday it is in the current month, however I don't want to show people who's birthdays have already passed, and I would like the birthdays from next month, if within 30 days from the current date, to be displayed.. This overlap of months/dates is where I'm falling short..

 

However I'm unsure if your code would achieve what I'm after?

 

Here is the code I'm using at the moment..

SELECT id, name, DOB FROM pp_members WHERE monthname(DOB) = monthname(curdate()) ORDER BY DOB ASC

 

Thanks

Denno

[denno020]

I have managed to solve the problem myself .

 

What I did was convert the date from the database into it's day-of-the-year equivalent, and then compared it to the day-of-the-year of the current date. This worked perfectly .

 

Here it is:

SELECT id, name, DOB FROM pp_members WHERE dayofyear(DOB) < dayofyear(curdate())+30 && dayofyear(DOB) > dayofyear(curdate()) ORDER BY dayofyear(DOB) ASC

 

I'm quite happy with this result .

 

Thanks for the help though.

Denno

[silkfire]

denno, strtotime converts an English date sentence into a date. You need to have 'first day of this month next month' instead of just 'next month' or '+1 month' because if you're on 31 January then +30 days will become March and you'll skip February completely. So what that does is first gives you 1st day of the month and then goes +30 days which guarantees not to skip month. The 'n' is a format that results in a month number 1-12 without a leading zero, a value that the MySQL MONTH() function understands.

[denno020]

Ahk, very clever silkfire.. I would never have considered the whole -February being a short month- problem. Thankyou for your clarification .

 

Denno

[sasa]

what if you run your code in december?

[phpSensei]

Get the NEXT MONTH from the current date, make sure that if you are on Jan, the next month will be 01, and not 13, make sure you change the year also...

 

Get the MONTH from the DOB, check with your date var.. 

 

 

TADAHHHHHH you have a code running.