How do I get rid of 5000 (update: now 10,000) PMs?

Looking for a MOD? Have a MOD request? Post here for help. (Note: This forum is community supported; phpBB does not have official MOD authors)
Ideas Centre
User avatar
beggers
Registered User
Posts: 1257
Joined: Fri Nov 23, 2001 8:19 pm
Location: Las Vegas
Contact:

How do I get rid of 5000 (update: now 10,000) PMs?

Post by beggers » Sun May 04, 2008 6:15 am

Over the years my phpBB board has accumulated over 5000 PMs. To my knowledge, phpBB has never had any PM maintenance tools, so it's gotten out of hand now. How can I prune PMs older than 90 days, for example?
Last edited by beggers on Sat Aug 08, 2009 2:02 am, edited 2 times in total.

Jotne
Registered User
Posts: 364
Joined: Wed Jul 16, 2003 10:18 pm
Location: Norway

Re: How do I get rid of 5000 PMs?

Post by Jotne » Sun May 04, 2008 7:19 am

I think you need a mod.

You can post a message to all users and tell them to delete PMs.
This may help some.

I am not sure what happens if you set number allowed of PM down from 100 to 10.
This may only restrict user to get more then 10 and not delete the rest 90.
Last edited by Jotne on Sun May 04, 2008 7:24 am, edited 1 time in total.
Don’t feed the Troll
Molde Cycleklubb - - http://www.moldeck.no
HP41.org - - http://forum.hp41.org

User avatar
karlsemple
Former Team Member
Posts: 39802
Joined: Mon Nov 01, 2004 8:54 am
Location: Hereford, UK
Contact:

Re: How do I get rid of 5000 PMs?

Post by karlsemple » Sun May 04, 2008 7:22 am

IS removing users pm's a good idea? What if the users in question have kept certain pm messages for a reason?
Image

User avatar
beggers
Registered User
Posts: 1257
Joined: Fri Nov 23, 2001 8:19 pm
Location: Las Vegas
Contact:

Re: How do I get rid of 5000 PMs?

Post by beggers » Sun May 04, 2008 9:07 am

Probably 60-70% of these members are long gone. I'm talking about PMs that have been stuck in the system for several years. I need a way to prune PMs.

User avatar
karlsemple
Former Team Member
Posts: 39802
Joined: Mon Nov 01, 2004 8:54 am
Location: Hereford, UK
Contact:

Re: How do I get rid of 5000 PMs?

Post by karlsemple » Sun May 04, 2008 9:21 am

beggers wrote:Probably 60-70% of these members are long gone. I'm talking about PMs that have been stuck in the system for several years. I need a way to prune PMs.

There is no method for doing this in phpBB3 and would require a modification of some kind so you may want to post in the mod request forum, in fact I will move this into that forum if you wish :)
Image

kropes
Registered User
Posts: 33
Joined: Mon Apr 21, 2008 7:40 am
Location: Honolulu, Hawaii

Re: How do I get rid of 5000 PMs?

Post by kropes » Sun May 04, 2008 10:57 am

Assuming that you have an understanding of SQL commands.... a quick and dirty method would be to go in and eradicate them from the database. Of course once the SQL commands are designed, it would help us all out...(with a mod) hehehehehe. So for anyone that want to tackle it (sorry, I don't have time and don't do Php that well. I am an asp/vbscript guy)

It looks like all date/time fields are stored in the database as seconds, with January 1, 1970 being the start time.

So for instance (if your using MS-SQL) running this in the database : "select datediff(s,'1/1/1970',getdate())"
(Result for me : 1209896627)

It shows you the current time in seconds since January 1st, 1970.

So a time in the database of 1209895007 would be (1209896627-1209895007) 1,620 seconds earlier, or rather, exactly 27 minutes earlier. (1620/60)

90 days would be a time difference of 60*60*24*90 (7,776,000) seconds earlier

It looks like the private messages only effects 3 tables : "phpbb_users, phpbb_privmsgs and phpbb_privmsgs_to"

