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]

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]

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]

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]

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

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

[restless]

Warning: Bad arguments to implode() in /forums/admincp/pmdelete.php on line 17

Warning: Bad arguments to implode() in /forums/admincp/pmdelete.php on line 18

Warning: Bad arguments to implode() in /admincp/pmdelete.php on line 19

(i left full path out)

[restless]

by the way... even though the private messages aren't there, because it's saying user's messages ARE there, their pm messages limits are filling up. mine included!

i have 4 private messages, but my box is now at it's limit because it's saying i have 70.

i realize i can just increase the limit, but man... what a pain.

[restless]

any ideas?

[g-force2k2]

Warning: Bad arguments to implode() in /forums/admincp/pmdelete.php on line 17

Warning: Bad arguments to implode() in /forums/admincp/pmdelete.php on line 18

Warning: Bad arguments to implode() in /admincp/pmdelete.php on line 19

(i left full path out)

I believe this is because you already deleted the fields from the pm table but the pmtext and pmreceipt tables both exist, another script would have to be written in order to delete the remaining records.

Regards,
g-force2k2

[NTLDR]

The pmtext table is cleaned hourly by a cron job.

[g-force2k2]

The pmtext table is cleaned hourly by a cron job.

Yeah thanks for the information, but it also only deletes the pmtext of the pm's which were deleted, and it also does not delete the pmreceipt table.

g-force2k2