Delete certain posts from a user with specific content?
 

Hi!

I need to delete (let's say) 500 out of 1000 messages of a user, where content of the post is equal (always) to a certain string.

How can I achieve that and that will still deduct them from the user's post count plus not corrupting any table/database?

Please be as specific as possible!

Thanks!

DELETE FROM $table WHERE $userid, $column LIKE '%$string%';

$table = What table (post, privatemessages)
$userid = User id (first user is 1)
$column = What part of the table is the string in (message)
$string = Phrase or word

I'm not 100% that will work, that was off top my head. Also after you have to update the counts via admin panel.

Just did
DELETE FROM post WHERE pagetext LIKE '%string%' AND username LIKE '%username%';

and it seems it worked nice.

Thanks!

mmmmmmmmmmm.......... errrrr.......

Houston we've got a problem!!

Right after running the query, I updated user counts.

THen, I searched for this user via FORUM SEARCH and then clicked on the SEE ALL POSTS FOR THIS USER.

Seems some of the threads this user was participating show NO FORUM related to when you search for all posts for this user.

And upon clicking on them it leads to a PAGE NOT FOUND error on the browser.

What's going on?

It matches the description of an orphan thread, but I just deleted POSTS from a single user! I don't want to loose the rest of the thread the offending message was in, so how could I fix this?

Thanks!

Woah!

There's more!

Doing further research and queries, I notice problematic threads not appearing on the search results lead to a forumid which does not exist.

Quering that forumid in table threadid throws... 257 records!

257 threads pointing to a non existant forumid!

How can this be possible?

Now, is there any query that would allow me to FIND (not delete!) ORPHAN threads? Ie threads pointing to invalid forumids? (ie forumids no longuer existing!)

Where can this problem come from?

I've managed to run tables check in a monthly basis with no errors ever!

DB is around 200Mb, including attachments.

Please advice.

Thanks!