I’m running my WordPress database and I want to send out a Christmas card to all my users.
The trouble is some of them are bogus. In fact nearly 3,000.
I’d like to not send out these messages so they just bounce back and fill up my inbox and overload the mailserver or whatever.
So I wrote this script.
// Create connection
$link = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$link) {
die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully <br>";
//If form submitted
if ($_GET["form"] == "true") {
echo '<H1>FORM SUBMITTED</H1>';
// select all users
$query = "SELECT wp_users.ID FROM wp_users,wp_comments GROUP BY wp_users.ID ORDER BY wp_users.ID asc";
$result = $link->query($query);
while($row = mysqli_fetch_array($result)) {
$all_names[]=$row["ID"];
}
$query = "SELECT wp_users.ID FROM wp_users,wp_comments WHERE INSTR(wp_users.display_name, wp_comments.comment_author) > 0 GROUP BY wp_users.ID ORDER BY wp_users.ID asc";
//execute the query.
$result = $link->query($query);
//display information:
while($row = mysqli_fetch_array($result)) {
$names[]=$row["ID"];
}
$dupe_ID = array_diff($all_names,$names);
$i=0;
// LOOP THE COMMAND
foreach ($dupe_ID as $value) {
// DELETE THE USERS
$query = "DELETE FROM wp_users WHERE ID = $value";
if (mysqli_query($link, $query)) {
$i++;
} else {
echo "Error deleting records: " . mysqli_error($link);
}
}
echo $i . 'Names deleted';
}
else
{
// select all users
$query = "SELECT wp_users.display_name FROM wp_users,wp_comments GROUP BY wp_users.ID ORDER BY wp_users.display_name asc";
$result = $link->query($query);
while($row = mysqli_fetch_array($result)) {
$all_names[]=$row["display_name"];
}
$query = "SELECT wp_users.display_name FROM wp_users,wp_comments WHERE INSTR (wp_users.display_name, wp_comments.comment_author) > 0 GROUP BY wp_users.ID ORDER BY wp_users.display_name asc";
//execute the query.
$result = $link->query($query);
//display information:
while($row = mysqli_fetch_array($result)) {
$names[]=$row["display_name"];
}
$dupe_names = array_diff($all_names,$names);
$i=1;
// print out the bogus names
foreach ($dupe_names as $value) {
echo $i.')'. $value .'<br>';
$i++;
}
echo '<p><h3>To delete these names click here </h3></p>';
echo '<form method="GET" target="bogus_users.php" ><input type=submit value="Submit"> <input type="hidden" name="form" value="true"></form>';
}
It’s in two parts:-
1) The page accurately tells me the duds in the database and outputs the names so I can scan through them.
2) Submit the form. The $_GET command returns a token telling the script to run an identical script which will delete those records. This returns the correct token, equivalent to the number of bogus records, but no records are deleted.
I ran it a few times until my ISP sent me an email saying I was overloading the server.
Is there an obvious mistake here, because I can see it.
First off, deleting in a loop is the anthesis of using a database. You should do a batch delete.
One way of doing this is to aggregate the IDs into a string $values in the form: id1, id2, id3, … and then do something like
Another approach, is to add a column to your wp_users table to flag the inActive users. Then you would do a soft delete, which will be a lot easier on the database and the server.
You would run an update statement setting the inActive flag instead of a DELETE statement and then you would just email the active users.
Finally, since your code is already doing all the work to figure out the real users, you can simply use that same code to send out the Christmas card. So instead of deleting the users, just mail the real ones, you have the list in your code.