1. Forums
  3. Support and Presales
  5. Developer Members Support
  7. custom user sync

custom user sync

9 years 1 month ago - 9 years 1 month ago #260892 by bradymc
custom user sync was created by bradymc
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.
// 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.

Please Log in to join the conversation.

9 years 1 month ago #260896 by krileon
Replied by krileon on topic custom user sync
Please understand we do not provide coding assistance. Please see my signature regarding this. Specialized requests typically will be ignored; especially repeat requests.

You're doing insert queries instead of update queries, which is why you're getting new rows. Your _user_profiles usage is the Joomla profile data table and it's not structured like that so that query shouldn't work at all. _user_profiles is structured with a profile_key and profile_value where each field has its own table row.

Please see the below examples based off your above information. Please note the below is untested and provided as an example. Please see the MYSQL documentation and tutorial links below for further SQL usage information.

Update Name:
UPDATE cb
SET cb.`firstname` = SPLIT_STRING( j.`name`, ',', 1 ), cb.`middlename` = SPLIT_STRING( j.`name`, ',', 2 ), cb.`lastname` = SPLIT_STRING( j.`name`, ',', 3 )
FROM `iiid_comprofiler` AS cb
INNER JOIN `iiid_users` AS j
ON j.`id` = cb.`id`

Move Address Field:
UPDATE cb
SET cb.`cb_address` = j.`profile_value`
FROM `iiid_comprofiler` AS cb
INNER JOIN `iiid_user_profiles` AS j
ON j.`user_id` = cb.`id`
WHERE j.`profile_key` = 'address1'

dev.mysql.com/doc/refman/5.6/en/index.html
www.w3schools.com/sql/default.asp

Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

Moderators: beat, nant, krileon
  1. Forums
  3. Support and Presales
  5. Developer Members Support
  7. custom user sync
Time to create page: 0.245 seconds

Search

Login / Logout

User Menu