How to Setup a PHP Script in Crontab to Clean Multiple WordPress (Database) on Same Server?


If you host many wordpress blogs on the same server, then you can do this easily by the following PHP script. I have hosted 6 websites on the one VPS, so instead of launching cleaning script one by one, I can just put that in the crontab so it is easier for me.

First, you would need something like this (handy functions).

1
2
3
4
5
6
7
8
  set_time_limit(600);
  mysql_connect("localhost", "user", "password") or die(mysql_error());
                              
  function run_query($query) {
    echo $query;
    mysql_query($query) or die(mysql_error());
    echo " ***OK!*** \n";
  }                                     
  set_time_limit(600);
  mysql_connect("localhost", "user", "password") or die(mysql_error());
                              
  function run_query($query) {
    echo $query;
    mysql_query($query) or die(mysql_error());
    echo " ***OK!*** \n";
  }                                     

The set_time_limit(600) is just a precaution in case it hangs (so that you can safely put that in crontab). Be sure to replace the mysql access details. The function run_query() will print the query and execute it, if anything goes wrong, the overall script will terminate.

Now, this is the best part, you can put some well-known-and-used SQL queries together so that you can execute all at once.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
  function clean_wp($db_name, $table_prefix, $skipwp = false) {
    mysql_select_db($db_name);
 
    echo "Optimising $db_name... \n";
    
    if (!$skipwp) {
      $query = "
        DELETE `a`, `b`, `c` FROM `{$table_prefix}terms` AS `a` 
        LEFT JOIN `{$table_prefix}term_taxonomy` AS `c` ON `a`.`term_id` = `c`.`term_id` 
        LEFT JOIN `{$table_prefix}term_relationships` AS `b` ON `b`.`term_taxonomy_id` = `c`.`term_taxonomy_id` 
        WHERE `c`.`taxonomy` = 'post_tag' AND `c`.`count` = 0";
  
      run_query($query);
      
      $query = "
        DELETE `a`, `b`, `c`
        FROM `{$table_prefix}posts` as `a`
        LEFT JOIN `{$table_prefix}term_relationships` as `b` ON ( `a` . `ID` = `b` . `object_id` )
        LEFT JOIN `{$table_prefix}postmeta` as `c` ON ( `a` . `ID` = `c` . `post_id` )
        WHERE `a` . `post_type` = 'revision' 
      ";
  
      run_query($query);
        
      $query = "
        DELETE FROM `{$table_prefix}commentmeta`
        WHERE `comment_id` NOT IN (
          SELECT `comment_id`
          FROM `{$table_prefix}comments` 
        )
      ";
        
      run_query($query);
  
      $query = "
        DELETE FROM `{$table_prefix}commentmeta` 
        WHERE `meta_key` LIKE \"%akismet%\"
      ";
        
      run_query($query);  
  
      $query = "
        DELETE FROM `{$table_prefix}comments` 
        WHERE `comment_approved` <> 1
      ";
      
      run_query($query);
      
      $query = "
        DELETE FROM `{$table_prefix}posts` 
        WHERE `post_type` = \"revision\"
      ";
  
      run_query($query);
    }
 
    $alletabellen = mysql_query("SHOW TABLES");
 
    while($tabel = mysql_fetch_assoc($alletabellen))
    {
        foreach ($tabel as $db => $tabelnaam) 
        {
            $query = "REPAIR TABLE `$tabelnaam`";
            run_query($query);
            
            $query = "OPTIMIZE TABLE `$tabelnaam`";
            run_query($query);
        }
    }
    
    echo " --- Done!!! ---";
  }
  function clean_wp($db_name, $table_prefix, $skipwp = false) {
    mysql_select_db($db_name);

    echo "Optimising $db_name... \n";
    
    if (!$skipwp) {
      $query = "
        DELETE `a`, `b`, `c` FROM `{$table_prefix}terms` AS `a` 
        LEFT JOIN `{$table_prefix}term_taxonomy` AS `c` ON `a`.`term_id` = `c`.`term_id` 
        LEFT JOIN `{$table_prefix}term_relationships` AS `b` ON `b`.`term_taxonomy_id` = `c`.`term_taxonomy_id` 
        WHERE `c`.`taxonomy` = 'post_tag' AND `c`.`count` = 0";
  
      run_query($query);
      
      $query = "
        DELETE `a`, `b`, `c`
        FROM `{$table_prefix}posts` as `a`
        LEFT JOIN `{$table_prefix}term_relationships` as `b` ON ( `a` . `ID` = `b` . `object_id` )
        LEFT JOIN `{$table_prefix}postmeta` as `c` ON ( `a` . `ID` = `c` . `post_id` )
        WHERE `a` . `post_type` = 'revision' 
      ";
  
      run_query($query);
        
      $query = "
        DELETE FROM `{$table_prefix}commentmeta`
        WHERE `comment_id` NOT IN (
          SELECT `comment_id`
          FROM `{$table_prefix}comments` 
        )
      ";
        
      run_query($query);
  
      $query = "
        DELETE FROM `{$table_prefix}commentmeta` 
        WHERE `meta_key` LIKE \"%akismet%\"
      ";
        
      run_query($query);  
  
      $query = "
        DELETE FROM `{$table_prefix}comments` 
        WHERE `comment_approved` <> 1
      ";
      
      run_query($query);
      
      $query = "
        DELETE FROM `{$table_prefix}posts` 
        WHERE `post_type` = \"revision\"
      ";
  
      run_query($query);
    }

    $alletabellen = mysql_query("SHOW TABLES");

    while($tabel = mysql_fetch_assoc($alletabellen))
    {
        foreach ($tabel as $db => $tabelnaam) 
        {
            $query = "REPAIR TABLE `$tabelnaam`";
            run_query($query);
            
            $query = "OPTIMIZE TABLE `$tabelnaam`";
            run_query($query);
        }
    }
    
    echo " --- Done!!! ---";
  }

The function clean_wp takes 3 parameters. The first one is the table name (so you can pass in different tables for different wordpress blogs). The second parameter specifies the wp table prefix and the default should be wp_. The third parameter specifies whether there is a wordpress blog in this table, so you can ignore wordpress-related cleaning queries and only execute the general ones.

The SQL queries are to clean the trash comments, unapproved comments (be careful with that), the revisions. The general queries include the optimising and repairing. So you should be able to put that easily in the crontab.

–EOF (The Ultimate Computing & Technology Blog) —

GD Star Rating
loading...
581 words
Last Post: Linux Easter Egg - apt-get moo
Next Post: How to Change the Crontab Editor?

The Permanent URL is: How to Setup a PHP Script in Crontab to Clean Multiple WordPress (Database) on Same Server?

Leave a Reply