vb.org Archive
Page 1 of 2 1 2
Show 40 post(s) from this thread on one page

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vBulletin 2.x Full Releases (https://vborg.vbsupport.ru/forumdisplay.php?f=4)
-   -   Mass Delete Old Private messages (https://vborg.vbsupport.ru/showthread.php?t=27421)

amykhar 09-04-2001 10:00 PM
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");

?>

Gutspiller 01-23-2002 06:22 PM
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 01-23-2002 06:40 PM
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 01-27-2002 01:13 PM
What version is this for?

TWTCommish 01-27-2002 01:17 PM
It's simple enough that I think it'll work for any version that supports the PM system.

PhotoGenie 01-27-2002 01:20 PM
TY!! TWT :)

LuBi 02-03-2002 03:58 AM
Can we get something like this that does it automatically?

amykhar 03-02-2002 04:08 PM
Lubi, just create a cron job for the script.

Amy

alexi 06-29-2002 11:34 AM
Which numbers would I change if I wanted a figure other than 60 days? Say 6 months? (180 days)

alexi 07-08-2002 10:49 PM
anyone have any idea on which number to change? The last 60?

Jawelin 07-10-2002 03:25 PM
Quote:
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 09-29-2002 12:25 AM
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 09-29-2002 01:37 AM
works fine for me

eoc_Jason 09-30-2002 04:37 AM
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:

PHP Code:
$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); 10-11-2002 07:32 PM
Works pretty good. Thanks

Bison 10-12-2002 02:14 PM
This would be cool is you can add this into the Admin cp ...

Night Owl 07-24-2003 12:35 AM
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 01-15-2004 04:00 PM
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 01-16-2004 01:03 PM
Quote:
Originally Posted by DJ5A
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:

PHP Code:
$time = time() - (30 * 24 * 60 * 60); 

djbaxter 02-24-2007 02:04 PM
I'd like to adapt this to work with version 3.60.

The existing version shows:

Code:
<?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 02-24-2007 09:00 PM
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.
Code:
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 02-24-2007 09:12 PM
Got some help on another forum which got me this far:

Code:
<?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

Quote:
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

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

Jacqueline 02-24-2007 09:58 PM
I believe the error is caused by the
Code:
$DB_site->query
.

Try this:

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

djbaxter 02-24-2007 10:33 PM
Better.

Code:
<?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:

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

Jacqueline 02-24-2007 10:51 PM
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 02-24-2007 10:54 PM
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 02-24-2007 11:02 PM
How did you run the first query exactly? I get an SQL error...

Jacqueline 02-24-2007 11:08 PM
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.

Code:
SELECT pmtextid FROM vbpmtext WHERE dateline < 1170202068
I was able to get the Resource error you are getting by changing this
Code:
print $a[oldpms];
to this
Code:
print $a;

Jacqueline 02-24-2007 11:12 PM
Here's the exact code I'm running right now. I changed the days to 25.
Quote:
<?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 02-25-2007 12:42 AM
if I use

Code:
print $a[oldpms];
it prints a blank.

If I use

Code:
print $a;
I get resource #16

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

Jacqueline 02-25-2007 01:14 AM
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.
Code:
<?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 02-25-2007 11:49 PM
A hint on another forum got me closer.

Using this:

Code:
<?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:

Quote:
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 02-26-2007 12:42 AM
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 02-26-2007 01:02 AM
Thanks, Jacqueline.

I've been soliciting some help over at TAZ as well and member Sarah there has suggested this:

Quote:
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 02-26-2007 01:27 AM
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 02-26-2007 02:52 PM
Here's the latest:

Code:
<?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:

Quote:
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 02-26-2007 03:13 PM
Answering my own question, from https://vborg.vbsupport.ru/showthread.php?t=75207:

Quote:
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.
Quote:
- 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:


Code:
$query = $DB_site->query($sqltext);
$array = $DB_site->fetch_array($query);
$DB_site->free_result($query);
If simply becomes

Code:
$array = $DB_site->query_first($sqltext);

djbaxter 02-26-2007 03:37 PM
So:

If I understand this now, the following tells me how many PMs, PM Texts, and PM Receipts need to be deleted:

Code:
<?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:

Code:
<?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 02-26-2007 08:47 PM
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 02-26-2007 08:49 PM
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.


All times are GMT. The time now is 04:46 PM.
Page 1 of 2 1 2
Show 40 post(s) from this thread on one page

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01582 seconds
  • Memory Usage 1,862KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (19)bbcode_code_printable
  • (3)bbcode_php_printable
  • (10)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (1)pagenav_pagelink
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (40)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • pagenav_page
  • pagenav_complete
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete