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.
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 (
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
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>
[[!Profile? &user=`[[*GalleryUser]]` &useExtended=`1`]]
Be sure that the Profile snippet tag is above all the placeholders.
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.)