mysql - Using PHP & cron how can i inform a user that he has got 15 days more left to pay his amount? ← (PHP, MySQL, HTML)

DueDate Table structure

my query here is how to check automatically in php that a user has 15 days prior from the table and inform a user that he has got 15 days more left to pay his amount. 15 days from the dueDate as shown in the table

so if the user due date is 2011-11-17 he has to be informed 15 day prior that is on 2011-11-02

how to achieve this using php to inform automatically ?

Solution:

You need a script run by CRON scheduled daily, probably on 00:01am every day.

You have to create a cron tab file, a plain text file and load it on your server. A cron tab file look like this

1 0 * * * php /path/to/your/script.php

Above file will run script.php on 00:01 am daily.

Reference/tutorial: http://adminschoice.com/crontab-quick-reference

In script.php, you do the 15 days look-up thingy.

Details...

There should be a CRON editor on your server's cpanel. You might not even need to write your own cron tab file, they usually have a GUI editor. If not 1 0 * * * php /path/to/your/script.php is your only content.

1 0 * * * means run the following command every day at 00:01am. php means execute the following PHP file and /path/to/your/script.php specifies which PHP file to run.

Remember that path mush be absolute path starting from the root of your server because CRON is not the same as running on web environment.

On script.php, you do the query (see other answers), get the list of people who are 15 days away from their due date and do the notification.

You can test your script.php by running directly from web browser before putting it into cron too.

Solution:

You can use the date time functions of mysql.

SELECT instalmentsDetailsID 
FROM DueDate 
WHERE DATE_SUB(dueDate, INTERVAL 15 DAY) >= NOW()

Solution:

SELECT * FROM DueDate WHERE DATE_ADD(DueDate , INTERVAL -15 DAY) <= NOW()

That will give you all the items that are currently in their notice period