notify by email on expiry date

I'm working on a database that saves records for laborers details such as name, nation, visa expiry date.

So, I want to create a little script that searches and matches expiry dates with today's date and send me a notification by email withi开发者_运维问答n 1 month before the visa expires.

A very rough outline, assuming you already have a database table.

Every day run a script that calculates todayminusonemonth (solved in Stratton's answer) and performs a select * from databasetable where expirydate = todayminusonemonth. Iterate over the result set, compose a message with the target's specific information and send it out using php's mail interface.

It's by far the easiest solution to make sure the script runs every day, if that's not an option a column should be added to the db to indicate that the mail has been sent. Or an extra table listing all days for which mails have been sent could be created...

See? This first decomposition of the problem was quite easy. Now you can start solving each of these partial problems, or look up information/howtos for each. Feel free to create a new question with more specific needs you may have.

One problem you may run into is that your webhost doesn't allow cron scripts to be executed. A very creative alternative solution using a website monitoring service is explained in I don't have cron jobs on my server. What is an alternative for sending emails without user input?

try something like this:

$monthAgo = date("y-m-d",strtotime('-1 month'));
$sql = ("SELECT * FROM tablename WHERE visa_expiry_date < '".$monthAgo ."'");

and you'll get all the visas that are 1 month away from expiration. then you just send an email using mail

do this all in a cron job

You can try SQLyog's SQL Scheduler and Reporting Tool which has a Job Agent that allows you to generate, format and send personalized mails with results returned from a query. This allows you to schedule execution of query(s). The resultset(s) can be emailed to multiple recipients. You can specify an external file or enter query(s) that you want to execute using this tool.

Read this.

You need to look into cron jobs? Which will be executed at the specified time with given interval.