Delete old data from statistics table

Some tables in Classipress installation can become huge after some time. Here is a PHP script on how to delete old data from statistics table.

There is a table called wp_cp_ad_pop_daily which stores daily statistics for every ad. This statistic is used for showing daily hits per ad, so deleting old values has no effect on total statistics, because for that purpose there is another table called cp_ad_pop_total.
If you are not using history data of daily statistic for some extensive reports, you don’t need this data. And this table can get really huge. My table had more than 122.000 records and was slowing my site down.

For this purpose I have created a script which will delete old records from this table. There are parameters you can set, so you don’t delete too much records. There is also a security check – a secret phrase you input in the routine, so nobody else can run this script but you.
This script can also check your table and tell you if you should clean your table.

Here are some examples on how to use the script.
Checking a table for size:

http://www.yourURL.com/delete_daily_counters.php?action=count&mysecretkey=somesecretkey

Deleting records from table: (parameters for deleting are stored in script)

http://www.yourURL.com/delete_daily_counters.php?action=delete&mysecretkey=somesecretkey

If you don’t specify parameter action, counting of records will happen.

Download

Download this file and put it to the root folder of your WordPress installation, where configuration file wp-config.php is.

Here is a short manual on how to use this script

When you download the file open it and go to Parameters section. There you will see some parameters.
1. Set parameter my_secret_phrase. Write in a password that you would like to use.
2. Set info_output to true
3. Call the script (type the url of the script to the browser)
4. You will receive a new url that you will use with jour cron job site
5. Set info_output back to false!

Best practice is to run this script daily from a cron job. You can use EasyCron website to run your jobs for free.

It is recommended to create a backup of your database before using this script.

There is also a plugin version of this script.

Compatibility
It is compatible with Classipress v 3.x

Version history:
2014/05/26 – 1.0.0 – Initial version
2014/05/28 – 1.1.0 – Added count option
2014/06/02 – 1.1.2 – Added HTML headers
2015/04/14 – 1.1.4 – Made some small corrections in the script

If you find any issues with this tutorial, please be so kind and write a comment. I’ll try to help as much as I can.