1

I have created several custom fields for my Drupal 7 users, which I imported from an external users table.

I am now trying to automate the population of these custom fields using a custom module, however my code is not working. The following code is what I have been working with:

<?php

function get_sage_fields($username) {

  // Get the extra fields for Sage users
  $sage_fields = db_query('SELECT OFFICE_ID, TRADE_STATUS, DESIGNATION,
    FIRST_NAME, LAST_NAME, PHONE_NUMBER, MAILING_ADDRESS, ACCOUNT_PREFIX,
    ALLOW_TO_TRADE FROM USER WHERE USER_ID = $username');
  return $sage_fields;
}

function sage_fields_user() {

  // Grab all usernames from the users table
  $usernames = db_query('SELECT name FROM users')->fetchCol();

  // Load the users
  $users = user_load_multiple($usernames);

  // Loop through and edit each user
  foreach($users as $user) {

    // Load the extra data
    $extra_data = get_sage_fields($user->name);

    // Update user fields
    $user->field_office_id[$user->language][0]['value'] = $extra_data['office_id'];
    $user->field_trade_status[$user->language][0]['value'] = $extra_data['trade_status'];
    $user->field_designation[$user->language][0]['value'] = $extra_data['designation'];
    $user->field_first_name[$user->language][0]['value'] = $extra_data['first_name'];
    $user->field_last_name[$user->language][0]['value'] = $extra_data['last_name'];
    $user->field_phone_number[$user->language][0]['value'] = $extra_data['phone_number'];
    $user->field_mailing_address[$user->language][0]['value'] = $extra_data['mailing_address'];
    $user->field_allow_to_trade[$user->language][0]['value'] = $extra_data['allow_to_trade'];

    // Save the user object
    user_save($user);
  }
}

I enabled my custom module under admin/modules and included it in my .info file with:

files[] = sage_fields.module

However, if I go to field_revision_[FIELD_NAME] in my D7 database, I do not see these fields populated.

Could someone please point out where I have gone wrong?

2 Answers 2

5

So Simple. just paste it

$user_fields = user_load($user->uid);    
$user_fields->field_points['und'][0]['value'] = $points;
user_save($user_fields);
1

There's a couple of things...

Firstly the argument you're passing to db_query() ($username) is a string so needs to be wrapped in quotes in the query. The database layer actually allows you to pass in parameters, which is safer (as it protects against SQL injection), and means you don't need to worry about this:

$args = array(':username' => $username);
$query = db_query('SELECT OFFICE_ID, TRADE_STATUS, DESIGNATION,
          FIRST_NAME, LAST_NAME, PHONE_NUMBER, MAILING_ADDRESS, ACCOUNT_PREFIX,
          ALLOW_TO_TRADE FROM USER WHERE USER_ID = :username', $args);

The second thing is that you need to actually convert the result from db_query() to something useable in the calling function. In your case you want a single object from the query, so you would user the fetchObject() method:

return $query->fetchObject();

If you change your get_sage_fields() function to look more like the above code you'll probably have a bit better luck :)

EDIT

Just to clarify, the finished code would look something like this:

function sage_fields_menu() {
  $items['user-data-import'] = array(
    'title' => 'User Data Import',
    'access arguments' => array('access administration pages'), // Or whatever permission you want to use
    'page callback' => 'sage_fields_import_user_data',
    'type' => MENU_CALLBACK
  );

  return $items;
}

function get_sage_fields($username) {
  // Get the extra fields for Sage users
  $args = array(':username' => $username);
  $sage_fields = db_query('SELECT OFFICE_ID, TRADE_STATUS, DESIGNATION,
    FIRST_NAME, LAST_NAME, PHONE_NUMBER, MAILING_ADDRESS, ACCOUNT_PREFIX,
    ALLOW_TO_TRADE FROM USER WHERE USER_ID = :username', $args);
  return $sage_fields->fetchObject();
}

// Don't call this function sage_fields_user as that will be an implementation
// of hook_user() and will cause you problems.
function sage_fields_import_user_data() {
  // Set 

  // Grab all user ids from the users table
  // You need the ids to load the user objects in user_load_multiple
  $uids = db_query('SELECT uid FROM users')->fetchCol();

  // Load the users
  $users = user_load_multiple($uids);

  // Loop through and edit each user
  foreach($users as $user) {

    // Load the extra data
    $extra_data = get_sage_fields($user->name);

    // Update user fields
    $user->field_office_id[$user->language][0]['value'] = $extra_data['office_id'];
    $user->field_trade_status[$user->language][0]['value'] = $extra_data['trade_status'];
    $user->field_designation[$user->language][0]['value'] = $extra_data['designation'];
    $user->field_first_name[$user->language][0]['value'] = $extra_data['first_name'];
    $user->field_last_name[$user->language][0]['value'] = $extra_data['last_name'];
    $user->field_phone_number[$user->language][0]['value'] = $extra_data['phone_number'];
    $user->field_mailing_address[$user->language][0]['value'] = $extra_data['mailing_address'];
    $user->field_allow_to_trade[$user->language][0]['value'] = $extra_data['allow_to_trade'];

    // Save the user object
    user_save($user);
  }

  return 'Done';
}

I've added a menu hook after you've cleared Drupal's cache you just need to go to http://mysite.com/user-data-import and it will start the process. The above code is based on your module being called sage_fields, you'll need to change that in the hook_menu() function if it's called something else.

As I mentioned in your other question running this for 8500+ users in one go will probably time out the server; you'll probably want to run this import in batches.

6
  • Thanks Clive. A bit better, now it's complaining about my SQL queries.
    – chlong
    Mar 23, 2012 at 19:53
  • Clive, I've made the changes you suggest and also changed $query = db_query('SELECT name FROM users'); $usernames = $query->fetchObject(); in my sage_fields_user() function, and it is not doing the population. Do I need to manually start the process or will Drupal do this for me?
    – chlong
    Mar 23, 2012 at 20:14
  • @chlong I've updated the answer with a more info, hope it helps
    – Clive
    Mar 23, 2012 at 20:37
  • I read up on user_load_multiple and realized that it only takes an array of UserIDs. My external database USER table does not contain numerical User IDs but only the Usernames (ie. In the external table, UserID = Username). Could this be why it is not working?
    – chlong
    Mar 26, 2012 at 14:37
  • 1
    AFAIK the user_save function takes 2 arguments in case of an user update: first is the original user object to modify and second is an array of fields / data you want to update. There also is a TODO in the function's metadata / comments, to drop this behavior and make it work like node_save (which behaves just like your example). Feb 13, 2013 at 14:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.