Displaying incorrect response from parameters

Question

DB table structure:

Session Table (aka Exam Table)

SessionId(auto) SessionName 137 XULWQ 

Question table:

 SessionId QuestionId OptionId 137 1 5 137 2 2 

Option_Table table:

 OptionId OptionType 1 AC 2 AD 3 AE 4 AF 5 AG 6 AH 7 AI 8 AJ 9 AK 10 AL 11 AM 12 AN 13 AO 14 AP 15 AQ 16 AR 17 AS 18 AT 19 AU 20 AV 21 AW 22 AX 23 AY 24 AZ 25 True or False 26 Yes or No 

Answer table:

  AnswerId(auto) SessionId QuestionId Answer 200 137 1 B 201 137 1 D 202 137 2 F 203 137 2 A 204 137 2 C 

I want to create a page where I want it to display incorrect answers to a question.

I am going to do this by extracting the type of each question, displaying all the response letters belonging to the type of options, and then deleting the correct answers from the responses to the letters, so that they are left only with incorrect answers.

array:

 $option = array(); $option[1]= array(A,B,C); $option[2]= array(A,B,C,D); $option[3]= array(A,B,C,D,E); ... $option[23]= array(A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y); $option[24]= array(A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z); $option[25]= array(True,False); $option[26]= array(Yes,No); 

My question is that I need help after this section. How to start getting the wrong answers after this array using mysqli / php and the database that I currently have?

UPDATE:

Below is an sql that displays the correct answers for each question:

  SELECT q.QuestionContent, o.OptionType, q.NoofAnswers, GROUP_CONCAT(DISTINCT Answer ORDER BY Answer SEPARATOR '') AS Answer, r.ReplyType, q.QuestionMarks FROM Question q LEFT JOIN Answer an ON q.QuestionId = an.QuestionId LEFT JOIN Reply r ON q.ReplyId = r.ReplyId LEFT JOIN Option_Table o ON q.OptionId = o.OptionId group by q.QuestionContent 

See SQL Fiddle with Demo

This returns the result:

 | QUESTIONCONTENT | OPTIONTYPE | NOOFANSWERS | ANSWER | REPLYTYPE | QUESTIONMARKS | ---------------------------------------------------------------------------------------- | Name these 2 flowers | AF | 2 | C | Multiple | 5 | | What is 2+2? | AD | 1 | ABD | Single | 5 | 

Answer 1

Your problem is that, as your Option_Table was designed, decoding an OptionType column to find out all possible answers requires some non-trivial external knowledge.

(Indeed, you did not provide enough information in your question for me to be sure how to do this, I can make an assumption for those OptionTypes that you showed me, but I can not, of course, if there are or may be others.)

It would be better to replace (or at least increase) this table with a simpler table, which for each OptionId simply lists all the possible parameters, for example:

 CREATE TABLE Options ( OptionId INTEGER NOT NULL, OptionAnswer CHAR(1) NOT NULL, -- or whatever type Answer.Answer has PRIMARY KEY (OptionId, OptionAnswer) ); INSERT INTO Options VALUES (1, 'A'), (1, 'B'), (1, 'C'), (2, 'A'), (2, 'B'), (2, 'C'), (2, 'D'), -- ... (25, 'T'), (25, 'F'), (26, 'Y'), (16, 'N'); 

Then you can find all the right and wrong answers for each question with this query:

 SELECT q.QuestionContent, q.NoofAnswers, GROUP_CONCAT(DISTINCT Answer ORDER BY Answer SEPARATOR '') AS RightAnswers, GROUP_CONCAT(DISTINCT CASE WHEN Answer IS NULL THEN OptionAnswer ELSE NULL END ORDER BY OptionAnswer SEPARATOR '') AS WrongAnswers, r.ReplyType, q.QuestionMarks FROM Question q LEFT JOIN Reply r ON q.ReplyId = r.ReplyId LEFT JOIN Options o ON q.OptionId = o.OptionId LEFT JOIN Answer an ON q.QuestionId = an.QuestionId AND o.OptionAnswer = an.Answer GROUP BY q.SessionId, q.QuestionId 

Here's a demonstration of this in SQLize (slightly modified to skip columns not included in your sample tables).

---

Edit: An alternative solution would be to create a list of incorrect answers in PHP. For example, if $row is an object (for example, returned by mysqli_fetch_object() ) containing one row from the original query, you can calculate incorrect answers like this:

 // Do this (preferably) before looping over the rows: $specialOptionTypes = array( 'Yes or No' => array( 'Y', 'N' ), 'True or False' => array( 'T', 'F' ), ); // Do this for each row: if ( array_key_exists( $row->OptionType, $specialOptionTypes ) ) { $options = $specialOptionTypes[ $row->OptionType ]; } else if ( preg_match( '/^([AZ])-([AZ])$/', $row->OptionType, $match ) ) { $options = range( $match[1], $match[2] ); } else { // issue warning about unrecognized option type $options = array(); } $right = str_split( $row->Answer ); // or explode() on a delimiter, if any $wrong = array_diff( $options, $right ); $row->WrongAnswers = implode( '', $wrong ); // if you actually want a string 

Here's a demo on ideone.com based on your sample request.

Answer 2

I think you need <a href="<?php echo $pages[$currentPages+1] ?>">Continue</a> . Otherwise, you do not output anything.