In brief: How can I build a query to display a list of "Principal" Users (think: public school principals) who have a specific UserRoleID 5 -- but, this list of Principals is displayed ONLY to a logged in "Liaison" User with UserRoleID 3.
Also, the list of displayed Principals must contain only Principals who live in the same state as the logged-in Liaison User. =) This is the tricky part.
1. Assume a logged-in user with a valid session.auth.UserID.
2. table tbl_NBPTS_Principals contains data about many users, including Principals and Liaisons
3. All users are assigned to a state of residence (variable "State" in the query below)
4. variable UserRoleID defines users by user role: Principals = UserRoleID 5; Liaisons = UserRoleID 3
5. UserRoleID is not the same as UserID -- each user has his or her own unique UserID, for database integrity; variable UserID is PK in table tbl_NBPTS_Principals
So far, my query is:
<cfquery datasource="#ds#" name="GetUserList">
WHERE UserID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(session.auth.UserID)#">
This query requests details (FirstName, LastName, etc) from tbl_NBPTS_Principals and displays them for the UserID of the logged in Liaison user. This first query works fine.
I think I need another query, that will request from tbl_NBPTS_Principals a list of users with UserRoleID = 5, who are associated with a certain state -- the SAME state as the logged in Liaison user!
So, to sum up: a liaison user logs in. This liaison user lives in the state of Illinois. I need to figure out a query to display a list of Principals (UserRoleID 5) from Illinois to this logged in liaison user.
I am baffled because this query below seems like it should work, but it does not:
<!--- in this query, select columns from table tbl_NBPTS_Principals --->
<!--- we want columns for only the logged-in UserID; UserRoleID = 5; and the State must match the State of the current logged-in user --->
<!--- the data is selected for the logged in UserID, and displayed in the table below; logged-in user can edit this data --->
<cfquery datasource="#ds#" name="GetPrincipalsbyState">
WHERE UserRoleID = <cfqueryparam cfsqltype="cf_sql_integer" value="5">
AND State = <cfqueryparam cfsqltype="cf_sql_varchar" value="#session.auth.State#">
... the results are blank. Where am I going wrong?
In application.cfc, variable session.auth.State is defined correctly in function onApplicationStart.
Thanks again for helping me. I've been staring at this all day. I really look forward to finding out how this works. =)