By Adam S on

I'm going to use the user import module to import several users from a different system. For reasons the UID of each user has to remain the same in the new site as in the old. The user ID is used for other purposes and there are hundreds of laminated cards with each users costumer ID out there.

I already tested the user import module and it works great except I can't find a way to map the unique costumer number to the new site's UID. My plan is to import the costumer ID by creating a field in the profile table called costumer ID and map it to the costumer number of the old database. After everything is finished I will manually write a SQL query and execute it in phpMyAdmin.

Right now there is only user 1 in the database (and some test users which will be erased). I want to then change each UID to the customer ID. I opened phpMyAdmin and changed one of my test user's uid. This is the query phpMyAdmin came up with.

UPDATE `horton79_a`.`users` SET `uid` = '14' WHERE `users`.`uid` =20 LIMIT 1 ;

Within the database that is being imported both the email address and customer ID are unique. I don't know how to use that to my advantage, but I do know it is a good thing. I'm going to start by trying to write a query, but I don't know anything about SQL. The fid is the field id of the custumer ID that is being imported by the user import module. This is my first attempt at writing an sql query, ever, so I'm just guessing what it might look like. What do you think? And, how do I make it work?

UPDATE 'horton79_a'.'users' SET 'uid' = 'profile_values'.'value' WHERE ('users'.'uid' = 'profile_values'.'uid' AND 'profile_values'.'fid' = 26);

Comments

abdu’s picture

abdu commented

Use this query

UPDATE users INNER JOIN profile_values on users.uid = profile_values.uid AND profile_values.fid = 10 SET users.uid = profile_values.value

Adam S’s picture

Adam S commented

it didn't work. Here is the error.

SQL query:

UPDATE users INNER JOIN profile_values ON users.uid = profile_values.uid AND profile_values.fid =32 SET users.uid = profile_values.value

MySQL said: Documentation
#1062 - Duplicate entry '96' for key 1
Adam S’s picture

Adam S commented

Your query does work. However, I have to make sure that all the imported users have high uid than the number of the customer ID or else there are during the process two different users with the same ID while the process sorts it out.

Adam S’s picture

Adam S commented

The issue now is updating the UID in the profile_values table. Every value for uid that is 55802 needs to change to 93 the value of the row with fid = 32.

SQL result

Host: localhost
Database: horton79_a
Generation Time: Oct 22, 2009 at 03:10 AM
Generated by: phpMyAdmin 2.11.9.5 / MySQL 5.0.81-community
SQL query: SELECT `fid`, `uid`, `value` FROM `profile_values` LIMIT 0, 30 ;
Rows: 30
fid 	uid 	value
14 	55802 	USA
16 	55802 	1-954-7
22 	55802 	http:/
32 	55802 	93
33 	55802 	company
4 	55803 	Bob
5 	55803 	D
6 	55803 	No match
14 	55803 	USA
16 	55803 	1-954-76
25 	55803 	Peterso
32 	55803 	96
33 	55803 	company
4 	55804 	John
5 	55804 	Ve
6 	55804 	business management
14 	55804 	USA
16 	55804 	1-954-
17 	55804 	954
22 	55804 	http://www
25 	55804 	Super
32 	55804 	99
33 	55804 	company
4 	55805 	Deas
5 	55805 	Manning
6 	55805 	Marketing
14 	55805 	USA
16 	55805 	1-843-
22 	55805 	www.
Adam S’s picture

Adam S commented

Ok, now I made a new table in the database called profile_switch which I'm hoping can take the first UPDATE query and change the profile_values table with it. I created the table in phpMyAdmin. Here is the query:

 INSERT INTO profile_switch( uid, switch )
SELECT uid, value
FROM profile_values
WHERE fid =32

Here is the query to make the switch

UPDATE profile_values INNER JOIN profile_switch ON profile_values.uid = profile_switch.uid SET profile_values.uid = profile_switch.switch

BAM!!!! It worked....... I can't even tell you how high I am right now.