Show User Info Based on a TV II

Use an @SELECT TV to show only users from a single user group in a drop-down list.


In the last article, we looked at a way to select a user from a list of all users in a TV and then show that user's information on the page. In this article, we'll see how to do it with a slightly more complicated @SELECT TV that will show the users in just one user group.

MODX logo

Restricting the List to a Single User Group

The first step is the same as the one in the previous article — creating the TV, which we called GalleryUser. On the "Input Options" tab, we set the TV Type to Listbox (single-select). In the last article, we put this in the "Input Option Values" field:

@SELECT `username`,`id` FROM `modx_users` WHERE `active` = 1

We left the other fields alone.


New @SELECT Statement

To restrict the list to members of a single group, we need a more complex @SELECT statement for the Input Option Values of the TV. This new code must be all on one line:

@SELECT modx_users.username, modx_users.id FROM modx_users LEFT JOIN modx_member_groups ON modx_users.id = modx_member_groups.member where modx_users.active = 1 AND modx_member_groups.user_group = 4

The number at the very end should be the ID of the user group. You can see that number by going to System (gear icon) -> Access Control Lists and looking at the "User Groups and Users" tab. The ID of the group is in parentheses next to the group name. In the SQL statement above, modx_ is the table prefix used in the MODX database, so you'll need to change it if your MODX DB tables have a different prefix.

In the SQL statement above, we're telling the database engine (e.g., MySql) that we want the username and id taken from the modx_users table. We have to qualify the fields by prefixing them with the table name and the dot so MySQL knows which table we want those fields from. The modx_member_groups table also has an ID field, but it doesn't hold the user ID. In that table, the user ID is in the member field. Note that since we're not going through xPDO, we need the actual table names rather than the aliases MODX uses for them (modUser and nodUserGroupMember.

If you need to use other tables, see this page to get the actual table names.

By using the JOIN ... ON code, we're telling MySQL that we want to find records where the id field in the modx_users table is the same as the member field in the modx_member_groups table.

In the WHERE part of the code, we're saying that we only want users where the active field in the user's table is set to 1, and the user_group field in the member groups table contains the ID of the user group we want to show users from.


Displaying the Results

As in the previous article, we'll use the Profile snippet to show the user's information. The Profile snippet is part of the Login package, so make sure the Login extra is installed. Put this code the page template (or in the Resource Content field):

[[!Profile? &user=`[[+GalleryUser]]`]]

<p>Username: [[+username]]</p>
<p>Full Name: [[+fullname]]</p>
<p>Email: [[+email]]</p>
<p>Phone: [[+phone]]</p>

You can use any placeholders from the user or user profile object. If you need to use extended fields, do this:

[[!Profile? &user=`[[*GalleryUser]]` &useExtended=`1`]]

Be sure that the Profile snippet tag is above all the placeholders.


Coming Up

In the next article, we'll look at how to change the message user's see when they enter the wrong credentials on the login page.


Looking for high-quality, MODX-friendly hosting? As of May 2016, Bob's Guides is hosted at A2 hosting. (More information in the box below.)



Comments (0)


Please login to comment.

  (Login)