I am working on a new project and I need your help.
We have a database, with the tables users & payments. In payments' table we have a field next_payment.

I want XX days before the date in the field to send automatically an email to inform the user.

AND

If the user didn't pay until this date, to change automaticaly the status of the payment to unpayed.

Nik.

Easy enough...what's the problem?

Hopefully you are storing the dates as timestamps.


Just use mktime(); =) and do mktime(0,0,0,0,$timeinDB-4,0);

So its the date -4 days, if thats today, then do something.

well and for that to work fine, you would need to run the script daily. So I would suggest you setup a cron job.

Hopefully you are storing the dates as timestamps.


Just use mktime(); =) and do mktime(0,0,0,0,$timeinDB-4,0);

So its the date -4 days, if thats today, then do something.

Not necessary...MySQL does date comparisons:

With DATETIME fields:

SELECT whatever FROM wherever WHERE DATE(NOW()) = DATE(next_payment - INTERVAL 4 DAYS);

With DATE fields:

SELECT whatever FROM wherever WHERE DATE(NOW()) = next_payment - INTERVAL 4 DAYS; // no need to extract date from field

With int field using a UNIX style timestamp:

SELECT whatever FROM wherever WHERE DATE(NOW()) = DATE(FROM_UNIXTIME(next_payment) - INTERVAL 4 DAYS);

Once you process the emails, you can use the same WHERE clause to UPDATE your payment status flag.

ref: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

I expected it more difficult...
Ok, I will try it, the only step I worry about is the cron job, cause I have never done it before. I read a tut from my hoster and it seems to be easy.
Thank you all for your help!

Which control panel does your host provide?

I use putty, it is ssh.
I will use these to make the cron:
export EDITOR=nano
crontab -e
m h dm m dw command

In the tutorial are some examples..

If you're on a cpanel webhost, you can use the Cron Job option in cpanel to make things easier. All you'll need to do is setup the script to run the query (in a php file somewhere on your server) and direct the cron job to run it.

Video, first google search result: http://www.2serveu.net/cpanel-tutorials/standardcron.html

Personally, I like his way better...what's wrong with learning how to really write a crontab?