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

XF 1.2 Delete All Posts By Guests

Discussion in 'XenForo Questions and Support' started by DRE, Aug 4, 2013.

  1. DRE

    DRE Well-Known Member

    How do you delete all posts by guests?
     
  2. DRE

    DRE Well-Known Member

  3. DRE

    DRE Well-Known Member

    My question is related to: http://xenforo.com/community/threads/xenforo-1-2-to-xenforo-1-2-importer-error.56494/
    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?
     
  4. DRE

    DRE Well-Known Member

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

    Got this from another thread:
    Code:
    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.

    Code:
    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.
     
  5. DRE

    DRE Well-Known Member

    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/

    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?
     
  6. DRE

    DRE Well-Known Member

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

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

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

    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:

    Code:
    UPDATE xf_conversation_user
    SET owner_user_id = 1688
    WHERE owner_user_id = 0;
    
    and
    Code:
    UPDATE xf_conversation_user
    SET last_message_user_id = 1688
    WHERE last_message_user_id = 0;
    
    K I'm getting the hang of this.
     
    Last edited: Aug 4, 2013
  7. DRE

    DRE Well-Known Member

    Even if you try to run this query:
    Code:
    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.
     
  8. DRE

    DRE Well-Known Member

    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?
     
    Last edited: Aug 4, 2013
  9. DRE

    DRE Well-Known Member

    Last edited: Aug 7, 2013

Share This Page