query get upcoming birthdays

[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

[php]
$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'");
[/php]

[digital-ether]

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

[php] $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'"); [/php]

There are a lot of php scripts that do this, however they are not standalone since they work on the database listing user data for some other system. For example, this forum should have an extension/module that will show upcoming users birthdays.

If you want to look through the codes for one of these pick a well used open source forum or CMS and search their module repository for an upcoming birthdays module.

For your mysql query there is only one value for the BETWEEN operator when there should be 2 values for comparison.

eg:

[HTML]"between min and max"[/HTML]

Heres the correct syntax: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_between

The above also has an explanation for how to use this with dates.

[UrbanSky]

Yeah I agree with digital, you should change the where clause to WHERE DOB BETWEEN CURDATE() AND CURDATE()+7

I think mysql can do that if not just use DATE_ADD()

I also think that you have over complicated the SELECT part using STR_TO_DATE() all you need to do is return the DOB and the age of the person which you can do with DATEDIFF() and if you need to change the date format use DATE_FORMAT.