Just wondering

[Andy1]

Ok, so I want to find a way to automatically delete users from my Mysql database after a specified amount of time. Members have the options of accessing the site for;

-1 day

-1 month

-1 year

at a time

 

i stumbled across this method while perusing the internet;

 

I'm making an assumption that you're using some flavor of UNIX, you have an account on the system, and the mysql table has some sort of date/timestamp field.

1) create a file called sqlexec containing the following:
mysql -u your_username -pyour_password

2) create a file called delete.sql containing the following:
use your_dbname;
delete from table_name where to_days(now())-to_days(date_field) > 14;

3)run it as a crontab:
# crontab -e
(insert into crontab file)
2 50 * * * /path/to/file/sqlexec<delete.sql

This will execute the script once a day at 2:50 AM

If the above assumptions weren't correct, then there are other ways

 

problem is i don't run Unix or know what a "cron" is. So does anyone know a way that i can have Mysql automatically delete user information after the specified amount of time, depending on what length of time they're allowed to access the site? As in, a single script or such that can delete a day user, month user or year user once their time is up, with little to no fuss. Or might i have to create seperate tables depending on the access time and apply something there?

 

Essentially what I want is a membership site that people can access for only as long as they've paid for. I THINK I've figured out the payment side of things (although suggestions are more than welcome) i just need help with removing users from the system more easily than by hand. Any help would be greatly appreciated.

[gizmola]

The basic idea is the same regardless of operating system.  They all have schedulers that allow you to run batch jobs.  MySQL does not have a built in scheduler to do this for you.  What operating system are you running the webserver/mysql on?

 

With that said, deleting people because they have an expired subscription is a bad design.  Your subscription table should have fromDate and  toDate columns, and your code should simply check that the person has an active subscription.  If not, then just don't deliver the service/login or whatever you're securing.  Ostensibly, you'll try and get former subscribers to renew, which is pretty hard to do if you went and deleted them from the database, to say nothing of the loss of any associated history/ accounting data, etc.