Set / Change user attributes via REST API

šŸ’» Development
1

Hi,

I got introduced to the REST API a few days ago and despite some initial resistance from my part Iā€™ve grown to like it. Itā€™s working well, but as new records are being created I miss that I cannot provide some extra custom values to user attributes (such as a Name etc.)

So I looked into /restapi/includes/subscribers.php and added a function:

public static function subscriberUpdateCustomField()
{
    $sql = 'UPDATE phplist_user_user_attribute SET value=:value WHERE userid=:userid AND attributeid=:attributeid;';
    
    $userid = sprintf('%d',$_REQUEST['userid']);
    $attributeid = sprintf('%d',$_REQUEST['attributeid']);
    if (empty($userid) || empty($attributeid)) {
        Response::outputErrorMessage('invalid call');
    }
    try {
        $db = PDO::getConnection();
        $stmt = $db->prepare($sql);
        $stmt->bindParam('userid', $userid, PDO::PARAM_INT);
        $stmt->bindParam('attributeid', $attributeid, PDO::PARAM_INT);
        $stmt->bindParam('value', $_REQUEST['value'], PDO::PARAM_STR);
        $stmt->execute();
        $db = null;
        self::SubscriberGet($userid);
    } catch (\Exception $e) {
        Response::outputError($e);
    }
}

This works well for records that already have one or more user attributes (e.g. imported via the browser interface).
If that is the case I can change any user attribute with ease.

However, for newly added records, e.g. via the API, that hence have no user attributes yet, I cannot change the user attributes. Even though the function always returns ā€˜successā€™ in this case, inspecting the record via the browser shows the fields are still empty.

I donā€™t know enough about mySQL and this db to know why, I assume the user attributes are a sub record of some sort (?) that needs to be created first as well ? Again, not a database expert at all.

If somebody could put me back on the rails for this last hurdle, that would be great !
Thanks !

1 Like
2

@ibPeter You probably need to use either ā€œINSERT ā€¦ON DUPLICATE KEYā€ or ā€œREPLACEā€.

3

Thank you @duncanc
However, there is an added level of complexity that Iā€™m unsure about on how to handle.
I mean, I could INSERT a userid in the table, and UPDATE it in case itā€™s a DUPLICATE, but the userid contains several attributeidā€™s with value as well, so how do I update only one attributeid this way ?

I could try something like:

$sql = ā€˜INSERT INTO phplist_user_user_attribute (userid) VALUES (:userid) ON DUPLICATE KEY UPDATE attributeid=:attributeid;ā€™;

but that doesnā€™t feel right nor does it update the value
Can I combine WHERE in this statement ?
I canā€™t seem to figure this one out (yet)

4

@ibPeter You want to insert user id/attribute id/attribute value. If the key (user id/attribute id) already exists then update the attribute value.

5

Thank you @duncanc
How would you write that line then ?
Iā€™m still not sure how to pair the userid / attributeid on a command line like this ?

EDIT. Nevermind, I believe I understand it now !
Testing underway for:

$sql = ā€˜INSERT INTO phplist_user_user_attribute (userid,attributeid,value) VALUES (:userid,:attributeid,:value) ON DUPLICATE KEY UPDATE value=:value;ā€™;

and it appears to be working great !

6

For completeness and possibly to help others, here is the function that should be added to subscribers.php

public static function subscriberUpdateUserAttribute()
{
    $sql = 'INSERT INTO phplist_user_user_attribute (userid,attributeid,value) VALUES (:userid,:attributeid,:value) ON DUPLICATE KEY UPDATE value=:value;';

    $userid = sprintf('%d',$_REQUEST['userid']);
    $attributeid = sprintf('%d',$_REQUEST['attributeid']);
    if (empty($userid) || empty($attributeid)) {
        Response::outputErrorMessage('invalid call');
    }
    try {
        $db = PDO::getConnection();
        $stmt = $db->prepare($sql);
        $stmt->bindParam('userid', $userid, PDO::PARAM_INT);
        $stmt->bindParam('attributeid', $attributeid, PDO::PARAM_INT);
        $stmt->bindParam('value', $_REQUEST['value'], PDO::PARAM_STR);
        $stmt->execute();
        $db = null;
        self::SubscriberGet($userid);
    } catch (\Exception $e) {
        Response::outputError($e);
    }
}

And here is how you use it (function in your own class / php code) :

public function UpdateSubscriberAttribute($subscriber_id, $attributeid, $value)
{

    $post_params = array(
        'userid'           => $subscriber_id,
        'attributeid'     => $attributeid,
        'value'         => $value,
    );

    // subscriberUpdateUserAttribute requires custom code added to %phplist%/admin/plugins/restapi/includes/subscribers.php
    $result = $this->callAPI('subscriberUpdateUserAttribute', $post_params);

    return $result;
}

And hereā€™s how you use latter function:
PS. you can find the attribute idā€™s when you logon to your phplist and navigate to Config / Configure attributes

In below example, user attribute 1 contains the userā€™s name:

$phpList->UpdateSubscriberAttribute($subscriber_id, 1, ā€˜Personā€™s nameā€™) ;

1 Like