How to Create a Foreach Loop that Returns Users In a Specific Usergroup

Question

My goal is to create an html select box via a foreach loop that returns all users within a specific custom usergroup (ex. usergroup = Agents).

Once, the query pulls in all records (users) for that specific usergroup, I then need to pull in data from multiple tables for each user, such as: #__users and #__user_profiles – as I’m wanting to grab #__user information such as: name and email, while also grabbing #__user_profile information such as: address1, city, postal_code, etc….

Any help would be appreciate….

Pseudo Code For What I Think I'm Needing

SELECT *
  FROM #__users AND #__user_profiles
  WHERE #__usergroup.title = Agent
  ORDER BY #__users.name ASC;

<select>
FOREACH user that has a #__usergroup.title = Agent
  <option>
  OUTPUT (
    #__users.name,
    #__users.email
    #__users.usertype,
    #__user_profiles.address1,
    #__user_profiles.city,
    #__user_profiles.postal_code,
    #__user_profiles.phone,
    #__user_profiles.website
  );
  </option>
END FOREACH
</select>

Answer 1

To answer the actual question, this code will give you a listbox of UserIDs and Names. Retrieving all the extra data would then just be a SQL select statement

$myGroupID = 2;  //Your User Group to list
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('a.id as value, a.name as text');
$query->from($db->quoteName('#__users').' as a');
$query->join('INNER', '#__user_usergroup_map as b ON b.user_id=a.id AND b.group_id='.$myGroupID);
$query->order('a.name');
$db->setQuery($query);
$MyUsers = $db->loadObjectList();
$mt = new stdClass();
$mt->value = '';
$mt->text = 'Select the User Profile';
array_unshift($MyUsers, $mt);
echo JHtml::_('select.genericlist', $MyUsers, 'MyListboxName', 'title="Select from My List"', 'value', 'text');

With the selected ID from the list, use this SQL to get the Profile data:

SELECT a.*,b.* 
FROM j32_users AS a
JOIN j32_user_profiles AS b ON b.user_id=a.id
WHERE a.id = "selected ID"

Answer 2

You would have to look for the corrosponding id number that comes with the group "Agent", or include the #__usergroups table in your query.

then, foreach user, if the group equals "agent" (or whatever number it is, if you're not including the user groups table), return the name, email, etc

Hope that helps. I could be somewhat mistaken as it's been a while but this worked for me in the past.