vb.org Archive - Mass Delete Old Private messages

Page 2 of 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)

stinger2

02-26-2007 09:00 PM

Quote:

https://vborg.vbsupport.ru/showthrea...mass+delete+pm

this is an answer i got from vb.com

hope it helps

Jacqueline

02-26-2007 11:00 PM

Thanks, stinger, but it looks like they are deleting everything in the tables with those.

djbaxter

02-27-2007 12:47 AM

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?

Code:

 $a = $db->query("DELETE * FROM vbpmtext, vbpm, vbpmreceipt WHERE (vbpm.pmid = vbpmreceipt.pmid) AND (vbpm.pmtextid = vbpmtext.pmtextid)AND vbpmtext.dateline < $time");

Jacqueline

02-27-2007 01:29 AM

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

02-27-2007 02:02 AM

It would be this:

Code:

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

02-27-2007 08:25 AM

It didn't work. Here's my adjusted code and the results I got from it:

Code:

<?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"); 



?>

Code:

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

02-27-2007 12:55 PM

Try this one:

Code:

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

02-27-2007 11:56 PM

Well, it's a step closer. The code did not produce any errors, but it also did not delete anything.

djbaxter

02-28-2007 12:06 AM

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:

Code:

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

02-28-2007 12:15 AM

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

02-28-2007 12:30 AM

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.

All times are GMT. The time now is 04:17 AM.

Page 2 of 2

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

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01095 seconds Memory Usage 1,740KB 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 (6)bbcode_code_printable (1)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 (11)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: