1

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>
  • You need a pivot table to clean up the second result set. I would like to demonstrate this technique (for you and researchers). Could you please offer a db-fiddle.com demo of the users and user_profiles tables (just the CREATE and INSERT queries to build the schema)? Given that sample input, I can craft a direct query to deliver a result set that will be a breeze to iterate. *be sure to obfuscate/redact any private data. – mickmackusa Jan 31 at 13:02
3

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"
  • Hi GDP, first off thanks so much! The first section worked perfectly but I'm a bit confused on the second part. So would I write something like what I have below in order to grab user data from the #__users and #__user_profiles table for the selected user? $db = JFactory::getDbo(); $query = "SELECT a.*,b.* FROM j32_users AS a JOIN j32_user_profiles AS b ON b.user_id=a.id WHERE a.id = " . $value; $db->setQuery($query); $agentData = $db->loadObjectList(); echo $agentData->name; – Corey Jan 6 '15 at 15:04
  • That SQL looks about right...remember though, user profile info is not saved in the DB in the same fashion as the user info. You've specified 5 different profile values, so there will be 5 rows returned for each user - all with the same info from #__users, but each row having a different value from #__user_profiles. You'll get the data you want, but the result won't be structured in the usual way. – GDP Jan 6 '15 at 15:40
  • Thanks so much! I see what you mean as the query is returning a multidimensional array that contains alot of the same data from the #__users table. I was able to extract the data I need by using the following code $db = JFactory::getDbo(); $query = $db->getQuery(true); $query = "SELECT a.*,b.* FROM #__users AS a JOIN #__user_profiles AS b ON b.user_id=a.id WHERE a.id = 356"; $db->setQuery($query); $agentRow = $db->loadRowList(); and then extracting bits from each array item by echo "UserID: " . $agentRow[0][0]; Thanks again for the help! – Corey Jan 6 '15 at 16:49
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.