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

Change user ID for two members

Discussion in 'XenForo Questions and Support' started by polle, Jul 30, 2012.

  1. polle

    polle Active Member

    I need to switch user IDs for 2 forum members.

    My regular user with all my posts is ID 3 and I want that to be ID 1.

    The user under ID 1 will become ID 3, so what I need is to switch the member ID for those 2 users.

    How can I do it without messing everything and without loosing all the posts from the users ?

  2. Chris D

    Chris D XenForo Developer Staff Member

    This is tricky.

    All of your posts are under ID 3, so even if you swapped your user account to ID 1 and ID 1 to ID 3, your posts would all still be under ID 3.

    What's your reason for wanting to do this?

    It may be possible with a database query to update all your posts to the right ID as well, but changing IDs is potentially very dodgy. There's probably a hell of a lot of tables that reference your activity under ID 3.

    I'd advise against it unless there's a major reason.
  3. polle

    polle Active Member

    I am bridging with other systems and those other systems have ID 1 for the main user, thats me.

    So as the other systems are using XF credentials, all the posts there shows the incorrect author. The other system has 4000 posts showing as an author XF user ID 1, of course I need to have those 4000 posts to have my name and I need to be ID 1 in XF to fix that.

    Thats why I need to switch the IDs, to have everything under my name and be ID 1 in all systems, in this case WordPress is the other one.

    Maybe its easier to switch user IDs in Wordpress ?

  4. Chris D

    Chris D XenForo Developer Staff Member

    I imagine it would be easier in WordPress, but I'm not 100% sure.

    It's just there's so many tables in XenForo where your user ID is used to store data about you.

    Trophies, posts, threads, likes, polls just to name a few off the top of my head.
  5. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Changing a user's user_id is not recommended. It's probably easier to change the ids on the posts instead. I don't have the WP schema in front of me, but normally a query like this will do:

    UPDATE name_of_post_table
    SET userid = 3
    WHERE userid = 1
    Chris D likes this.
  6. polle

    polle Active Member

    Well I did this in WP database in case someone else need this.

    In phpMyAdmin select wp_users table.
    Click edit in a user and change the ID to match the forum ID. Save.
    Then a SQL query:
    UPDATE `wp_posts` SET `post_author` = 'USER_XENFORO_ID' WHERE `post_author` = 'USER_WORDPRESS_ID'
    I am not sure if I missed something and if something else is needed, like for comments or something else, but for now it seems to be working correct.

    Cool :)

    Chris D likes this.
  7. Chris D

    Chris D XenForo Developer Staff Member

    Nice work :)
  8. RichardKYA

    RichardKYA Well-Known Member

    Hello guys,

    I don't know much about databases or queries, but a recent event has sparked my interest, so I am trying to learn more about them. With that in mind, I am asking this question purely for my own understanding and because I would like to test this on my own xenforo databases, I would like to ask here first before I do anything unrepairable. At the moment my site only has test accounts, so I'm not worried about causing any damage to real user accounts.

    My question is: If I ran a query such as Jake's example,

    but instead, ran it on my entire xenforo database

    UPDATE my_xenforo_database_name SET userid = '2' WHERE userid = '8'

    would that update all the table rows within the database?

    Or if I were to run

    UPDATE my_xenforo_database_name SET username = 'Richard' WHERE username = 'John'

    These are just examples, but I am just trying to understand the effect it would have if I ran them on the whole database as oppose to running them on one selected table or is it only possible to run queries on individual tables?

    Thank you for any input

  9. Brogan

    Brogan XenForo Moderator Staff Member

    You can't run queries against an entire database.
    They have to be explicitly targeted at a table/column.
  10. RichardKYA

    RichardKYA Well-Known Member

    Yeah, I thought not. I read what Chris said again after posting my question
    and thought he wouldn't have said that if it were possible to do it all in one hit.

    I've just started a video course in SQL, but sometimes it just easier to ask someone that knows lol

    Thank you for your reply
    Last edited: Mar 5, 2015

Share This Page