Deleted a user but the name still shows up

[Jeffin]

I recently deleted a user on his request but his name and posts still show up on the forums. How do I remove all traces completely? Thanks.

 

[Jake Bunce]

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;

Or if you want to soft delete those posts then run these queries.

The red is the username of the deleted user whose posts and threads you want to soft delete. The blue is the deletion info (user_id and username of the user who is doing the deleting, as well as a stated reason for the log). Backup first:

UPDATE xf_post
SET message_state = 'deleted'
WHERE message_state = 'visible'
AND username = 'old name'
AND user_id = 0;

INSERT INTO xf_deletion_log (content_type, content_id, delete_date, delete_user_id, delete_username, delete_reason)
	SELECT 'post', post_id, UNIX_TIMESTAMP(), 1, 'admin', 'manually deleted via query'
	FROM xf_post
	WHERE message_state = 'deleted'
	AND username = 'old name'
	AND user_id = 0
ON DUPLICATE KEY UPDATE
	content_id = VALUES(content_id);

UPDATE xf_thread
SET discussion_state = 'deleted'
WHERE discussion_state = 'visible'
AND username = 'old name'
AND user_id = 0;

INSERT INTO xf_deletion_log (content_type, content_id, delete_date, delete_user_id, delete_username, delete_reason)
	SELECT 'thread', thread_id, UNIX_TIMESTAMP(), 1, 'admin', 'manually deleted via query'
	FROM xf_thread
	WHERE discussion_state = 'deleted'
	AND username = 'old name'
	AND user_id = 0
ON DUPLICATE KEY UPDATE
	content_id = VALUES(content_id);

 

[jgas]

hi!
I tried this, but phpmyadmin answers with "# MySQL returned an empty result set (i.e. zero rows)."

Maybe the problem is that the username was USER NAME with an empty character in the middle?

I used this query:



UPDATE xf_post
SET username = 'Guest'
WHERE username = 'Magic Bass'
AND user_id = 0;



UPDATE xf_thread
SET username = 'Guest'
WHERE username = 'Magic Bass'
AND user_id = 0;



Thanks!!

 

[Jake Bunce]

You would have to confirm that the conditions are correct. It will only update records WHERE username = 'Magic Bass' AND user_id = 0 . If the name is misspelled or the user_id is not 0 then it won't update any records.

 

[jgas]

That's strange... it worked but without the line "AND user_id = 0"

 

[Jake Bunce]

That's strange... it worked but without the line "AND user_id = 0"

That is to ensure it's a guest post.

 

[DRE]

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;

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? Related to: http://xenforo.com/community/threads/delete-all-posts-by-guests.56518/

 

[Jake Bunce]

...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.

 

[DRE]

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

What's the query?

 

[Jake Bunce]

To change the user_id?

UPDATE xf_post
SET user_id = 5
WHERE user_id = 0;

That will change all posts from user_id 0 to user_id 5.