Logical behind MCQ/quiz

I am new to php and database. A project has come up in which I need to make a quiz or MCQ. from my understanding there are two ends, one which admin posts questions with options and other end is public side in which the person is displayed with options and the users select. Now here is confusion

  1. How do I save questions’s options in database and later show them to users
  2. When user select the answer, how do I safe it back to database? Do I need 3 tables? One for questions, one for answers and one for all options?
  3. As the user will be random, how can I save each answers as I need to display them random results based on there selection.

I know it’s too much to handle but I need some head start with coding and how to move forward.

You can do it all in one database table and I wrote an online movie quiz the structure of my quiz is:

CREATE TABLE IF NOT EXISTS `movietrivia` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `confirm` enum('yes','no') COLLATE latin1_german2_ci NOT NULL,
  `question` text COLLATE latin1_german2_ci NOT NULL,
  `answerA` char(50) COLLATE latin1_german2_ci NOT NULL,
  `answerB` char(50) COLLATE latin1_german2_ci NOT NULL,
  `answerC` char(50) COLLATE latin1_german2_ci NOT NULL,
  `answerD` char(50) COLLATE latin1_german2_ci NOT NULL,
  `correct` int(1) NOT NULL,
  `dateAdded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=72 ;

Of course you would be setting the quiz database table the way you want it to look like.

Setting up the table, implementing it in PHP isn’t too much to handle and if you run into problems just come back here with you problem and code. People here will be glad to help you along.

If its one table, how do we select and display options for that one question, because lets say there are 20 questions. Its hard to imagine, how will we use where clause of selecting question 1 and selecting options from where foreign id = 1 too.

In the code above, the single table contains the question, the four possible answers, and which one of them is the correct answer, so retrieving the information to display the question is a simple query. It would be a little more complex to split it across two tables, but unless you have some answers that are valid for more than one question, I can’t see why you would want to. If you did, you’d just use an INNER JOIN to link the questions and corresponding answers.

You would also do it if the different questions have a different number of answers, e.g., one question may have 2 answers while another may have 10. You could add 10 columns to one table and have 8 them be null for the two answer question, but a separate table would be the better model here.

1 Like

Good point, I hadn’t considered varying numbers of possible answers.

@Pepster I got the first part right, i did select and it came out well :smile: , now my question here is which is really confusing me for, how to select answers to a question no 6. I know this verbally that you would use something like WHERE id=6 but on what bases.? My code is as follows.

  global $wpdb;
  $myrows = $wpdb->get_results( "SELECT question,a1,a2,a3,a4,a5,a6 FROM wp_quiz" );
  foreach ($myrows as $list) {
    echo $list->question;
    echo "<br>";
    echo $list->a1 ."<br>". 
    $list->a2 ."<br>".
    $list->a3. "<br>";
    $list->a4. "<br>";
    $list->a5. "<br>";
    $list->a6. "<br>";
    $list->a7. "<br>";
  }

P.S, how would you preformat the code?