Matching new users with imported messages

[dkendall]

I've looked around and don't see this covered anywhere, so here goes.

I've imported messages from a system that didn't require user registration.

Now, when users register on vBulletin, their old messages aren't associated with their new profile.

Is it possible to search all the messages after a user registers, and associate old messages by e-mail address?

Has anyone dealt with this?

David

[Jerry]

Which system did you import from and do the old posts have usernames or email addresses for the username of the post ?

[dkendall]

I imported from Phorum 3.4.8, and the posts show the users' names in vB.

In Phorum, the posts have both e-mail addresses and "display" names associated with them.

The post table has only the display names in the username column.

I guess I'd have to add a column, tweak the import code, and re-run the import to enable something like this. :(

OTOH, post does have the importpostid column, so it might be possible to write some code that would fetch the e-mail address from the old database.

David

[Jerry]

If the display names are constant with the new username you can use SQL to tie it all together, though you would have to do that each time a new user account was created.

[dkendall]

The display names are not unique (e.g., "David") so that wouldn't be reliable.

FWIW, I think that modifying the import to store an "import username" in the posts table would be a good idea. In the case of a Phorum import, it could be the e-mail address. For other source forums, it could be a unique username.

Then we could periodically run a task to match up newly registered users with old e-mail addresses.

David

[Jerry]

In my experience, email is the best way to go as that's more unique to a person that a name.

You could alter the post table, then either put in some custom code for each post import or update ImpEx to add the email as a normal field with $try-> etc

[dkendall]

Here's what I did, in case anyone wants to try something like this in future:

1) Added 'importemail' columns to the 'thread' and 'post' tables.

ALTER TABLE thread ADD importemail char(100) DEFAULT '' NOT NULL;
ALTER TABLE post ADD importemail char(100) DEFAULT '' NOT NULL;

2) Added these columns to the 'vbfields' table.

INSERT INTO vbfields
VALUES ('importemail', 'thread', 'N', '!##NULL##!', 'return true;')
INSERT INTO vbfields
VALUES ('importemail', 'post', 'N', '!##NULL##!', 'return true;')

3) Added this to 005.php (after all the other set_value calls):

$try->set_value('nonmandatory', 'importemail', $thread_details['email']);

4) Added this to 006.php:

$try->set_value('nonmandatory', 'importemail', $post_details['email']);

5) Modified import_post and import_thread in ImpExDatabase_350.php to store the new columns. For example:


$sql = "
INSERT INTO " . $tableprefix . "post
(
threadid, userid, importthreadid,
...
visible, attach, importpostid, importemail
)
VALUES
(
'" . $this->get_value('mandatory', 'threadid') . "',
...
'" . $this->get_value('nonmandatory', 'importpostid') . "',
'" . addslashes($this->get_value('nonmandatory', 'importemail')) . "'
)
";

6) Re-run the thread and post imports using ImpEx.

7) Run lots of SQL updates (such as the following) to assocate threads/posts from selected users with their vBulletin user profiles:

UPDATE thread SET postuserid=9 WHERE postuserid=0
AND importemail='someone@anydomain.com';

UPDATE post SET userid=9 WHERE userid=0
AND importemail='someone@anydomain.com';

8) Do the "end game" stuff from the import FAQ, plus run Update Post Counts to fix up the tables.

9) Wait a while. (I'm not sure if there's a scheduled task that kicks in and fixes things up, or if things were cached, but it took a few minutes for things to look right.)

David