Limiting users views

I have an application that I am trying to restrict what the users can see. This is a free project management app that I found on the web, right now all users can see all projects. I would like to limit the users to only be able to view projects that they are assigned to.

The three tables that i think will apply to this problem are Users, Projects, and UserProjects.

UsersProjects contains 3 fields the UserId from the users table and ProjectId from the project table. and another field called UserProjectId.

Could someone help me with code for a function so that when the user clicks on the project button they will only see the projects that they are assigned to.

If more info is need then please let me know.

Thanks in advanced.

rosejr

Hi rosejr,

This restiction can be done by using joins in your sql statement.

You select the fields from the project table (the ones you need to build the screen) and inner join on the UserProject table to find only projects the user has access to:

SQL Statement:
SELECT Projects.*
FROM Projects
INNER JOIN UserProjects ON
Projects.ProjectID = UserProjects.ProjectID
WHERE UserProjects.UserID = [the UserID]

You only need to now the UserID (and since the user is logged in, you probably have this in a Session Variable) and substitute [the UserID] with this variable and you are ready to go:


You asked for a function:
<%
Function fnSelectProjectsForUser(iUserID)

Dim oCnn, oRs, sSQL

Set oCnn = Server.CreateObject("ADODB.Connection")

oCnn.Open [YourConnectionString]

sSQL = "SELECT Projects.*" & _
" FROM Projects" & _
" INNER JOIN UserProjects ON" & _
" Projects.ProjectID = UserProjects.ProjectID" & _
"WHERE UserProjects.UserID = " & iUserID

Set oRs = oCnn.Execute(sSQL)

If oRs.EOF Then
fnSelectProjectsForUser = 0
Else
fnSelectProjectsForUser = oRs.GetRows
End If

oRs.Close
oCnn.Close

Set oRs = Nothing
Set oCnn = Nothing
End Function
%>

You can call this function with the following code:


<table>
<%
Dim aProjects, i, j

aProjects = fnSelectProjectsForUser(Session("userID"))

If IsArray(aProjects) Then

For i = 0 To UBound(aProjects, 2)
Response.Write "<tr>" & Vbcrlf
For j = 0 To UBound(aProjects, 1)
Response.Write "<td>" & aProjects(j,i) & "</td>" & vbcrlf
Next
Response.write "</tr>" & Vbcrlf
Next
End If
%>
</table>

Hope this will help