Restricting access to list & detail pages

[mgn2683]

I have a sort of complex query to write and I don't know where to start. Basically, there are list and detail pages of candidate answers on a site. There are readers assigned to certain candidates. Is there a way to display a list page showing only the candidates assigned to that particular reader? There are two separate user levels in the database user table for readers and candidates.

And also, based off this, another part is added. Once a Chaplain signs off on a candidate answer, we need to be able to restrict readers from returning to that particular candidate detail. I was thinking about doing something in a recordset for this:

WHERE score_overview.chaplain_review= null OR blank

so that if it reads a chaplain has already checked it off, it won't allow the reader back. But how would I actually set up the restriction through a recordset?? Thanks in advance.

[Atli]

Hi.

Not knowing your exact table structure, I assuming a structure like this:

CREATE TABLE `Member` (
  `MemberID` INT Auto_Increment Primary Key Not Null,
  `MemberRole` ENUM('Reader', 'Candidate') Not Null,
  `MemberName` VarChar(65) Not Null
);

CREATE TABLE `ReaderAssignment` (
  `ReaderID` INT References `Member`(`MemberID`),
  `CandidateID` INT References `Member`(`MemberID`),
  PRIMARY KEY (`ReaderID`, `CandidateID`)
);

CREATE TABLE `Answers` (
  `AnswerID` INT Auto_Increment Primary Key Not Null,
  `CandidateID` INT References `Member`(`MemberID`),
  `AnswerText` VarChar(255) Not Null Default 'Whatever',
  /* Or whatever data your answer may need */
  `AnswerReviewed` BOOL Not Null Default FALSE
);

You could fetch a list of un-reviewed answers for a specific reader (John, in my example) by doing this:

SELECT 
  reader.`MemberName` AS 'Reader',
  candidate.`MemberName` AS 'Candidate',
  answer.`AnswerID`, answer.`AnswerText`
FROM `Answers` AS answer
INNER JOIN `Member` AS candidate
  ON candidate.`MemberID` = answer.`CandidateID`
INNER JOIN `ReaderAssignment` AS assign
  ON candidate.`MemberID` = assign.`CandidateID`
INNER JOIN `Member` AS reader
  ON reader.`MemberID` = assign.`ReaderID`
  AND reader.`MemberName` = 'John'
WHERE 
  answer.`AnswerReviewed` = FALSE
ORDER BY
  candidate.`MemberName`;

The thinking here is...

• First, fetch all the answers that haven't been answered. Filter that using the WHERE caluse.
• Get the candidate info by joining the Member table.
• Get the reader info by joining the ReaderAssignment table to get the reader ID, and then the Member table again to get the reader info.
• Finally, filter out all answers except those belonging to a specific reader by adding the AND clause to the second Member table join.
• And, just for kicks, throw in the ORDER BY clause to get the list ordered by candidate names.

[mgn2683]

I am using Developer Toolbox for this. So far, this is the query I have:

SELECT score_overview.id, score_overview.chaplain_review, tbl_user.Id, tbl_user.user_level
FROM score_overview, tbl_user
WHERE score_overview.chaplain_review= '1' AND tbl_user.user_level= "2"

I don't have to pull in the candidate answer because a recordset on the list page is already pulling that. This recordset is on the detail page, because I don't want to restrict readers from the list, just whatever records have already been checked off by the chaplain.

Thanks in advance for any help.

[mgn2683]

Anyone have any ideas or a better method to achieve this?