« Integrating Google Analytics and Wufoo
Simple Environment Handling with Google App Engine »

Deleting inactive WordPressMU blogs

7 Comments Published by December 31st, 2008 in technology, wordpress

Update: we’ve had some problems where some incorrect tables have been deleted by this script. We haven’t had time to investigate, but for now I don’t recommend using this script as is.

Elad posted a small script to delete inactive WordressMU blogs. This can be useful if you run a blog network and want to automate the removal of stale content and regain some database space. I recommend backing up your DB regularly if you plan on running this script in production as it deletes data from the DB.

AJ and I made some modification and improvements to Elad’s version. Namely:

  • fixed bugs in the original (swapped blogs_mail and blogs_delete values)
  • added option to exclude certain blogs from the process (so that you don’t accidentally delete your primary blog for example)
  • changed mail to use SMTP in case you don’t run a mail server on your WordPressMU box
  • changed delete procedure to loop though all user tables (no longer hard-coded to only certain tables)

To use this code place the .php file somewhere inside your wordpressMU install and setup a cron job to run it weekly (for example, something like “php -q /path/wpmu-cleanup.php”). Note that this code does not delete the user from the wp_users table, nor does it delete their folder in /wp-content/blogs.dir.

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
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211

< ?php
/**********************************************************
WPMU Cleanup
Original Version by Elad Salomons: http://www.ringofblogs.com/2008/06/10/wpmu-cleanup-script-to-delete-unused-blogs/
Modified by Boris Masis: http://www.borism.net/2008/12/31/deleting-inactive-wordpressmu-blogs
 
This script checks the WPMU database for blogs that have
not been updated for X days, then sends a notice to the
blogowner that the blog will be deleted after Y days.
 
It also automatically deletes blogs that have not been
updated for Y days, then sends an email to the blogowner.
 
On both occasions, the script will send a report with
details on warned and deleted blogs to the administrator.
 
This script has *NOT* been programmed as a plugin since
it is intended to be executed by a cronjob. As it is now
it should be run once every 7 days, but you can configure
it to do otherwise.
 
This script is *NOT* a final version and should be used
with caution and only if you know what you are doing. I
recommend testing it on a testsite rather than a livesite.
 
**********************************************************/
 
/* SETTINGS */
require_once "Mail.php";
require_once('wp-load.php');
$mysql_server				= '';
$mysql_username				= '';
$mysql_password				= '';
$mysql_database				= 'wordpress';
 
$options['email_host']		= '';
$options['email_from']		= ''; // Email-address used for sending notices.
$options['email_subject']	= 'Your Website';
$options['mail_report']		= ''; // Reports will be sent to this address.
$options['timetomail']		= 256; // Number of days blog can be inactive before receiving deletion warning.
$options['timetodeletion']	= 270; // Number of days blog can be inactive before deletion.
$options['dateformat']		= 'm-d-Y'; // Dateformat used in emails.
 
$options['noticemessage']	= 'Dear user,' . "\n\n" . 'You have not updated your website in %timetomail% days. This message is to remind you that your website will be deleted if it is not updated in %timetodeletion% days.' . "\n\n" . 'If you would like to keep your website, please update it before %date%.' . "\n\n" . 'Best regards.' . "\n\n";
$options['deletemessage']	= 'Dear user,' . "\n\n" . 'Since your website has not been updated for %timetodeletion% days, it has been deleted.' . "\n\n" . 'Please contact us if you have any questions.' . "\n\n" . 'Best regards.' . "\n\n";
 
$options['blogstoignore']	= array(); //blogs that should not be part of this logic for example =array(1,2);
 
/********************/
/*** STOP EDITING ***/
/********************/
 
$options['unix_timetomail']		= $options['timetomail'] * 24 * 60 * 60;
$options['unix_timetodeletion']	= $options['timetodeletion'] * 24 * 60 * 60;
$options['date']				= date($options['dateformat'], (($options['timetodeletion'] - $options['timetomail']) * 24 * 60 * 60) + time());
 
function mailer ($to, $from, $subject, $body){
	global $options;
	$headers = array ('From' => $from,
	  'To' => $to,
	  'Subject' => $subject);
	$smtp = Mail::factory('smtp',
		array ('host' => $options['email_host'],
		'auth' => false));
	$mail = $smtp->send($to, $headers, $body);
}
 
// Connect to database
$db =	mysql_connect($mysql_server,
					   $mysql_username,
					   $mysql_password);
 
		mysql_select_db($mysql_database, $db);
 
