vb.org Archive - Mass Delete Old Private messages

Page 3 of 6

Last »

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)

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.

All times are GMT. The time now is 08:38 AM.

Page 3 of 6

Last »

Show 40 post(s) from this thread on one page

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01048 seconds Memory Usage 1,749KB 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 (11)bbcode_code_printable (3)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (4)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (10)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
Messages: