Prune Private Messages PMs Over X Months Old

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)
Get Involved
kjnpf
Registered User
Posts: 104
Joined: Tue Apr 29, 2003 9:27 am
Location: Cheshire, England
Contact:

Prune Private Messages PMs Over X Months Old

Post by kjnpf » Mon Jan 12, 2009 3:26 am

I have searched without finding any MODs that can prune Private Messages from the database that are, say, 6 months old or more. Before I go and write a script myself to get the job done (and remove some of the ten thousand+ PMs from my database) I thought I would ask here to see if it has been done and is available already!!

So... are there any PM Pruning MODs for phpBB 3.0.4

If not, anyone know where I can get the database schema to find out how the PMs work and how they are stored so I can delete all data associated with a private message? There seems to be a few tables with data in and I don't want to go and break my database!!
Keith Jordan
Neptune Pink Floyd

User avatar
A_O_C
Registered User
Posts: 2383
Joined: Sun Jul 01, 2007 11:26 pm
Location: phpbb_

Re: Prune Private Messages PMs Over X Months Old

Post by A_O_C » Mon Jan 12, 2009 4:47 am

in the {phpbb_prefix}_privmsgs table, there is a field named "message_time". it's in UNIX time, so you would have to compare that to the current time inside a SQL query.

kjnpf
Registered User
Posts: 104
Joined: Tue Apr 29, 2003 9:27 am
Location: Cheshire, England
Contact:

Re: Prune Private Messages PMs Over X Months Old

Post by kjnpf » Mon Jan 12, 2009 8:36 am

I have been looking at the includes/functions_privmsgs.php file and the code that deletes private messages is in there. I will have to have a look at what it is doing and understand the structure and relationships between the various tables in the database and come up with something.

I would have thought this MOD would have a) been made already and b) would even be a part of phpBB!!

Seems I shall have to make it unless someone else reads this thread and can shed light on where an existing MOD or script could be!!
Keith Jordan
Neptune Pink Floyd

User avatar
digbydo
Registered User
Posts: 55
Joined: Tue Jun 24, 2008 5:27 am
Location: Cyberspace

Re: Prune Private Messages PMs Over X Months Old

Post by digbydo » Thu Jan 29, 2009 9:08 am

A_O_C wrote:in the {phpbb_prefix}_privmsgs table, there is a field named "message_time". it's in UNIX time, so you would have to compare that to the current time inside a SQL query.
Could the messages simply be deleted from that table or is there a link to others somewhere? An index of some sort to be rebuilt?

User avatar
A_O_C
Registered User
Posts: 2383
Joined: Sun Jul 01, 2007 11:26 pm
Location: phpbb_

Re: Prune Private Messages PMs Over X Months Old

Post by A_O_C » Thu Jan 29, 2009 4:06 pm

i havent looked. just make sure you make a backup before you do anything with your phpbb database. ;)

Toy Maker
Registered User
Posts: 87
Joined: Tue Jun 10, 2003 9:33 pm

Re: Prune Private Messages PMs Over X Months Old

Post by Toy Maker » Sun Feb 15, 2009 3:17 am

Anyone get this working ??? I need to prune 1000's of PMs out of my database.
I set the PM limit to 1000, cause I didnt want to clean mineup, and now everyone has a huge inbox...
Please help.

James

User avatar
digbydo
Registered User
Posts: 55
Joined: Tue Jun 24, 2008 5:27 am
Location: Cyberspace

Re: Prune Private Messages PMs Over X Months Old

Post by digbydo » Mon Feb 16, 2009 4:26 pm

Toy Maker wrote:Anyone get this working ??? I need to prune 1000's of PMs out of my database.
I set the PM limit to 1000, cause I didnt want to clean mineup, and now everyone has a huge inbox...
Please help.

James
I see the error of your way.... set everyone else to, say, 20 - put yourself in a group with permission for 1000 PM's.
You could then set the other users to auto-delete on receipt of a new PM or get them to clear down manually.
:)

User avatar
wads24
Registered User
Posts: 528
Joined: Fri Jun 10, 2005 4:44 am
Name: James

Re: Prune Private Messages PMs Over X Months Old

Post by wads24 » Mon Mar 02, 2009 2:06 pm

I also have so very many pm's in my outbox, Would there be a way to display more messages on 1 page? So don't have to repeat the manually delete option on over 200 pages?
Thanks in advance for a reply.

net83it
Registered User
Posts: 500
Joined: Sun Jan 15, 2006 2:42 pm
Location: Sicily
Contact:

Re: Prune Private Messages PMs Over X Months Old

Post by net83it » Thu Jul 29, 2010 7:52 pm

any news about understanding how pms are stored in the database?

i see 4 phpbb tables...

wile
Registered User
Posts: 57
Joined: Mon Apr 30, 2007 4:21 pm

Re: Prune Private Messages PMs Over X Months Old

Post by wile » Mon Dec 20, 2010 1:46 pm

Dear all,

I was investigating this topic since I have faced to similar situation that some private messages older than something should be removed.
From my point of view we need to follow this process:

TABLE phpbb_privmsgs
- there is list of all private messages
- we need to find all that are older than something based on unix message_time in this table
- each message has own msg_id
- each message has also information if message contains an attachment

TABLE phpbb_privmsgs_to
- if we are about to remove something from phpbb_privmsgs we should also remove all entries with same msg_id also from this table -- otherwise we could create "phantom" messages (there would be something that user has 3 messages in inbox but no new message displayed in inbox in real)

TABLE phpbb_attachments
- If private message contains some attachment (based on information from phpbb_privmsgs) then we should find respective row in phpbb_attachment where is post_msg_id same like msg_id of our private message that we want to remove and (in_message = 1) - what indicates that attachment was part of PM (and not part of post because both can have same ID)
- this table contains "physical_filename" of the attachment
- this attachment can be removed with unlink()
- if exists also thumbnail for the file - it is indicated by (thumbnail=1). FIle has prefix thumb_.
- then we can remove also entry from phpbb_attachments

Please review and let me know any issues that you found in my draft.

Thank you.
Last edited by wile on Sun Nov 16, 2014 1:38 pm, edited 1 time in total.

wile
Registered User
Posts: 57
Joined: Mon Apr 30, 2007 4:21 pm

Re: Prune Private Messages PMs Over X Months Old

Post by wile » Fri Dec 31, 2010 10:38 am

And this is my code for purge_pm.php that allows mentioned deletion of older PMs to admins and moderators.
I am only amateur but I tested it and it should work well.
Let me know if any issues found.

Code: Select all

<?php
define('IN_PHPBB', true);
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : './';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include($phpbb_root_path . 'common.' . $phpEx);
// Start session management
$user->session_begin();
$auth->acl($user->data);
$user->setup();

// functions*

function unix_days_ago($pocet_dni)
	{
	return time() - (60*60*24*(int)$pocet_dni);
	}
// functions*

if ($user->data['user_id'] == ANONYMOUS)
{
   die ("<b>User is not logged in.</b>");
}

else
{
    echo '<fieldset><legend>Purge PMs</legend><b>Logged user: ' . $user->data['username_clean'] . '.</b><br>';
	if ($auth->acl_get('a_') || ($auth->acl_get('m_')))
	{
	echo"<form action=\"purge_pm.php\" method=\"post\">";
	echo"Find private messages older than <input name=\"pocet_dni\" type=\"text\"> days.<br>";
	echo"<input type=\"hidden\" name=\"action\" value=\"vypis\"><input type=\"submit\" value=\"OK\"></form>";
	}
	else die("<b>You do not have a permission.</b>");
}

if ((!($auth->acl_get('a_'))) && (!($auth->acl_get('m_')))) die();

