Countdown in mySQL

[badgerland]

I am new to mySQL and is not sure where to start from.

I have a database of username, password, date of creation, active/inactive (1 and 0), name and etc...

I would like to know how I can add a field that involves with counting down the days.

Say for example the number is 30, which means the account will be valid for 30 days. Each day, the number will decrease by 1 automatically, to reflect the days passed.

If that number reaches 0, the active/inactive status will also change to 0, as in inactive. Otherwise, as long as the number is above 0, the status will be 1, so that the user can log in.

How and where should I start?

Thanks!!

[hericles]

Why don't you make it easier on yourself and simply record the first day of the account activation? Then you just compare to that value in your code to see how much time is remaining if any. It saves you updating the record for every user every day just to decrease the days field.
The first time the user tries to log in and the date is 30 days past update the active/inactive field and inform the user.

[NinjaMediaD]

The only other thing that you could do possibly to make it warn the user that the account is coming near due is to create a cron job that runs a check once a day, if the account is coming near due (like maybe 10 days) it will shoot an email to the user that the account is ten days from expiration.

Cron jobs are usually handled by the server and you have to have access to the server to install and run the scripts for them. It would require a php script that is placed on your server that would check the DB for the days and account information in a loop and any that it finds within x number of days it will shoot out an email to. The cron job is a manager that checks server time and when the server time matches what you have set it to it runs a script. This would be better than running the script whenever someone else logs into the site.

[drjohn]

I was just about to say what hericles said.
Your desired method (changing the entire database every night) is a flawed approach.

Since even with your approach you will still have to look at the value returned and have your code make a decision based on the value, why change it when the simple date comparison suggested is so much less effort?

[datakeyword]

hericles and drjohn had said what I want to say.
You should not store the "countdown number", on the other hand, you should count it when you use it.
e.g. When you want to known "how many people are in active"