get upcoming birthdays

[proctk]

Hi

 

I have a column in a mysql table which is formated as 'date' called DOB.

 

I want to create a query that will get all birthdays cumming up in the next ten days.

 

any help is excellent

[Caesar]

This would be much easier if you stored dates as timestamps but alas, look into the strtotime() function and it should point you in the right direction in regards to an approach on how to compare the dates.

[dough boy]

Something like

 

SELECT *
FROM table
WHERE DOB BETWEEN NOW() AND NOW() + INTERVAL('10 day')

 

Oh, and make sure you have DOB indexed.

[proctk]

what do you mean make sure dob is indexed

[dough boy]

In your database, make sure that the field DOB is indexed.

 

Anytime you are "searching" your database using the "where" command (or in joins), make sure that all of those fields are indexed.

 

Here is some documentation for it: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

[proctk]

the below code gives this error message

 

SQL Error: SELECT * FROM users WHERE DOB BETWEEN NOW() AND NOW() + INTERVAL('10 day')

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

 

$query_get_member_bdays = ("SELECT * FROM users WHERE DOB BETWEEN NOW() AND NOW() + INTERVAL('10 day')");

[proctk]

after some searching I come up with the below which  give the error I posted. I'm surprised that I have not been able to find script that uses mysql to get birthdays within x days.

 

error message, any ideas, thank you for the help

SQL Error: SELECT DOB, STR_TO_DATE(CONCAT(EXTRACT(DAY FROM DOB),'-',EXTRACT(MONTH FROM DOB),'-',EXTRACT(YEAR FROM CURDATE())),'%y-%m-%d') AS ThisYearsDate FROM users WHERE CURDATE() BETWEEN 'ThisYearsDate'

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

 

$query_get_member_bdays = ("SELECT DOB, STR_TO_DATE(CONCAT(EXTRACT(DAY FROM DOB),'-',EXTRACT(MONTH FROM DOB),'-',EXTRACT(YEAR FROM CURDATE())),'%y-%m-%d') AS ThisYearsDate FROM users WHERE CURDATE() BETWEEN 'ThisYearsDate'"); 

[dough boy]

Sorry (trying to type from memory doesn't always work)

 

SELECT * FROM users WHERE DOB BETWEEN NOW() AND NOW() + INTERVAL 10 day

 

Also if you have query caching turned on, make sure you replace the 'NOW()' with the actual date via PHP, i.e. '2007-04-02'.  The reason for this is that it will cache the query because it is always the same date today.  When you use the NOW() command, it includes time down to seconds so the query will never be cached.

[proctk]

I changed now() to CURDATE() and the date.  the query does not return anything. Note the date is stored in a mysql table column set as format 'date'  yyyy-mm-dd

 

the date of birth is a date in the past

 

say 1970-04-01

 

the query should return the above based on todays date 2007-04-02

 

thanks for the help

[dough boy]

Ah.  Forgot about that.  Then this should definately work:

 

SELECT DOB, (DAYOFYEAR(NOW() +INTERVAL 10 day) - DAYOFYEAR(DOB)) AS diff FROM users HAVING diff BETWEEN 1 AND 10

 

Basically you just need to figure out what day of the year their birthday is on, then figure out what day of the year is 10 days from now, subtract them and you are good to go.

[proctk]

thank you so much, you just ended my days of searching

[dough boy]

That will be $1,000,000!   Glad to help out