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

Query for vBulletin

Discussion in 'XenForo Questions and Support' started by fredrikse, Dec 2, 2012.

  1. fredrikse

    fredrikse Active Member


    Before I migrate my vBulletin forum to Xenforo I want to go trough and remove any duplicate accounts in my database where the mail addresses are the same. I know there are a few from my test imports to Xenforo.

    Anyone that can assist me with a SQL query that shows all members that have more than one account (where the mail address is the same) and also how many posts each of these accounts have? After I have identified these accounts I'm going to remove the oldest accounts that are duplicates and don't have any posts.
  2. Morgain

    Morgain Well-Known Member

    Suggest you contact some people privately on this as it's not strictly XF support.
    See top right Inbox.

    Try waindigo / ScmitzIT/ Jake Bunce/ Slavik for starters. Ask if they are too busy can they suggest anyone else who might help by giving you the queries to use.
    Or ask on VB how to clean up your member base You dont have to say it's so you can leave!
    Jake Bunce likes this.
  3. melbo

    melbo Well-Known Member

    The XF to VB import will identify these duplicate accounts at the Import Users step and ask you what you'd like to do with each one: delete one, merge them into one or change the email address(s)

    I'd strongly suggest some test imports before you inport 'for real' and while performing these tests, you'll see the above mentioned step of the vB import.
    Jake Bunce likes this.
  4. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Merging duplicate users in vB has to be done manually in the Admin CP. There isn't a simple query for this. If there are a lot of duplicate users then I suggest letting the importer automatically merge accounts by email address. Users can login using their email address.
  5. Brandon Sheley

    Brandon Sheley Well-Known Member

    Why not do a fresh import on a clean install?
  6. fredrikse

    fredrikse Active Member

    I know the XF import will find duplicate accounts in the vBulletin database. I have also done several test imports to see how how duplicate accounts are handled. The reason why I want to remove any duplicate accounts (without any posts) already in the vBulletin database is just to make thing easier and control that correct usernames (of duplicate accounts) will be visible on the Xenforo platform.

    I will try myself and see if I can come up with a query that works to fetch this information :)
  7. fredrikse

    fredrikse Active Member

    This worked just fine:

    Does anyone know if it's possible to decode joindate and lastactivity right in the SQL query? Now it looks like "1203094920".
    Jake Bunce likes this.
  8. fredrikse

    fredrikse Active Member

    Made a small correction. Now the SQL will sort the activity field correctly and I can see the most recent accounts and vice versa.

    AndyB, thumped and Jake Bunce like this.
  9. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    fredrikse likes this.
  10. fredrikse

    fredrikse Active Member

    Google helped me out with this one! Finally I got my list the way I wanted it. Did the reason why I want to do this make any sense to you now? :) At the very beginning I ran this forum on a platform I had developed myself. Don't ask me why that was possible but somehow people could register new accounts with the same e-mail address. That's why I want to clean this out now.

Share This Page