switch ($_POST["action"])
	{
	case "vypis":
		if ($_POST["pocet_dni"]!=="") $pocet_dni = $_POST["pocet_dni"]; else $pocet_dni="0";
		$timestamp = unix_days_ago($pocet_dni);
		
		$sql = 'SELECT msg_id,message_time,message_subject,message_attachment FROM phpbb_privmsgs where message_time < '.$timestamp.' order by message_time';
		$result = $db->sql_query($sql);
		$row = $db->sql_fetchrowset($result);
		$size = sizeof($row);
		echo "Found <b>".$size. "</b> messages older than <b>".$pocet_dni."</b> days.<br>";
		//print_r ($row);
	
		if ($size!==0)
		{
			echo"<form name=\"test\" action=\"purge_pm.php\" method=\"post\"><table border=1><tr><td><input type=\"checkbox\"></td><td>ID </td><td>Date and time</td><td>Subject</td><td>Attachment</td></tr>";
			foreach ($row as $row2) 
			{
				echo"<tr>";
				echo "<td><input type=\"checkbox\" name=\"id[]\" value=\"".$row2['msg_id']."\"></td>";
				echo "<td>".$row2['msg_id']."</td>";
				$pole_id[] = $row2['msg_id'];
				$sprava_cas = date("Y-m-d h:i:s A",$row2['message_time']);
				echo "<td>".$sprava_cas."</td>";
				echo "<td>".$row2['message_subject']."</td>";
					if ($row2['message_attachment']=="1") 
					{
						$sql2 = 'SELECT physical_filename FROM phpbb_attachments where post_msg_id = '.$row2['msg_id'].'';
						$result = $db->sql_query($sql2);
						$row2 = $db->sql_fetchrowset($result);
						$size2 = sizeof($row2);
						echo "<td>Y - ".$size2 ."</td>"; 
					}
					else echo "<td>N</td>";
				
				echo"</tr>\n";
			}
			echo"</table><br>";
			echo"<a href=\"#\" onclick=\"marklist('test', 'id', true); return false;\">Mark all</a> &bull; <a href=\"#\" onclick=\"marklist('test', 'id', false); return false;\">Unmark all</a><br>";
			echo"<input type=\"hidden\" name=\"action\" value=\"delete\"><input type=\"submit\" value=\"Remove selected\"></form><br> * After clicking the button all marked messages will be completely removed from database.";
		}		
		$db->sql_freeresult($result);
	break;
	
	case "delete":
		$id = $_POST['id']; 
		$pocet = sizeof($id);
		echo "Preparation for deletion of <b>$pocet</b> private messages. <br><br>";
			for ($i=0;$i<$pocet;$i++)
				{
				echo"<hr>&bull; Message id <b>$id[$i]</b> - deletion in progress.<br>";
				$sql2 = 'SELECT physical_filename FROM phpbb_attachments where post_msg_id = '.$id[$i].'';
				$result = $db->sql_query($sql2);
				$row2 = $db->sql_fetchrowset($result);
				$size2 = sizeof($row2);
				if ($size2!=="0")
					{
						foreach ($row2 as $hodnota) 
						{
						echo"&nbsp;&nbsp;&nbsp;Attachment found. [file <b>files/".$hodnota['physical_filename']."</b>, was removed.]<br>";
						unlink ("files/".$hodnota['physical_filename']);
						$sql = 'DELETE FROM phpbb_attachments where post_msg_id = '.$id[$i].'';
						$result = $db->sql_query($sql);
						echo"&nbsp;&nbsp;&nbsp; Information about attachment removed from table <b>phpbb_attachments</b>.<br>";
						}
					}

		$sql = 'DELETE phpbb_privmsgs AS t1, phpbb_privmsgs_to AS t2 FROM phpbb_privmsgs AS t1 INNER JOIN phpbb_privmsgs_to AS t2
WHERE t1.msg_id=t2.msg_id AND t1.msg_id='.$id[$i].'';
		$result = $db->sql_query($sql);
		echo"&nbsp;&nbsp;&nbsp; Table <b>phpbb_privmsgs_to</b> purged.<br>";
		echo"&nbsp;&nbsp;&nbsp; Deletion of private message id.<b>$id[$i]</b> completed.";
		$db->sql_freeresult($result);
		}		
	
	break;
	}
echo"</fieldset>";

?>
<script>
function marklist(id, name, state)
{
	var parent = document.getElementById(id);
	if (!parent)
	{
		eval('parent = document.' + id);
	}

	if (!parent)
	{
		return;
	}

	var rb = parent.getElementsByTagName('input');
	
	for (var r = 0; r < rb.length; r++)
	{	
		if (rb[r].name.substr(0, name.length) == name)
		{
			rb[r].checked = state;
		}
	}
}
</script>
Script should be placed in forum folder.
Using this on your own responsibility only ! :D

User avatar
wads24
Registered User
Posts: 528
Joined: Fri Jun 10, 2005 4:44 am
Name: James

Re: Prune Private Messages PMs Over X Months Old

Post by wads24 » Fri Dec 31, 2010 11:26 am

This previous poster only has 2 posts, and I am scared to try this without someone checking it out from phpbb. Has anyone from phpbb tested this script?
Thanks in advance for a reply.

.m.
Registered User
Posts: 438
Joined: Wed Nov 04, 2009 8:39 pm

Re: Prune Private Messages PMs Over X Months Old

Post by .m. » Fri Dec 31, 2010 1:05 pm

Hi, please do not worry about the number of his posts . I find that the code
is well written. you can try it :) & tell others whether it works fine or not ..

Ozo
Registered User
Posts: 330
Joined: Mon Dec 13, 2010 7:57 pm

Re: Prune Private Messages PMs Over X Months Old

Post by Ozo » Fri Dec 31, 2010 1:31 pm

I just did on a test board. I don't have "old" pm's in the database to delete yet, but it seems to do the job.
regardless, always always make a backup of your database/files especially when dealing with any script or MOD that alters your forums database. :)

wile
Registered User
Posts: 57
Joined: Mon Apr 30, 2007 4:21 pm

Re: Prune Private Messages PMs Over X Months Old

Post by wile » Sat Jan 01, 2011 11:01 am

btw, if you enter "0" days then script will list all PMs in database (based on criteria messages older than 0 days) and you can choose which messages you want to remove. :)

Locked

Return to “[3.0.x] MOD Requests”