Deleting from the Database

[Collegeboox]

Does anyone have a code that will automatically delete stuff out of the database after 30 days of being in there...?

[Drummin]

Well you would make a page that query's the DB tables for records older than 30 days and gets the record id.  Set it up that same way you would query for showing records that are older than 30 days and within the "mysql_fetch_array" loop you delete the record using the id as the identifier.  If you can manually run this page and it does the job, setup a "cron job" with your host to run this page once every day.

[Psycho]

Well you would make a page that query's the DB tables for records older than 30 days and gets the record id.  Set it up that same way you would query for showing records that are older than 30 days and within the "mysql_fetch_array" loop you delete the record using the id as the identifier.  If you can manually run this page and it does the job, setup a "cron job" with your host to run this page once every day.

 

Absolutely not! Never run queries in loops. Why would you run a SELECT query to get all the records that are older than 30 days only to loop through each of the records in the result to perform a DELETE. You can simply run ONE query to delete all the records older than 30 days.

 

Example

DELETE FROM table_name
WHERE date_created < DATE_SUB(curdate(), INTERVAL 30 DAY)

[Collegeboox]

Example

DELETE FROM table_name
WHERE date_created < DATE_SUB(curdate(), INTERVAL 30 DAY)

 

So you are saying to place this code on the homepage that is going to be pulling the information from the database everyday?

[Drummin]

So you are saying to place this code on the homepage that is going to be pulling the information from the database everyday?

IF the home page is looked at every day.

[Adam]

Ideally you don't want to be running irrelevant queries on your home page, or any other page, that isn't actually needed to display it. You should set-up a cron job or a Windows task to periodically run maintenance like this, preferably in the middle of the night or whenever you're on-line user count is at its lowest.

[Collegeboox]

Ideally you don't want to be running irrelevant queries on your home page, or any other page, that isn't actually needed to display it. You should set-up a cron job or a Windows task to periodically run maintenance like this, preferably in the middle of the night or whenever you're on-line user count is at its lowest.

 

How do I run a cron job? and what is the code for a cron job?

[Psycho]

Ideally you don't want to be running irrelevant queries on your home page, or any other page, that isn't actually needed to display it. You should set-up a cron job or a Windows task to periodically run maintenance like this, preferably in the middle of the night or whenever you're on-line user count is at its lowest.

 

How do I run a cron job? and what is the code for a cron job?

 

A cron job is just a scheduled process where you can specify a PHP file to run at a certain period/interval. How you set that up would be dependant on the type of server you are running. If you are using a host look into the tools your host provides.

 

Also, be aware that the code provided below is only "example" code. You would need a delete query for all tables that you need to delete from and those tables must have a field where you are already tracking the date created for the data. Additionally, you need to take foreign key references into consideration. You should not delete records if those records are used as a foreign key reference in another table.

 

Lastly, if you are capturing the date created for records (which you must do in order to implement this), then you probably don't need to delete the records anyway! If you want to display a list of records that were created in the last 30 days, then just format your queries to do that instead of deleting the old records. You need to be very sure that you don't want data before deleting it.