PM pruning Advice

[Mark.B]

I'm trying to write a query (or queries) that will prune PMs over a certain age, but without affecting any custom PM folders.

I need to do this because my PM table is enormous due to all the automated PMs sent out by the arcades that people never delete.

However, I am struggling. There are a number of mods out there but all they do is prune the pm table. I need also to prune the pmtext table because that's the greater size.

The trouble I'm running into is that by selecting pmtext with a particular PM id from, say, the inbox, the same pmtextid may also be required in someone's sent items.

Although I seem to get close, I always end up with a load of "blank" PM entries for some people, with a default timestamp and no subject, presumably because the pmtext entry has been pruned but not the pm entry.

So far I have this, which was adapted from one of the mods:

$time = time() - (90 * 24 * 60 * 60);
 $query = "DELETE " . TABLE_PREFIX . "pmtext . * , " . TABLE_PREFIX . "pm . *
 FROM " . TABLE_PREFIX . "pmtext, " . TABLE_PREFIX . "pm
 WHERE pm.folderid IN (0, -1)
 AND " . TABLE_PREFIX . "pm.pmtextid = " . TABLE_PREFIX . "pmtext.pmtextid
 AND " . TABLE_PREFIX . "pmtext.dateline < $time";
 mysql_query($query);

But this is not quite working right as some users end up with blank PM entries in their inbox or sent items.

It's driving me mad but I cannot think of a way to select all PMs from the PM table that are over 90 days old and are flagged as being in someone's inbox, find the pmtextid, delete both that entry and the corresponding entry in the sent items, and then delete the pmtext entry that matches up.

Can anyone think of a way to do this?