We help IT Professionals succeed at work.
Get Started

how to build a query to display -- to a logged in user -- a list of other users with UserRoleID = 5 -- who ALSO live in the same state as the logged in user?

Eric Bourland
on
279 Views
Last Modified: 2012-05-09
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.

In detail:

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">
  SELECT UserID
     , FirstName
     , LastName
     , State
     , UserEmail
     , UserRoleID
     , SSMA_TimeStamp
     , DateRecordModified
     , NameofSchool
     , NameofDistrict
  FROM tbl_NBPTS_Principals
  WHERE UserID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(session.auth.UserID)#">
  </cfquery>

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">
  SELECT UserID
     , FirstName
     , LastName
     , State
     , UserEmail
     , UserRoleID
     , SSMA_TimeStamp
     , DateRecordModified
     , NameofSchool
     , NameofDistrict
  FROM tbl_NBPTS_Principals
  WHERE UserRoleID = <cfqueryparam cfsqltype="cf_sql_integer" value="5">
    AND State = <cfqueryparam cfsqltype="cf_sql_varchar" value="#session.auth.State#">
  </cfquery>

... 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. =)

Eric
Comment
Watch Question
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 4 Answers and 18 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE