XF 1.2 Delete All Posts By Guests

[DRE]

How do you delete all posts by guests?

 

[DRE]

Got my answer. http://xenforo.com/community/threads/delete-all-guest-posts.51837/

 

[DRE]

My question is related to: http://xenforo.com/community/threads/xenforo-1-2-to-xenforo-1-2-importer-error.56494/

Server Error
Mysqli statement execute error : Column 'last_message_user_id' cannot be null

So I was looking at this thread: http://xenforo.com/community/thread...conversation_recipient-user_id-to-zero.54996/

and it made me realized that maybe all I need to do is create an account that no one will use and just change the user ids from 0 to 1. I would do the same for profile posts and messages. So all I would need to do is search by user id and replace it with the user ID of the new user I made. Once I do that, I can probably continue importing. The new user id will be a user called 'Guest'.

What's the query to change a user id in profile posts, conversations and messages?

 

[DRE]

This is how I change a user id and username with someone else for both posts and threads.

Got this from another thread:

UPDATE xf_post SET user_id = 1319 WHERE username ='';
UPDATE xf_thread SET user_id = 1319 WHERE username ='';

I've altered it to look sort've like what I need. Would this work? 0 is the null account of a spammer who was deleted and 599 being the Guest account I created.

UPDATE xf_post SET user_id = 0 WHERE username ='Guest';
UPDATE xf_thread SET user_id = 0 WHERE username ='Guest';
UPDATE xf_post SET user_id = 0 WHERE user_id = 599;
UPDATE xf_thread SET user_id = 0 WHERE user_id = 599;

Too scared to run this lol.

 

[DRE]

Been looking at 'Dealing With Forum Spam' by @Jake Bunce http://xenforo.com/community/resources/dealing-with-forum-spam.980/

Sorta close to what I need http://xenforo.com/community/threads/deleted-a-user-but-the-name-still-shows-up.35531

And from this thread: http://xenforo.com/community/threads/deleted-a-user-but-the-name-still-shows-up.35531/

You can run these queries on your database to change the name on the posts from the deleted user:

UPDATE xf_post
SET username = 'new name'
WHERE username = 'old name'
AND user_id = 0;

UPDATE xf_thread
SET username = 'new name'
WHERE username = 'old name'
AND user_id = 0;

I don't know their user name and just want to change it to Guests and since they are already a guests with 0 as their user id can I change their user id to an account I created?

 

[DRE]

What if you don't know their user name and just want to change it to Guests and since they are already a guests with 0 as their user id can you change their user id to an account you created?

Yes you can. It's just a simple update query.

What's the query?

To change the user_id?

UPDATE xf_post
SET user_id = 5
WHERE user_id = 0;

I created a new guest account whose user id is 1688 then ran this query:

UPDATE xf_post
SET user_id = 1688
WHERE user_id = 0;

Result: 155 rows affected. ( Query took 0.0052 sec )

So next I tried this with threads.

UPDATE xf_thread
SET user_id = 1688
WHERE user_id = 0;

Result: 14 rows affected. ( Query took 0.0166 sec )

Hmm ran into an error when I tried the following code:

UPDATE xf_conversation_user
SET user_id = 1688
WHERE user_id = 0;

#1054 - Unknown column 'user_id' in 'where clause'

What's that mean? Same error also occurs in xf_liked_content
Oh wait, the user id is different. It's like_user_id

and I figured out the xf_conversation_user error. It must be:

UPDATE xf_conversation_user
SET owner_user_id = 1688
WHERE owner_user_id = 0;

and

UPDATE xf_conversation_user
SET last_message_user_id = 1688
WHERE last_message_user_id = 0;

K I'm getting the hang of this.

 

[DRE]

Even if you try to run this query:

UPDATE xf_post
SET user_id = 1688
WHERE user_id = 0;

It doesn't work all the time because even if you deleted the user their account user name AND user id still shows.

WTF. That may have been a bug from an earlier version (buggier version) of Xenforo. Oh wait...no I figured it out.

This is probably because of that Floris delete zero posters addon.

 

[DRE]

So if a post is made by an account that has been deleted but their user id still shows in the database when it's not supposed to, the only way to delete that account is to do it manually. I think I already have queries for that... which leads me to my next question, how do I find a post in the database just by entering the post ID?

 

[DRE]

Might as well add these threads in here before I lose em.

How To Find A Post In Database by Searching User ID?

SELECT * FROM xf_post WHERE user_id = "xxx"

How To Find A Post In Database by Searching Post ID?

SELECT * FROM xf_post WHERE post_id = "xxx"