Query for vBulletin

[fredrikse]

Hi,

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.

 

[Morgain]

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!

 

[melbo]

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]

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.

 

[Brandon Sheley]

Why not do a fresh import on a clean install?

 

[fredrikse]

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

 

[fredrikse]

This worked just fine:

SELECT username, email, joindate, lastactivity, posts FROM user WHERE email IN
(SELECT email FROM user GROUP BY email HAVING ( COUNT( email ) > 1 )) ORDER BY email DESC, posts ASC

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

 

[fredrikse]

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

SELECT username, email, joindate, lastactivity, posts FROM user WHERE email IN
(SELECT email FROM user GROUP BY email HAVING ( COUNT( email ) > 1 )) ORDER BY email ASC, posts ASC, lastactivity ASC

 

[Jake Bunce]

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

You need to use FROM_UNIXTIME(). Examples here:

http://xenforo.com/community/threads/new-users-posts-threads-reports-queries.15508/

 

[fredrikse]

You need to use FROM_UNIXTIME(). Examples here:

http://xenforo.com/community/threads/new-users-posts-threads-reports-queries.15508/

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.