// Get blogs
$sql = 'SELECT blog_id,last_updated FROM wp_blogs';
$sql = mysql_query($sql);
 
		while( $row = mysql_fetch_assoc($sql) ) :
 
			if (!in_array($row['blog_id'], $options['blogstoignore']))
				$blogs_raw[$row['blog_id']] = strtotime($row['last_updated']);
 
		endwhile;
 
		// Get blogs for deletion notice
		foreach( $blogs_raw as $key => $value ) :
 
			$time = time() - $value;
 
			if( $time >= $options['unix_timetomail'] && $time < $options['unix_timetodeletion'] ) :
 
				$blogs_mail[] = array('blogid'  => $key,
									  'email'   => '',
									  'siteurl' => '');
 
			endif;
 
		endforeach;
 
				// Get userdata for warning notice
				for($i = 0; $i < sizeof($blogs_mail); $i++) :
 
					$str = 'admin_email';
					$sql = mysql_query("SELECT option_value FROM wp_" . $blogs_mail[$i]['blogid'] . "_options WHERE option_name = '$str'");
 
						while ( $row = mysql_fetch_assoc($sql) ) :
 
							$blogs_mail[$i]['email'] = $row['option_value'];
 
						endwhile;
 
					$str = 'siteurl';
					$sql = mysql_query("SELECT option_value FROM wp_" . $blogs_mail[$i]['blogid'] . "_options WHERE option_name = '$str'");
 
						while ( $row = mysql_fetch_assoc($sql) ) :
 
							$blogs_mail[$i]['siteurl'] = $row['option_value'];
 
						endwhile;
 
				endfor;
 
						$cronmsg = 'The following blogs have been sent deletion warnings:' . "\n\n";
						// Create and send warning notice to users
						for($i = 0; $i < sizeof($blogs_mail); $i++) :
 
							$message = str_replace('%timetomail%', $options['timetomail'], $options['noticemessage']);
							$message = str_replace('%timetodeletion%', $options['timetodeletion'], $message);
							$message = str_replace('%date%', $options['date'], $message);
							//$message = str_replace('%loginurl%', $blogs_mail[$i]['siteurl'] . 'wp-login.php', $message);
 
							mailer($blogs_mail[$i]['email'], $options['email_from'], $options['email_subject'], $message);
 
							$cronmsg .= $blogs_mail[$i]['siteurl'] . "\n";
 
						endfor;
						if (sizeof($blogs_mail) > 0) mailer($options['mail_report'], $options['mail_report'], 'Blogs Sent Warnings', $cronmsg);
 
 
 
		// Get blogs for deletion
		foreach( $blogs_raw as $key => $value ) :
 
			$time = time() - $value;
 
			if( $time >= $options['unix_timetodeletion'] ) :
 
				$blogs_delete[] = array('blogid'  => $key,
										'email'   => '',
										'siteurl' => '');
 
			endif;
 
		endforeach;
 
				// Get userdata for deletion notice
				for($i = 0; $i < sizeof($blogs_delete); $i++) :
 
					$str = 'admin_email';
					$sql = mysql_query("SELECT option_value FROM wp_" . $blogs_delete[$i]['blogid'] . "_options WHERE option_name = '$str'");
 
						while ( $row = mysql_fetch_assoc($sql) ) :
 
							$blogs_delete[$i]['email'] = $row['option_value'];
 
						endwhile;
 
					$str = 'siteurl';
					$sql = mysql_query("SELECT option_value FROM wp_" . $blogs_delete[$i]['blogid'] . "_options WHERE option_name = '$str'");
 
						while ( $row = mysql_fetch_assoc($sql) ) :
 
							$blogs_delete[$i]['siteurl'] = $row['option_value'];
 
						endwhile;
 
				endfor;
 
						// Delete tables
						$cronmsg = 'The following blogs have been deleted:' . "\n\n";
						for($i = 0; $i < sizeof($blogs_delete); $i++) :
 
							//loop though and delete all tables that begin with the blog id
							$prefix = 'wp_' . $blogs_delete[$i]['blogid'] . '_%';
 
							$querystr ="SHOW TABLES LIKE '$prefix'";
							$tables = $wpdb->get_results($querystr, ARRAY_N);
 
							$query = "";
							if ($tables){
								foreach ($tables as $table){
									$query = 'DROP TABLE IF EXISTS '. $table[0];
									$wpdb->query($query);
								}
							}
 
							$sql_blogid	= $blogs_delete[$i]['blogid'];
							mysql_query("DELETE FROM wp_blogs WHERE blog_id = '$sql_blogid'") or die('Error: ' . mysql_error());
 
							// Send mail
							$message = str_replace('%timetodeletion%', $options['timetodeletion'], $options['deletemessage']);
 
							mailer($blogs_delete[$i]['email'], $options['email_from'], $options['email_subject'], $message);
 
							$cronmsg .= $blogs_delete[$i]['siteurl'] . "\n";
 
						endfor;
						if (sizeof($blogs_delete) > 0) mailer($options['mail_report'], $options['mail_report'], 'Deleted Blogs', $cronmsg);
 
?>

Search