SQL Query Problem in Joomla 3.x

Question

I'm quite new to SQL and PHP and tried two write an array into the database, but I couldn't figure it out so far I always get following Error:

1054 Unknown column 'Array' in 'field list' SQL=UPDATE #__typsys SET usergroup = Array WHERE userid = 687

I tried it this way:

// Get an Array with all the Usergroups the user is in
$usergroups = JFactory::getUser()->getAuthorisedGroups();

// Building the updatequery
$query = $db->getQuery(true);
$query
    ->update($db->quoteName('#__typsys'))
    ->set($db->quoteName(usergroup) . ' = ' . $db->quote($usergroups))
    ->where($db->quoteName('userid') . ' = ' . $user->id)
;
// Executing the updatequery
$db->setQuery($query);
$db->execute();

If I write a normal string like "test" in the database everything works fine, but not with an array. I have searched for hours now, but didn't find any solution. Help is much appreciated!!!

Thx in advanced!

Answer 1

This is because $usergroups returns an array and not a single value. It returns you the usergroup id which the login user belongs to. Like if the current user belongs to Registered and Administrator group it will return the ids of them as an array.

For this, you have the following option:

// Get an Array with all the Usergroups the user is in
$usergroups = JFactory::getUser()->getAuthorisedGroups(); // returns Array ( [0] => 1 [1] => 8 )
$usergroups_str = implode(',',$usergroups); // returns (1,8)

// Building the updatequery
$query = $db->getQuery(true);
$query
    ->update($db->quoteName('#__typsys'))
    ->set($db->quoteName(usergroup) . ' = ' . $db->quote($usergroups_str))
    ->where($db->quoteName('userid') . ' = ' . $user->id)
;
// Executing the updatequery
$db->setQuery($query);
$db->execute();

This will update the table row for logged in user with all his access levels. Also, I would suggest not to update the usergroup column with the usergroup name example Registered. And while fetching, you can simply explode it.

Hope this helps