Add extended fields to multiple existing user accounts

24 Posts

andrewhl Reply #1, 12 years, 10 months ago

Is there a way to bulk create extended (custom) fields for existing users?

I’m already using the Register snippet to create various custom fields on signup; now, however, I need to go back and add a number of new fields to the existing users. There are far too many users to do this manually, i.e., through the MODx Manager.

Any suggestions?

5 Posts

DutchIris Reply #2, 12 years, 10 months ago

Do you just want to create the fields with empty values, so users can fill in the values themselves? In that case you can just add the fields to your updateprofile chunk and they will be saved automatically.

Or do you want to create the field and save the value at the same time? Then you might hack the database directly.

The extended fields are in a column called ’extended’ in the table [yourprefix]_user_attributes. Extended fields are saved in one field in a array-like format, like so:

{"firstname":"Bas","lastname":"Zijlstra"}

You already have extended fields, so you need to update the values rather than overwrite them. Do something like:

update [yourprefix]_user_attributes set extended = concat(left([yourprefix]_user_attributes.extended, length([yourprefix]_user_attributes.extended) - 1), ',"foo":"bar"', '}') where id = [n]

There may be a simpler way. Also, I’d recommend that you back up your data first!

24,544 Posts

BobRay Reply #3, 12 years, 10 months ago

Do you want to add the extended field for all users, or just some?

Do you want to set a value for any of the new fields, and if so, will it be the same for each user our not?

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

24 Posts

andrewhl Reply #4, 12 years, 9 months ago

Thanks for the responses!

The fields I am adding are for administrative purposes and need to be applied to user accounts, but can’t be visible/editable by them. They’re basically hidden boolean fields set to true/false by administrators for each user.

The fields should not contain any values when set initially. I could add them to the update profile chunk, but I don’t want them visible to users (who /are/ permitted to update other fields in their profile).

I need to add them to all users. I’ve already added some manually, I just need to finish the rest. I could write a SQL query to do this, but if there’s an easier way, I’d love to hear it.

Thanks!

24 Posts

andrewhl Reply #5, 12 years, 9 months ago

Okay, I just ran a SQL query. It worked like a charm. Thanks!

24,544 Posts

BobRay Reply #6, 12 years, 9 months ago

The extended fields are stored as a JSON string, so hopefully that’s what you used in your SQL query.

MODX will convert them for you if you put them there with xPDO, but a query would need them in the proper format.

The snippet version would look something like this:

<?php
$users = $modx->getCollection('modUser', array ('active' => 1));

foreach ($users as $user) {

    $profile = $user->getOne('Profile');

    $extended = array();

    $extended = $profile->get('extended');

    $extended[] 'field1' => 'value1';
    $extended[] 'field2' => 'value2';

    $profile->set('extended', $extended);
    $profile->save();
}

The second argument to getCollection() is optional. Leave it out if you want to set the fields for all users, active or not.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

☆ A M B ☆
24,524 Posts

sottwell Reply #7, 9 years, 2 months ago

This post is old, but the topic recurs.

How to set extended fields with containers? This doesn't work...

    $extended = $profile->get('extended');
    $extended['subscriptions'][$product_code] = $date;
    $profile->set('extended', $extended);
    $profile->save();

I dunno. After fussing with it for a while, the exact same code now works. [ed. note: sottwell last edited this post 9 years, 2 months ago.]

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org