1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Deleted a user but the name still shows up

Discussion in 'Troubleshooting and Problems' started by Jeffin, Aug 27, 2012.

  1. Jeffin

    Jeffin Well-Known Member

    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.
     
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

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

    Code:
    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:

    Code:
    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);
    
     
    upnet and Jeffin like this.
  3. jgas

    jgas Active Member

    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!!
     
  4. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

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

    jgas Active Member

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

    Jake Bunce XenForo Moderator Staff Member

    That is to ensure it's a guest post.
     
  7. DRE

    DRE Well-Known Member

    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/
     
  8. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

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

    DRE Well-Known Member

    What's the query?
     
  10. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    To change the user_id?

    Code:
    UPDATE xf_post
    SET user_id = 5
    WHERE user_id = 0;
    
    That will change all posts from user_id 0 to user_id 5.
     
    DRE likes this.

Share This Page