Joomla 3.7.x PHP API set value of custom user field

Question

I'm writing a PHP script that goes through the users, and reads and writes some custom user fields.

The reading part is done, by the use of the FieldsHelper. It works nicely. However, trying to write back some data into these custom user fields seems trickier...

I tried multiple ways of writing into the fields but none of them worked. It showed a 404 error page...

<?php
define('_JEXEC', 1);
if (file_exists(__DIR__ . '/defines.php')) {
    include_once __DIR__ . '/defines.php';
}
if (!defined('_JDEFINES')) {
    define('JPATH_BASE', __DIR__);
    require_once JPATH_BASE . '/includes/defines.php';
}
require_once JPATH_BASE . '/includes/framework.php';

// Load the fields helper
JLoader::register('FieldsHelper', JPATH_ADMINISTRATOR . '/components/com_fields/helpers/fields.php');

// Instantiate the application.
$app = JFactory::getApplication('site');
jimport('joomla.plugin.helper');
// JFactory
require_once (JPATH_BASE .'/libraries/joomla/factory.php');

// Read & write custom fields
ReadWriteCustomFields();

function ReadWriteCustomFields() {
    // query users
    $db = JFactory::getDBO();
    $query = "SELECT id FROM #__users" ;
    $db->setQuery($query);

    $rows = $db->loadObjectList();

    $model = JModelLegacy::getInstance('Field', 'FieldsModel', array('ignore_request' => true));

    //run through users
    foreach ($rows as $row) {
        //get the user object
        $user = JUser::getInstance($row->id);
        //get custom fields
        $customFields = FieldsHelper::getFields('com_users.user', $user, true);

        if($customFields[1]->value == "") {
            // write into custom field
            //$customFields[1]->value = "TRIED THIS";
            $model->setFieldValue($customFields[1]->id, $user->id, "AND ALSO THIS");
            $model->setFieldValue(1, $user->id, "AND ALSO THIS");
        }
    }
}
?>

Answer 1

Don't mess directly with the table! Use the field model which has a setValue function. It allows you to write the value correctly into the database as done in the system plugin.

Answer 2

I work with them by reading and writing straight to the table. There are two - #__fields and #__fields_values. The former is for looking fields up by their alias, the latter is for querying and setting values.

To retrieve an ID for a user field, the following will do:

function GetFieldID($Alias)
{
    $db = JFactory::getDBO();
    $db->setQuery($db->getQuery(true)
        ->select('id')
        ->from('#__fields')
        ->where("context = 'com_users.user'")->where("name='".$Alias."'"));
    return $db->loadResult();
}

To query a field, this:

function GetField($UserID, $FieldID)
{
    $db = JFactory::getDBO();
    $db->setQuery($db->getQuery(true)
        ->select('value')
        ->from('#__fields_values')
        ->where("field_id = $FieldID")->where("item_id=$UserID"));
    return $db->loadResult();
}

And so forth. Putting together an insert and an update would be equally trivial.

If there's a good high level API for doing the same, I have yet to find it.