The "privmsgs" table has the date/time stored as the field "message_time" that is the one you do the primary comparison off of. The "msg_id" and "author_id" cross reference to the "_privmsgs_to" and "_users" tables respectively. You can check against the "users" table to see when they last visited, if they are even active, and how many unread messages they may have. If you then remove the private messages, the "users" table needs updated with counts, and the " privmsgs_to" needs modified to remove the references.

In deleting private messages, If both the sender and the receiver are no longer members of the board and have been deleted, those are likely safe to get rid of. The problem is when only one of the users is no longer active and the other may still be active.

Hope it helps ?!!

User avatar
beggers
Registered User
Posts: 1257
Joined: Fri Nov 23, 2001 8:19 pm
Location: Las Vegas
Contact:

Re: How do I get rid of 5000 PMs?

Post by beggers » Sun May 04, 2008 5:19 pm

karlsemple wrote:
beggers wrote:Probably 60-70% of these members are long gone. I'm talking about PMs that have been stuck in the system for several years. I need a way to prune PMs.

There is no method for doing this in phpBB3 and would require a modification of some kind so you may want to post in the mod request forum, in fact I will move this into that forum if you wish :)
I appreciate everyone's input, but dealing with the SQL directly is beyond me so please go ahead and move this into the mod request forum. Thanks.

User avatar
beggers
Registered User
Posts: 1257
Joined: Fri Nov 23, 2001 8:19 pm
Location: Las Vegas
Contact:

Re: How do I get rid of 5000 PMs?

Post by beggers » Thu May 15, 2008 3:14 am

Is everyone aware that PMs accumulate forever and there's no way for an Admin to get rid of them?

I'm being hit by PM spammers and have no way of deleting their multiple PMs. Right now I have close to 6000 PMs that have accumulated over the years. Is there anyone who can put together a simple hack to prune PM either to/from specific users or PMs older that x days?

Evil20071
Registered User
Posts: 63
Joined: Mon Feb 04, 2008 7:43 am
Contact:

Re: How do I get rid of 5000 PMs?

Post by Evil20071 » Thu May 15, 2008 6:12 am

Why not change the limitations of everyone's inboxes? Limit the number of messages per folder to something like, say 25? Then set it to delete all older posts. This should delete all the older ones as well as keep you with 25/ folder for user. Granted, if a user needs a folder to save PM's in, then you'll have to set the number of PM's higher for everyone. Just talk to your members and see. :)

User avatar
beggers
Registered User
Posts: 1257
Joined: Fri Nov 23, 2001 8:19 pm
Location: Las Vegas
Contact:

Re: How do I get rid of 5000 PMs?

Post by beggers » Thu May 15, 2008 6:51 am

Evil20071 wrote:Why not change the limitations of everyone's inboxes? Limit the number of messages per folder to something like, say 25? Then set it to delete all older posts. This should delete all the older ones as well as keep you with 25/ folder for user. Granted, if a user needs a folder to save PM's in, then you'll have to set the number of PM's higher for everyone. Just talk to your members and see. :)
I don't think that's going to delete the old messages. I have PMs from people who haven't been associated with the board in years. It's about 7 years worth of PMs.

tommekemc
Registered User
Posts: 457
Joined: Wed May 14, 2008 6:36 pm
Location: Belgium
Contact:

Re: How do I get rid of 5000 PMs?

Post by tommekemc » Thu May 15, 2008 7:55 pm

you could delete them with a simple mySQL command, I'll do it via PHP since that's the way I'm sure of the date.
(make sure you first backed up your DB!!!)

Code: Select all

<?
$date=(date(U)-(90*24*60*60));
$theId=mysql_query("SELECT msg_id FROM phpbb_privmsgs WHERE message_time < ".$date) or die ('error 1');
while($theId=mysql_fetch_row($theId))
{
	mysql_query("DELETE FROM phpbb_privmsgs_to WHERE msg_id=".$theId) or die ('error 2');
}
$messagecount=mysql_query("DELETE FROM phpbb_privmsgs WHERE message_time < ".$date) or die ('error 3');
echo "deleted ".mysql_num_rows($messagecount)." files.";
?>
the posts will be deleted with no warning, make sure you backup your database first, and you'dd better wait for a reply to confirm this code before trying this yourself.
don't forget to be connected, and I can't ask you more, BACK UP THE MESSAGES!!!
My forum isn't online for this long, so I'll give it a test ASAP (somewhere tomorrow)
my sig
my projects

