Mass Delete Old Private messages

[amykhar]

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

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

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

?>

[Gutspiller]

What do you mean put in "a" file? a php file? Another question, are you saying I would have to run this file everytime I wanted it to delete the messages or do I just have to run it once? If so is there a way to get it to automatically do this instead of needing to be manually ran?

Thanks for any info you can provide. :tu:

[TWTCommish]

It would need to be executed...every time it would delete ALL PMs (for all users) older than 60 days. If you wanted it run regularly without your intervention, you'd need to ask your webhost about setting up a crontab.

[PhotoGenie]

What version is this for?

[TWTCommish]

It's simple enough that I think it'll work for any version that supports the PM system.

[PhotoGenie]

TY!! TWT :)

[LuBi]

Can we get something like this that does it automatically?

[amykhar]

Lubi, just create a cron job for the script.

Amy

[alexi]

Which numbers would I change if I wanted a figure other than 60 days? Say 6 months? (180 days)

[alexi]

anyone have any idea on which number to change? The last 60?

[Jawelin]

Originally posted by amykhar
$time = time() - (60 * 24 * 60 * 60);

Php time() function is expressed in seconds.
So, 60*60*24 means a day... :)

Any further problem ?
:bunny:

P.S.: the multiplier operator has the commutative property... :p

[grace819]

can anyone confirm if this hack does what its suppose to do? Meaning does the above code guarantee to delete PMs longer than 60 days?

[alexi]

works fine for me

[eoc_Jason]

If you don't think it will work (or just want to get a number) you can comment out the delete line and use these to see the total rows that will be deleted:


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

[[email protected] vbmenu_register("postmenu_308716", true);]

Works pretty good. Thanks

[Bison]

This would be cool is you can add this into the Admin cp ...

[Night Owl]

Does this work on version 2.3.0?

Does it empty out all the folders?

How can I make it delete the PMs older than one month?

Thank you!

[morey]

Amy,

I took your script and made an option in the Admin CP to run it. It seemed to work, but when I ran it all I got was a blank screen. I was wondering if there is a way to add something to the end of the script so that when it is finished running it would return you to say the PM stats query? I am not that versed in php, to do the coding myself .

Thanks

[chrisvonc]

Hello:
I would also like to know if it works with version 2.3.0 & which 60 is the 60 days because I would like to change it to 30 Days!

If you are still interested to change the days, I modifed the first 60 and it seemed to work on my v2.3.2 test forum. So your code for 30 day limit would look like this:

$time = time() - (30 * 24 * 60 * 60);

[djbaxter]

I'd like to adapt this to work with version 3.60.

The existing version shows:

<?php

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

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

?>

In 3.60, I see three variables for private messages:


pm
pmreceipt
pmtext


Can I just duplicate the a$ query above to prune each of those variables? Or is that unnecessary or redundant?

[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.

[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 (http://www.theadminzone.com/forums/showthread.php?p=245284#post245284) 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.

[stinger2]

https://vborg.vbsupport.ru/showthread.php?t=108711&highlight=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

[Jacqueline]

I'm going cross-eyed looking at this. I really think it needs a left join to get delete the pmreceipt and the pm records based on the date in pmtextid.