Thursday, August 13, 2015

Querying date with just month and day argument in MySql with PHP

While We were working on a web portal, one of the use case was to send the greeting email to the members on their birthday and anniversaries.

Here we had to get all the email ID's from the database whose birthday is today. And we set the crone job to execute this script(script to send the email) everyday morning at 9 am. So Everyday morning all the members whose birthday or anniversary is today, gets a greetings email. It's not a big task.

But the problem here was the Birth and anniversary dates stored in database are in YYYY-MM-DD format. And when you get the today's date and try to query these dates it's not at all possible to get any result because of YYYY.

The solution for this is very simple. We can query just by month and day.  First we need to get the today's date without year.

//Setting the time zone

//Get today's date without year
 echo  $today;

//add "%-" to the today's date in the place of year
 $today1= "%-".$today;
 echo $today1;

//Now write the SQL query using LIKE statement

//Note members is the table and dob&anniversary are cloumns.

$query_res="SELECT fname,email FROM `members` WHERE `dob` LIKE '$today1' or `anniversary` LIKE '$today1'";

//code to send the email

Thats it. Happy Coding..

No comments: