vb.org Archive - Mass Delete Old Private messages

Page 4 of 6

« First

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-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 02:53 PM.

Page 4 of 6

« First

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

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01954 seconds Memory Usage 1,779KB 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 (7)bbcode_code_printable (5)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: