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]

Duplicating the above won't work. You can look at the usertools.php file in the admincp to see how vbulletin deletes sent pms or all pms, which may help you get started.

It looks like a similar query will work, but all three are linked together.

pm
pmid, pmtextid, userid, folderid, messageread, importpmid

pmtext
pmtextid, fromuserid, fromusername, title, message, touserarray, iconid, dateline, showsignature, allowsmilie, importpmid

pmreceipt
pmid, userid, touserid, tousername, title, sendtime, readtime, denied

Your query would need get the $pmtextid based on the fromuserid and dateline in pmtext, and get the $pmid based on the pmtextid in pm, then delete these three rows
delete from pmtext where pmtextid = $pmtextid
delete from pm where pmid = $pmid
delete from pmreceipt where pmid = $pmid

I believe that's what is needed. I hope that helps some!

[djbaxter]

Got some help on another forum which got me this far:

<?php 

require ("global.php"); 
$time = time() - (60 * 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_site->query("SELECT COUNT(*) AS oldpms FROM vbpm WHERE pmtextid IN(SELECT pmtextid FROM vbpmtext WHERE dateline < $time)"); 
 print "Total PMs To Be Deleted:";
 print $a[oldpms]; 
 $a = $DB_site->query("SELECT COUNT(*) AS oldpms FROM vbpmtext WHERE pmtextid IN(SELECT pmtextid FROM vbpmtext WHERE dateline < $time)"); 
 print "Total PMtexts To Be Deleted:";
 print $a[oldpms]; 
?>

("vb" is a variable prefix in this database)

However, this doesn't work. I get

Fatal error: Call to a member function on a non-object in /home/psychlin/public_html/forum/delete-pm.php on line 12

which is

$a = $DB_site->query("SELECT COUNT(*) AS oldpms FROM vbpm WHERE pmtextid IN(SELECT pmtextid FROM vbpmtext WHERE dateline < $time)");

[Jacqueline]

I believe the error is caused by the

$DB_site->query

.

Try this:

$a = $db->query_read("SELECT COUNT(*) AS oldpms FROM vbpm WHERE pmtextid IN(SELECT pmtextid FROM vbpmtext WHERE dateline < $time)");

[djbaxter]

Better.

<?php 

require ("global.php"); 
$time = time() - (60 * 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("SELECT COUNT(*) AS oldpms FROM vbpm WHERE pmtextid IN(SELECT pmtextid FROM vbpmtext WHERE dateline < $time)"); 
 print "Total PMs To Be Deleted:";
 print $a; 
 $a = $db->query("SELECT COUNT(*) AS oldpms FROM vbpmtext WHERE pmtextid IN(SELECT pmtextid FROM vbpmtext WHERE dateline < $time)"); 
 print "Total PMtexts To Be Deleted:";
 print $a; 
?>

..yields:

Total PMs To Be Deleted:Resource id #16
Total PMtexts To Be Deleted:Resource id #17

[Jacqueline]

That is odd. I tried running the first query manually setting a time so it was 1 pm that is in my inbox and outbox. The count on the internal query was 1 and the full select was 2. That is correct. I tried running that query in a .php file and $a was blank. I'm not sure what is missing, or why you would be getting Resource id #16.

I wonder if php versions make a difference here?

[djbaxter]

No idea.

This is what's running on my server:

Server Type Linux
Web Server Apache v1.3.37
PHP 4.4.4
MySQL Version 4.1.21-standard

[djbaxter]

How did you run the first query exactly? I get an SQL error...

[Jacqueline]

I put in a value for $time to get it to run with no errors. I got that value by adding a line to print $time.

SELECT pmtextid FROM vbpmtext WHERE dateline < 1170202068

I was able to get the Resource error you are getting by changing this

 print $a[oldpms];

to this

 print $a;

[Jacqueline]

Here's the exact code I'm running right now. I changed the days to 25.

<?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)");
print "<br />Total PMs To Be Deleted:<br />";
print $a[oldpms];
// $a = $DB_site->query("SELECT COUNT(*) AS oldpms FROM vbpmtext WHERE pmtextid IN(SELECT pmtextid FROM vbpmtext WHERE dateline < $time)");
// print "Total PMtexts To Be Deleted:";
// print $a[oldpms];
?>

[djbaxter]

if I use

print $a[oldpms];

it prints a blank.

If I use

print $a;

I get resource #16

Addendum: OK - I just saw your post above. So we get the same thing.