I have a custom profile setup where it stores more than just the client's name, UN. and PW and need to transfer this data to the CB table. I understand that this probably would entail writing a custom SQL command... but I am not well versed in SQL commands. I've searched the internet and this is what I've hacked together...
I need to map a single name field into first, middle, and last name columns first. I have started to require middle names so not everyone has 3 names in their profile so I'm not sure how to modify the function below to account for that.
Code:
// Create a function to "explode" the single name field
CREATE FUNCTION SPLIT_STRING(str VARCHAR(255), delim VARCHAR(12), pos INT)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, pos),
LENGTH(SUBSTRING_INDEX(str, delim, pos-1)) + 1),
delim, '');
// Process the first name
INSERT INTO `iiid_comprofiler` (`firstname`)
SELECT `iiid_users` SPLIT_STRING(`name`, ',', 1);
// Process the middle name
INSERT INTO `iiid_comprofiler` (`middlename`)
SELECT `iiid_users` SPLIT_STRING(`name`, ',', 2);
// Process the last name
INSERT INTO `iiid_comprofiler` (`lastname`)
SELECT `iiid_users` SPLIT_STRING(`name`, ',', 3);
//Process other profile info
INSERT INTO `iiid_comprofiler` (`cb_address`, `cb_address2`, `city`, `state`, `zip`, `phone`)
SELECT `iiid_user_profiles` (`address1`, `address2`, `city`, `state`, `zip_code`, `phone`);
My other problem is that the "other profile info" above wasn't stored correctly in the table. Instead of each field being in a column they are each in their own row. See attached screenshot.
krileon
