querie for deleting private messages by date

[restless]

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]

Originally Posted by restless

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.

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]

my backup was screwed.

[Zachery]

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]

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]

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]

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]

i went ahead and did just that.

[restless]

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

"Private Messages: Unread 1, Total 61"

[g-force2k2]

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