Clever Geek Handbook

Filter by user group in RSform

I am using RSform in Joomla to create a data entry form. The form has a dropdown list to select a user and this field gets their data using the following code in the form.

What I would like to do is show only the usernames in a specific user group. The actual users are stored in a table #__users

with a unique identifier named id

, groups are stored in the table #__usergroups

, and the unique identifier is also named id

. I want to be able to filter the list to show only those users in the group 2

.

Can anyone please help?

Thank you in advance

// Prepare the empty array
$items = array();
// Prepare the database connection
$db = JFactory::getDbo();
// Keep this if you'd like a "Please select" option, otherwise comment or remove it
$items[] = "|Please Select[c]";

// Run the SQL query and store it in $results
$db->setQuery("SELECT id, name, email FROM #__users");
$results = $db->loadObjectList();

// Now, we need to convert the results into a readable RSForm! Pro format.
// The Items field will accept values in this format:
// value-to-be-stored|value-to-be-shown
// Eg. m|M-sized T-shirt
foreach ($results as $result) {
  $value = $result->name;
  $label = $result->name;
  $items[] = $value.'|'.$label;
}

// Multiple values are separated by new lines, so we need to do this now
$items = implode("\n", $items);

// Now we need to return the value to the field
return $items;
+3


source to share


2 answers


You will need to map users to the table usergroup_map

like this:

$groupID = 2;
$db = JFactory::getDbo();

$query = $db->getQuery(true);  
$query->select('a.*')
      ->from($db->quoteName('#__users') . ' AS a')
      ->join('LEFT', $db->quoteName('#__user_usergroup_map') . 'AS map2 ON map2.user_id = a.id')
      ->group($db->quoteName(array('a.id', 'a.username')))
      ->where('map2.group_id = ' . (int) $groupID);   
$db->setQuery($query);
$results = $db->loadObjectList();

foreach($results as $result) {
    echo $results->username;
}

This will just display all of the username of the user owned $groupID

, which is currently set to 2 , and you can change it accordingly.



So your final and complete code will be as follows:

$items = array();
$groupID = 2;
$db = JFactory::getDbo();

$query = $db->getQuery(true);  
$query->select('a.*')
      ->from($db->quoteName('#__users') . ' AS a')
      ->join('LEFT', $db->quoteName('#__user_usergroup_map') . 'AS map2 ON map2.user_id = a.id')
      ->group($db->quoteName(array('a.id', 'a.username')))
      ->where('map2.group_id = ' . (int) $groupID);    
$db->setQuery($query);
$results = $db->loadObjectList();

$items[] = "|Please Select[c]";

foreach ($results as $result) {
    $value = $result->username;
    $label = $result->username;
    $items[] = $value.'|'.$label;
}

$items = implode("\n", $items);

return $items;
0


source


The mapping of the user to user_group is stored in a table #__user_usergroup_map

.

So, to get all user IDs that exist in a group, you must run the following:



$db = JFactory::getDbo();
$query = $db->getQuery(true);

$user_group_id = 8; // user group id

$query->select($db->quoteName(array('user_id')));
$query->from($db->quoteName('#__user_usergroup_map'));
$query->where($db->quoteName('group_id') . ' = ' . $user_group_id );
$db->setQuery($query);

$results = $db->loadObjectList();

Hope it helps

0


source






More articles:


All Articles