images deleted because of the 6kb (wtf?) rule...

kropes
Registered User
Posts: 33
Joined: Mon Apr 21, 2008 7:40 am
Location: Honolulu, Hawaii

Re: How do I get rid of 5000 PMs?

Post by kropes » Thu May 15, 2008 11:33 pm

Just FYI... read my post about 5 above.

that code shown does not look like it will reset the message counters, or it deltes PM's from inactive users. it looks like it is strictly based on date. you may have active users loosing old PM's

tommekemc
Registered User
Posts: 457
Joined: Wed May 14, 2008 6:36 pm
Location: Belgium
Contact:

Re: How do I get rid of 5000 PMs?

Post by tommekemc » Fri May 16, 2008 3:22 pm

indeed, that's what I said, it removes the messages.
But her question was something that just deleted all messages older than 90 days, that's what I supplied, I did my best writing the code.
indeed, there might be some more databases involved in the process, that's why I told to wait before trying this script.
I'll be checking how to edit it.
As far as I can see, the only counters are 'user_new_privmsg' and 'user_unread_privmsg'
these should be reset.
editing the script now.

Code: Select all

<?
$date=(date(U)-(90*24*60*60));
$pmCounterDeduct=mysql_query("SELECT msg_id, user_id FROM phpbb_privmsgs_to WHERE pm_new=1") or die ('error counter deduct');

