vb.org Archive - querie for deleting private messages by date

Page 1 of 2

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

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - querie for deleting private messages by date (https://vborg.vbsupport.ru/showthread.php?t=60177)

restless

01-13-2004 09:36 PM

querie for deleting private messages by date

when i imported my old database, it messed up and only imported certain things. it DID import private messages, but unfortunately... it DIDN'T import my users.

so... i have a bunch of new users signing up, and for each new user, they find a cache of old pm's in their inbox that belong to whoever had their user id# on the old forum. this is going to keep happening right up through our 4000 and something registrations unless i can figure out how to kill them.

is there a mysql querie i can use to delete pms from before a certain date? this is a very sensitive issue, as there are some fairly private pms in there.

basically, i need to delete all private messages from before December 24, 2003.

Zachery

01-13-2004 09:42 PM

Quote:

Originally Posted by restless

you can login and just empty the whole table... altho that wouldnt do for your date solution

why didnt you just rever tot he back up and try to import again?

restless

01-13-2004 09:45 PM

my backup was screwed.

Zachery

01-13-2004 09:48 PM

Quote:

Originally Posted by restless

my backup was screwed.

then why not just reinstall and start fresh? (just a thought)

:) im sure someone will come up with a querry to clear all the pm's by date but i know i cant

you COULD just empty the table (not drop it)

NTLDR

01-13-2004 09:56 PM

This is easier said then done, due to the 3 PM tables in vB3. You'd need to get the PM's text based on date, then get the pm info for that single one, then delete the PM, PM Text and PM Recipt for that PM and then move onto the next one. I think thats right anyway.

restless

01-13-2004 09:59 PM

Quote:

Originally Posted by Faranth

then why not just reinstall and start fresh? (just a thought)

hehe... because before i figured out what was going on, i'd already had over 300 users re-register and i've got over 8000 posts. twice in 2 weeks would not have been good.

Zachery

01-13-2004 10:00 PM

Quote:

Originally Posted by restless

hehe... because before i figured out what was going on, i'd already had over 300 users re-register and i've got over 8000 posts. twice in 2 weeks would not have been good.

ok good point, i think my idea of emptying the tables seems to be the best idea

restless

01-15-2004 09:45 PM

i went ahead and did just that.

restless

01-15-2004 10:30 PM

hmm... and now i can't get rid of the Private Messages display:

"Private Messages: Unread 1, Total 61"

g-force2k2

01-15-2004 11:08 PM

Upload this file as pmdelete.php and run it in your browser. Haven't tested it but it should do the trick.

PHP Code:


		
			
<?php



error_reporting ( E_ALL & ~E_NOTICE ) ;



require ( './global.php' ) ;



$_dtime = mktime ( 0, 0, 0, 12, 24, 2003 ) ;

$getpms = $DB_site->query ( "SELECT pm.pmid AS id,pmtextid AS tid

    FROM " . TABLE_PREFIX . "pm 

    LEFT JOIN " . TABLE_PREFIX . "pmreceipt ON pmreceipt.pmid = pm.pmid

    WHERE pmreceipt.sendtime <= $_dtime" ) ;

while ( $pm = $DB_site->fetch_array ( $getpms ) ) :

    $_idarray[] = $pm['id'] ;

    $_tidarray[] = $pm['tid'] ;

endwhile ;



$DB_site->query ( "DELETE FROM " . TABLE_PREFIX . "pm WHERE pmid IN ( '" . implode ( "', '", $_idarray ) . "' )" ) ;

$DB_site->query ( "DELETE FROM " . TABLE_PREFIX . "pmreceipt WHERE pmid IN ( '" . implode ( "', '", $_idarray ) . "' )" ) ;

$DB_site->query ( "DELETE FROM " . TABLE_PREFIX . "pmtext WHERE pmtextid IN ( '" . implode ( "', '", $_tidarray ) . "' )" ) ;



echo "<p><font face=\"courier\">PM Deletion Completed.</font></p>" ;



?>

Cheers,
g-force2k2

All times are GMT. The time now is 04:14 PM.

Page 1 of 2

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

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01066 seconds Memory Usage 1,747KB 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 (1)bbcode_php_printable (4)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 (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: