I’m not sure if there are any people interest in PHPList database maintenance.
I believe my ability to send 24,000+ html emails per hour is partially attributed to knowing the importance of keeping my DB as sleek and fast as possible.
PHPList doesn’t address DB maintenance properly at all.
I do… a whole lot.
I’ve written a boatload of PHP / MySql scripts that I’m willing to share… I just don’t know if there’s any interest or where to post them.
A few of my most used include:
Orphan Record Clean Up: Deletes rows from 7 tables where there is no user_user associated. (Delete an addy from PHPList and it leaves these orphans - at it’s worst when you delete all that are not subscribed to any lists)
Orphan Record Create: Adds records to tables where the user_user is blacklisted but not in the required additional 2 tables. (PHPList causes this when you manually blacklist an addy)
Quick Config: Changes all config values with the click of a button. (Useful if using one install for multiple domains)
Black on Black: This one is special. It moves all blacklisted user_user addy’s to a special list. Doing so keeps my server iterating through my “send to” lists moving as fast as possible because it doesn’t have to spend time evaluating user_user.blacklisted.
Optimize All Tables: Take a wild guess what this does.
Repair Tables: I had a table go sour once, just once… it works very well.
I have no time for github even though it would make the world a simpler place for a lot of people.
LOL I don’t even have time for being here right now
Is there no proper place within these forums for me to just post the code for each?
This is one of the most useful for sites with many email addys.
Edit lines 5-8 with your info.
Upload the file to your server.
Use a browser to visit the URL of the script and it will execute.
<?php
// REMOVES ROWS FROM TABLES WHERE THERE IS NO pl_user_user ASSOCIATED
$servername = "mydomain.com"; //ENTER YOUR DOMAIN NAME
$username = "mydbusername"; //ENTER YOUR DATABASE USERNAME
$password = "mydbpassword"; //ENTER YOUR DATABASE PASSWORD
$dbname = "mydbname"; //ENTER YOUR DATABASE NAME
$conn = mysqli_connect($servername, $username, $password, $dbname);
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
} else {
$sql = "SELECT COUNT(email) FROM pl_user_blacklist WHERE email NOT IN (SELECT email FROM pl_user_user);";
$result = $conn->query($sql);
$row = $result->fetch_row();
$cnt = $row[0];
$sql = "DELETE FROM pl_user_blacklist WHERE email NOT IN (SELECT email FROM pl_user_user);";
mysqli_query($conn, $sql);
echo "$cnt Orphans Removed From pl_user_blacklist<br />";
$sql = "SELECT COUNT(email) FROM pl_user_blacklist_data WHERE email NOT IN (SELECT email FROM pl_user_user);";
$result = $conn->query($sql);
$row = $result->fetch_row();
$cnt = $row[0];
$sql = "DELETE FROM pl_user_blacklist_data WHERE email NOT IN (SELECT email FROM pl_user_user);";
mysqli_query($conn, $sql);
echo "$cnt Orphans Removed From pl_user_blacklist_data<br />";
$sql = "SELECT COUNT(pl_user_message_bounce.user) FROM pl_user_message_bounce WHERE pl_user_message_bounce.user NOT IN (SELECT id FROM pl_user_user);";
$result = $conn->query($sql);
$row = $result->fetch_row();
$cnt = $row[0];
$sql = "DELETE FROM pl_user_message_bounce WHERE pl_user_message_bounce.user NOT IN (SELECT id FROM pl_user_user);";
mysqli_query($conn, $sql);
echo "$cnt Orphans Removed From pl_user_message_bounce<br />";
$sql = "SELECT COUNT(userid) FROM pl_user_user_attribute WHERE userid NOT IN (SELECT id FROM pl_user_user);";
$result = $conn->query($sql);
$row = $result->fetch_row();
$cnt = $row[0];
$sql = "DELETE FROM pl_user_user_attribute WHERE userid NOT IN (SELECT id FROM pl_user_user);";
mysqli_query($conn, $sql);
echo "$cnt Orphans Removed From pl_user_user_attribute<br />";
$sql = "SELECT COUNT(userid) FROM pl_user_user_history WHERE userid NOT IN (SELECT id FROM pl_user_user);";
$result = $conn->query($sql);
$row = $result->fetch_row();
$cnt = $row[0];
$sql = "DELETE FROM pl_user_user_history WHERE userid NOT IN (SELECT id FROM pl_user_user);";
mysqli_query($conn, $sql);
echo "$cnt Orphans Removed From pl_user_user_history<br />";
$sql = "SELECT COUNT(userid) FROM pl_usermessage WHERE userid NOT IN (SELECT id FROM pl_user_user);";
$result = $conn->query($sql);
$row = $result->fetch_row();
$cnt = $row[0];
$sql = "DELETE FROM pl_usermessage WHERE userid NOT IN (SELECT id FROM pl_user_user);";
mysqli_query($conn, $sql);
echo "$cnt Orphans Removed From pl_usermessage<br />";
$sql = "SELECT COUNT(userid) FROM pl_listuser WHERE userid NOT IN (SELECT id FROM pl_user_user);";
$result = $conn->query($sql);
$row = $result->fetch_row();
$cnt = $row[0];
$sql = "DELETE FROM pl_listuser WHERE userid NOT IN (SELECT id FROM pl_user_user);";
mysqli_query($conn, $sql);
echo "$cnt Orphans Removed From pl_listuser<br />";
mysqli_close($conn);
};
?>
A couple of comments:
The code uses a non-default prefix for the phplist tables, “pl” instead of “phplist” so that is something else that people would have to change. Also, the “user” prefix is optional so that too might have to be removed before trying to use the script.
It is actually valid for the user_blacklist table to contain rows with email addresses that do not exist in the user table. The recipient of a forwarded campaign, who is not himself a subscriber, can click a link to stop receiving emails from phplist, in effect a non-subscriber blacklisting. To remove such rows is probably “the wrong thing to do”.
You’re correct however…
I have several lists with well over 100,000 confirmed addys and this has never been a problem. I believe the quantity of occurrences is far and few between.
Maybe I’ll modify PHPList to handle these situations by adding them to a special list, marking them as blacklisted, and inserting the missing records in the 2 other required tables.
Thanks for your kind offer (if it still stands after all this time)
Yes I am very interested in your scripts, especially Black on Black,
but I’m sure the others would also be useful toward learning if not actually using
I have a few situations that the PHPlist menu options don’t cover, and as I’m an uneducated hacker, working from examples would be very useful in solving these I’m sure.
Are these scripts still available, how do I get hold of them ?