//looking which messages have the var pm_new, then checking on the date
while($thecounter=myqsq_fetch_row($pmCounterDeduct)){
	$idPmCounterDeduct=mysql_query("SELECT msg_id FROM phpbb_privmsgs WHERE msg_id='".$thecounter[0]."' AND message_time < '".$date."'") or die ('error id fetch');//let it return something if this exists
	if(mysql_num_rows($idPmCounterDeduct)==1){
		//if here's 1 existing row
		$editTheCounterNumbers=mysql_query("UPDATE phpbb_users SET user_new_privmsg=(user_new_privmsg-1) WHERE user_id='".$thecounter[1]."') or die ('error counter deduct deducting');
	}
}

//now check for pm_unread
$pmCounterDeduct=mysql_query("SELECT msg_id, user_id FROM phpbb_privmsgs_to WHERE pm_unread=1") or die ('error counter deduct 2');
while($thecounter=myqsq_fetch_row($pmCounterDeduct)){
	$idPmCounterDeduct=mysql_query("SELECT msg_id FROM phpbb_privmsgs WHERE msg_id='".$thecounter[0]."' AND message_time < '".$date."'") or die ('error id fetch 2');//let it return something if this exists
	if(mysql_num_rows($idPmCounterDeduct)==1){
		//if here's 1 existing row
		$editTheCounterNumbers=mysql_query("UPDATE phpbb_users SET user_unread_privmsg=(user_unread_privmsg-1) WHERE user_id='".$thecounter[1]."') or die ('error counter deduct deducting 2');
	}
}

//now, start deleting!!!
$theId=mysql_query("SELECT msg_id FROM phpbb_privmsgs WHERE message_time < ".$date) or die ('error 1');
while($theIdArray=mysql_fetch_row($theId))
{
	mysql_query("DELETE FROM phpbb_privmsgs_to WHERE msg_id=".$theIdArray[0]) or die ('error 2');
}
$messagecount=mysql_query("DELETE FROM phpbb_privmsgs WHERE message_time < ".$date) or die ('error 3');
echo "deleted ".mysql_affected_rows($theId)." files.";
?>
The code should be working now, don't have that much messages on my forum, so it's very hard to try this.
MAKE A BACKUP FIRST (database-backup)
Please disable your forum, execute this php, and enable your forum again.
if anything went wrong reapply the backup.
Any error messages could help me.
Last edited by tommekemc on Fri May 16, 2008 7:03 pm, edited 2 times in total.
my sig
my projects

images deleted because of the 6kb (wtf?) rule...

User avatar
beggers
Registered User
Posts: 1257
Joined: Fri Nov 23, 2001 8:19 pm
Location: Las Vegas
Contact:

Re: How do I get rid of 5000 PMs?

Post by beggers » Fri May 16, 2008 4:36 pm

I very much appreciate the help on this, everyone. I look forward to trying the final working version, especially since I now have over 6000 PMs! :)

User avatar
blackwatch_uk
Registered User
Posts: 258
Joined: Wed Dec 20, 2006 11:02 am
Location: UK
Contact:

Re: How do I get rid of 5000 PMs?

Post by blackwatch_uk » Wed Jun 10, 2009 2:32 pm

tommekemc wrote:indeed, that's what I said, it removes the messages.
But her question was something that just deleted all messages older than 90 days, that's what I supplied, I did my best writing the code.
indeed, there might be some more databases involved in the process, that's why I told to wait before trying this script.
I'll be checking how to edit it.
As far as I can see, the only counters are 'user_new_privmsg' and 'user_unread_privmsg'
these should be reset.
editing the script now.

Code: Select all

<?
$date=(date(U)-(90*24*60*60));
$pmCounterDeduct=mysql_query("SELECT msg_id, user_id FROM phpbb_privmsgs_to WHERE pm_new=1") or die ('error counter deduct');

//looking which messages have the var pm_new, then checking on the date
while($thecounter=myqsq_fetch_row($pmCounterDeduct)){
	$idPmCounterDeduct=mysql_query("SELECT msg_id FROM phpbb_privmsgs WHERE msg_id='".$thecounter[0]."' AND message_time < '".$date."'") or die ('error id fetch');//let it return something if this exists
	if(mysql_num_rows($idPmCounterDeduct)==1){
		//if here's 1 existing row
		$editTheCounterNumbers=mysql_query("UPDATE phpbb_users SET user_new_privmsg=(user_new_privmsg-1) WHERE user_id='".$thecounter[1]."') or die ('error counter deduct deducting');
	}
}

//now check for pm_unread
$pmCounterDeduct=mysql_query("SELECT msg_id, user_id FROM phpbb_privmsgs_to WHERE pm_unread=1") or die ('error counter deduct 2');
while($thecounter=myqsq_fetch_row($pmCounterDeduct)){
	$idPmCounterDeduct=mysql_query("SELECT msg_id FROM phpbb_privmsgs WHERE msg_id='".$thecounter[0]."' AND message_time < '".$date."'") or die ('error id fetch 2');//let it return something if this exists
	if(mysql_num_rows($idPmCounterDeduct)==1){
		//if here's 1 existing row
		$editTheCounterNumbers=mysql_query("UPDATE phpbb_users SET user_unread_privmsg=(user_unread_privmsg-1) WHERE user_id='".$thecounter[1]."') or die ('error counter deduct deducting 2');
	}
}

//now, start deleting!!!
$theId=mysql_query("SELECT msg_id FROM phpbb_privmsgs WHERE message_time < ".$date) or die ('error 1');
while($theIdArray=mysql_fetch_row($theId))
{
	mysql_query("DELETE FROM phpbb_privmsgs_to WHERE msg_id=".$theIdArray[0]) or die ('error 2');
}
$messagecount=mysql_query("DELETE FROM phpbb_privmsgs WHERE message_time < ".$date) or die ('error 3');
echo "deleted ".mysql_affected_rows($theId)." files.";
?>
The code should be working now, don't have that much messages on my forum, so it's very hard to try this.
MAKE A BACKUP FIRST (database-backup)
Please disable your forum, execute this php, and enable your forum again.
if anything went wrong reapply the backup.
Any error messages could help me.


Can anybody confirm if this command works?

Locked

Return to “[3.0.x] MOD Requests”