Updating user fields in Drupal 7

Question

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?

Answer 1

So Simple. just paste it

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

Answer 2

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.