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");

?>

[stinger2]

https://vborg.vbsupport.ru/showthrea...mass+delete+pm

this is an answer i got from vb.com

hope it helps

[Jacqueline]

Thanks, stinger, but it looks like they are deleting everything in the tables with those.

[djbaxter]

Jacqueline, looking at it again, wouldn't this statement prune all three variables in one query? Could I just delete the first two and leave this?

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

[Jacqueline]

I think it will do it, but don't know for certain. Best bet is to backup your current forum and restore it to your testdb forum. Don't forget to pw protect the testvb directory. You can try the query out there, and if it doesn't work right, restore, then try again.

If that's not available, paste the exact code you want to try for the full .php file and I'll try it on the forum I'm building and see what happens. I only have a couple of PMs I've sent to myself, so its not as good a test as working with your own db, but definately better than nothing.

[djbaxter]

It would be this:

<?php 

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

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

?>

Adjust the time to whatever you wish.

[Jacqueline]

It didn't work. Here's my adjusted code and the results I got from it:

<?php 

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

$a = $db->query("DELETE * FROM vbul_pmtext, vbul_pm, vbul_pmreceipt WHERE (vbul_pm.pmid = vbul_pmreceipt.pmid) AND (vbul_pm.pmtextid = vbul_pmtext.pmtextid)AND vbul_pmtext.dateline < $time"); 

?>

Database error in vBulletin 3.6.4:

Invalid SQL:
DELETE * FROM vbul_pmtext, vbul_pm, vbul_pmreceipt WHERE (vbul_pm.pmid = vbul_pmreceipt.pmid) AND (vbul_pm.pmtextid = vbul_pmtext.pmtextid)AND vbul_pmtext.dateline < 1170411524;

MySQL Error  : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '* FROM vbul_pmtext, vbul_pm, vbul_pmreceipt WHERE (vbul_pm.pmid = vbul_pmreceipt' at line 1 Error Number : 1064
Date         : Tuesday, February 27th 2007 @ 04:18:44 AM

There probably needs to be some sort of left join in there to get it working.

[djbaxter]

Try this one:

<?php 

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

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

?>

[Jacqueline]

Well, it's a step closer. The code did not produce any errors, but it also did not delete anything.

[djbaxter]

Any chance it's the time value you used? Or the possibility that you have no PM receipts?

I really need to create a test forum, I think...

But I'm also wondering about just omitting the PM receipts in the main query and adding a line to delete ALL of the receipts, which are far from critical.

That would give us:

<?php 

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

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

?>

and then we just add a DELETE * or TRUNCATE query for vbpmreceipt to follow.

[Jacqueline]

I verified the time value is good tonight. I do have a PM receipt. It is always a good idea to have a test forum, so I would do that anyway.

I took the query from the file and ran it in MySQL Query Browser and it did a partial. It actually got rid of the pm receipt, but it did not delete the rest. I'm looking at MySQL syntax right now. Here's the page if you want to take a look.
http://dev.mysql.com/doc/refman/5.0/en/delete.html