Mass Delete Old Private messages

Here is a simple script that I just hacked up that will delete all private messages older than 60 days old. Just copy the code to a new file, put in your forum root directory, and run it.

PHP Code:


<?php

require ("global.php");
$time = time() - (60 * 24 * 60 * 60);

$a = $DB_site->query("DELETE FROM privatemessage WHERE dateline < $time");

?>

[Jacqueline]

I cannot see what is wrong. There must be some code syntax we are not aware of as the query works fine manually. I did see that perhaps quotes were missing around the array element, but that doesn't seem to make a difference. Here's what I've got that shows some of what I've tried to make this work.

<?php 

require ("global.php"); 
$time = time() - (25 * 24 * 60 * 60); 

print "1168888543<br />";
print $time;
print "<br />1170502241";

// $a = $DB_site->query("DELETE FROM privatemessage WHERE dateline < $time"); 
// $a = $DB_site->query_first("SELECT COUNT(*) AS oldpms FROM privatemessage WHERE dateline < $time"); 
// print "Total PMs To Be Deleted: $a[oldpms]"; 

// $a = $DB_site->query("DELETE FROM vbpm WHERE pmtextid IN(SELECT pmtextid FROM vbpmtext WHERE dateline < $time)); 
// $a = $DB_site->query("DELETE FROM vbpmtext WHERE pmtextid IN(SELECT pmtextid FROM vbpmtext WHERE dateline < $time)); 

 $a = $db->query_read("SELECT COUNT(*) AS oldpms FROM vbul_pm WHERE pmtextid IN(SELECT pmtextid FROM vbul_pmtext WHERE dateline < $time)"); 
 $b = $a[oldpms] + 1;
 $c = $a;
 print "<br />Total PMs To Be Deleted:<br />" . $a[1];
 print $a['oldpms'];
print $b . '<br />' . $c; 
 if ($a['oldpms'] == 3)
 {print '<br />OK<br />';
 }
 if ($a['oldpms'] <> 3)
 {print '<br />Not OK<br />';
 }
$a = $db->query_read("SELECT COUNT(*) AS oldpms FROM vbul_pmtext WHERE pmtextid IN(SELECT pmtextid FROM vbul_pmtext WHERE dateline < $time)"); 
print "Total PMtexts To Be Deleted:";
print $a[oldpms]; 
?>

[djbaxter]

A hint on another forum got me closer.

Using this:

<?php 

require ("global.php"); 
$time = time() - (25 * 24 * 60 * 60); 

// $a = $DB_site->query("DELETE FROM privatemessage WHERE dateline < $time"); 
// $a = $DB_site->query_first("SELECT COUNT(*) AS oldpms FROM privatemessage WHERE dateline < $time"); 
// print "Total PMs To Be Deleted: $a[oldpms]"; 

// $a = $DB_site->query("DELETE FROM vbpm WHERE pmtextid IN(SELECT pmtextid FROM vbpmtext WHERE dateline < $time)); 
// $a = $DB_site->query("DELETE FROM vbpmtext WHERE pmtextid IN(SELECT pmtextid FROM vbpmtext WHERE dateline < $time)); 

 $a = $db->query_first("SELECT COUNT(*) AS oldpms FROM vbpm WHERE pmtextid IN(SELECT pmtextid FROM vbpmtext WHERE dateline < $time)"); 
 print "<br />Total PMs To Be Deleted: ";
 print $a['oldpms'];
$a = $db->query_first("SELECT COUNT(*) AS oldpms FROM vbpmtext WHERE pmtextid IN(SELECT pmtextid FROM vbpmtext WHERE dateline < $time)"); 
print "<br />Total PMtexts To Be Deleted: ";
print $a['oldpms']; 
?>

I get this:

Total PMs To Be Deleted: 9687
Total PMtexts To Be Deleted: 4771

The questions is why are those two counts different? Shouldn't they be the same?

[Jacqueline]

It is normal that those two counts are different. The database is normalized, so the text of the PM only gets sent once, while the PM will have a count of 2, one for the inbox of receiver and one for the sentbox of the sender. The PMs would be exactly double if no one had deleted any, but with that many you'll have some where either the sender or receiver deleted, but not the other.

So the initial welcome PM on my system has this:
PMID = 1, PMTextID=1,UserID=1,folderid=-1
PMID=2,PMTextID=1,UserID=1,folderid=0

The PM went from me to me, so the UserID is the same. One PM is in my sent items, and one is in my inbox, which must be what the folderid is referring to. They both have identical text, so they point to the same PMTextID.

So that code looks fine. Now you need a query that will select and delete all the rows in vbpmreceipts where PMID = one of the PMIDs you are deleting.

[djbaxter]

Thanks, Jacqueline.

I've been soliciting some help over at TAZ as well and member Sarah there has suggested this:

I would use your script to keep the pmreceipt synchronized also.

SELECT COUNT( * )
FROM vbpmreceipt
LEFT JOIN vbpm ON vbpm.pmid = vbpmreceipt.pmid
LEFT JOIN vbpmtext ON vbpm.pmtextid = vbpmtext.pmtextid
WHERE vbpmtext.dateline < $time

These selects are actually trickier than the deletes because they are selecting COUNT(*). You can do the deletes all with one query.

DELETE *
FROM vbpmtext, vbpm, vbpmreceipt
WHERE (vbpm.pmid = vbpmreceipt.pmid)
AND (vbpm.pmtextid = vbpmtext.pmtextid)
AND vbpmtext.dateline < $time

Test on a backup first!

I'm a little too tired to try this tonight but I will when I am more awake (the "print" version first!).

[Jacqueline]

At first I tried these and they didn't work. Then I realized there were no read receipts on this forum yet, so I sent a message with one. The select count selected one. The delete I changed to a select and it selected the correct record, so it looks like she did a great job with the code for you.

[djbaxter]

Here's the latest:

<?php 

require ("global.php"); 
$time = time() - (25 * 24 * 60 * 60); 

// $a = $DB_site->query("DELETE FROM privatemessage WHERE dateline < $time"); 
// $a = $DB_site->query_first("SELECT COUNT(*) AS oldpms FROM privatemessage WHERE dateline < $time"); 
// print "Total PMs To Be Deleted: $a[oldpms]"; 

// $a = $DB_site->query("DELETE FROM vbpm WHERE pmtextid IN(SELECT pmtextid FROM vbpmtext WHERE dateline < $time)); 
// $a = $DB_site->query("DELETE FROM vbpmtext WHERE pmtextid IN(SELECT pmtextid FROM vbpmtext WHERE dateline < $time)); 

 $a = $db->query_first("SELECT COUNT(*) AS oldpms FROM vbpm WHERE pmtextid IN(SELECT pmtextid FROM vbpmtext WHERE dateline < $time)"); 
 print "<br />Total PMs To Be Deleted: ";
 print $a['oldpms'];
 $a = $db->query_first("SELECT COUNT(*) AS oldpms FROM vbpmtext WHERE pmtextid IN(SELECT pmtextid FROM vbpmtext WHERE dateline < $time)"); 
 print "<br />Total PM Texts To Be Deleted: ";
 print $a['oldpms']; 
$a = $db->query_first ("SELECT COUNT(*) AS oldpms
FROM vbpmreceipt
LEFT JOIN vbpm ON vbpm.pmid = vbpmreceipt.pmid
LEFT JOIN vbpmtext ON vbpm.pmtextid = vbpmtext.pmtextid
WHERE vbpmtext.dateline < $time");
print "<br />Total PM Receipts To Be Deleted: ";
print $a['oldpms']; 
?>

The output from this yields:

Total PMs To Be Deleted: 9700
Total PM Texts To Be Deleted: 4784
Total PM Receipts To Be Deleted: 1

Now my question:

This is the result using "query_first". If I just use "query", no data is printed (just the text). What exactly is the difference?

[djbaxter]

Answering my own question, from https://vborg.vbsupport.ru/showthread.php?t=75207:

Return Values

For SELECT, SHOW, DESCRIBE or EXPLAIN statements, mysql_query() returns a resource on success, and FALSE on error.

For other type of SQL statements, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success and FALSE on error.

- function query_first

Usage within vBulletin: $DB_site->query_first();

This function is a great one for saving time while coding. What is dose is query the database with $DB_site->query():, it then runs the results through $DB_site->fetch_array and returns the array based on the data in the first row retrieved by the query. It also runs $DB_site->free_result(); to save some memory.

Basically it saves you from having to write this all the time:

$query = $DB_site->query($sqltext); 
$array = $DB_site->fetch_array($query); 
$DB_site->free_result($query);

If simply becomes

$array = $DB_site->query_first($sqltext);

[djbaxter]

So:

If I understand this now, the following tells me how many PMs, PM Texts, and PM Receipts need to be deleted:

<?php 

require ("global.php"); 
$time = time() - (25 * 24 * 60 * 60); 

 $a = $db->query_first("SELECT COUNT(*) AS oldpms FROM vbpm WHERE pmtextid IN(SELECT pmtextid FROM vbpmtext WHERE dateline < $time)"); 
 print "<br />Total PMs To Be Deleted: ";
 print $a['oldpms'];
 $a = $db->query_first("SELECT COUNT(*) AS oldpms FROM vbpmtext WHERE pmtextid IN(SELECT pmtextid FROM vbpmtext WHERE dateline < $time)"); 
 print "<br />Total PM Texts To Be Deleted: ";
 print $a['oldpms']; 
$a = $db->query_first ("SELECT COUNT(*) AS oldpms
FROM vbpmreceipt
LEFT JOIN vbpm ON vbpm.pmid = vbpmreceipt.pmid
LEFT JOIN vbpmtext ON vbpm.pmtextid = vbpmtext.pmtextid
WHERE vbpmtext.dateline < $time");
print "<br />Total PM Receipts To Be Deleted: ";
print $a['oldpms']; 
?>

and the following will actually delete those items:

<?php 

require ("global.php"); 
$time = time() - (25 * 24 * 60 * 60); 

 $a = $db->query("DELETE * FROM vbpm WHERE pmtextid IN(SELECT pmtextid FROM vbpmtext WHERE dateline < $time)"); 
 $a = $db->query("DELETE * FROM vbpmtext WHERE pmtextid IN(SELECT pmtextid FROM vbpmtext WHERE dateline < $time)"); 
 $a = $db->query("DELETE * FROM vbpmtext, vbpm, vbpmreceipt WHERE (vbpm.pmid = vbpmreceipt.pmid) AND (vbpm.pmtextid = vbpmtext.pmtextid)AND vbpmtext.dateline < $time"); 

?>

Correct?

And do I need "DELETE *" or just "DELETE"?

[Jacqueline]

I will take a look at this later tonight, but I just spotted that you've got the 25 days I was using instead of the 60 days you wanted in your time variable.

[Jacqueline]

I took a quick look at the delete statements and it doesn't look right. The delete * is right, but I do not see any where you are deleting from vbpmreceipt. The order matters too. Probably vbpmreceipts need to be deleted first, then vbpm, then vbpmtext. Once you delete vbpmtext, you lose your date and cannot use it to delete the others.