Displaying different questions each time and record answer into database

[cool_irfan]

Hi friends, I'm developing the online exam application and strucked. below is my code to display the records randomly each time, but the problem is these questions are repeating and also the answer of the next question(that is about to display) is saved as the answer selected for present displayed question whenever I hit the submit button which is totally wrong method.

`

Inline Code Example Here<form action="#" method='post' >
<td class="center"> <?php


 $connect = mysqli_connect('localhost', 'root', '', 'exam') or die('Error connecting to MySQL server.');
$i=0;

           $result=mysqli_query($connect,"SELECT * FROM addquestions order by RAND() LIMIT 1 ") or die(mysqli_error());
  while ($row = mysqli_fetch_array($result) )
   {
    $question = $row['question'];
    $option_a = $row['option_a'];
    $option_b =$row['option_b'];
    $option_c = $row['option_c'];
    $option_d = $row['option_d'];
    $answer = $row['answer'];
    ?>
<p><b>Question :</b> <?php echo $question; 
 ?>
<p>Answers: <input type = "checkbox" name = 'a' value = 'a'/> <?php echo $option_a; ?></p>
<p> <input type = "checkbox" name = 'b' value = 'b'/><?php echo $option_b; ?> </p>
<p> <input type = "checkbox" name = 'c' value = 'c'/> <?php echo $option_c; ?></p>
<p> <input type = "checkbox" name = 'd' value = 'd'/> <?php echo $option_d; ?></p>
<input type="submit" name = "submit" />
   <?php

if(isset($_POST['submit']))

{ $n = strcmp($option_a,$answer);
    if(isset($_POST['a']))
    {
    $query = mysqli_query($connect, "INSERT INTO questions(test_id, question, option_a, option_b, option_c, option_d, answer, user_answer)VALUES('$name','$question', '$option_a', '$option_b', '$option_c', '$option_d', '$answer','a')");
     echo "<script>alert('Entered Successfully, ')</script>"; 

   }
else if(isset($_POST['b']))
    {

$query = mysqli_query($connect, "INSERT INTO questions(test_id, question, option_a, option_b, option_c, option_d, answer, user_answer)VALUES('$name','$question', '$option_a', '$option_b', '$option_c', '$option_d', '$answer','b')");
     echo "<script>alert('Entered Successfully, ')</script>";
}
else if(isset($_POST['c']))
    {

$query = mysqli_query($connect, "INSERT INTO questions(test_id, question, option_a, option_b, option_c, option_d, answer, user_answer)VALUES('$name','$question', '$option_a', '$option_b', '$option_c', '$option_d', '$answer','c')");
     echo "<script>alert('Entered Successfully, ')</script>";
}
else if(isset($_POST['d']))
    {

$query = mysqli_query($connect, "INSERT INTO questions(test_id, question, option_a, option_b, option_c, option_d, answer, user_answer)VALUES('$name','$question', '$option_a', '$option_b', '$option_c', '$option_d', '$answer','d')");
     echo "<script>alert('Entered Successfully, ')</script>";
}
else 
    {

$query = mysqli_query($connect, "INSERT INTO questions(test_id, question, option_a, option_b, option_c, option_d, answer, user_answer)VALUES('$name','$question', '$option_a', '$option_b', '$option_c', '$option_d', '$answer','not selected')");
     echo "<script>alert('Entered Successfully, ')</script>";
}
}



   } ?>
</form>

`

You have a few options.

1) The easiest is probably randomizing the select with ORDER BY rand() - however this is not a great - see discussion about using RAND() in searches. BUT, it is easy. You can then store your resulting array in a session variable. You can then store your answers in the same array and then to a bulk INSERT at the end. Why session? Well you may need to flick through several page loads to complete the test and this would be required if you weren't storing anything until the end.

Drawbacks? A few - what happens if the session is dropped, e.g. candidate stops exam in middle or browser closes - all data lost. The RAND() order as mentioned.

2) You could draw up a random list as mentioned above and store this order in a some related tables beforehand e.g. tests, test_answers (related table) - you'd have to have these tables anyway. You save the answer to the DB on each response. This could be done via Ajax or normal form submission.

Drawbacks? Not many that I can think of - just a bit more to-ing and fro-ing from the DB between questions, but that's not a biggie.

I'm sure there are other ways, but those are the ones that spring to mind.

Possible tables:

TESTS
id | name | ...

QUESTIONS
id | test_id | question | ansA | ansB | ansC | ansD | correctAns

USERS
id | name | ...

TEST_USERS
id | test_id | user_id | active | created | test_result (non-normalized field)

TEST_RESPONSES
id | test_users_id | question_id | response | result (optional marking field)

So starting a new test with a logged in user:

1) They select the test they want to take (e.g. from a dropdown with data from the TESTS table).

2) Your app INSERTS a record in the TEST_USERS table (user_id and test_id) - grab the new TEST_USERS 'id' using a last_id function for whichever abstraction (PDO/mysqli etc) that you're using

3) Next, app selects say 20 random questions from QUESTIONS WHERE the test_id is equal to that selected by the user. You can LIMIT the questions to however many you want and ORDER BY something like RAND(). You just need to SELECT the question 'id' field for now.

4) INSERT these ids in a bulk INSERT along with test_users 'id' from point 2) into the TEST_RESPONSES table

Your test is now set up ready for the user to begin. They could begin immediately or leave it for later.

When they want to begin SELECT from several tables using INNER JOINs:

SELECT q.question, q.ansA, q.ansB, q.ansC, q.ansD FROM questions AS q INNER JOIN test_responses AS tr ON q.id = tr.question_id INNER JOIN test_users as tu ON tr.test_users_id = tu.id WHERE tu.test_id = :test_id AND tu.user_id = :user_id ORDER BY tr.id  

If you want to just retrieve one question at a time, you can select the first non-null field in repsonses from test_responses:

SELECT q.question, q.ansA, q.ansB, q.ansC, q.ansD FROM questions AS q INNER JOIN test_responses AS tr ON q.id = tr.question_id INNER JOIN test_users as tu ON tr.test_users_id = tu.id WHERE tu.test_id = :test_id AND tu.user_id = :user_id AND tr.response IS NULL ORDER BY tr.id LIMIT 1  

Off the top of my head, but I hope it makes sense.

[cool_irfan]

@Diafol Thanks for your response, I'm too correct in thinking your logic to store the answer eachtime in same db_table. If I use rand() the questions are repeating and the answer of the present displayed question(when I clicked submit) is storing for the next question which displayes after.

I didn't show the response bit, but it would be something like...

Say you had a form with radiobuttons

<input name="response" id="responseA" type="radio" value="A" /><label for ="responseA"><?= $ansA ?></label>
<input name="response" id="responseB" type="radio" value="B" /><label for ="responseB"><?= $ansB ?></label>

etc. The $ansX variables coming from the last query shown in the previous post. You could save the question id (test_response 'id' field) to a hidden field for easy access, however this opens up the possibility of an user changing the value to anything they'd like.

You grab the response from: $_POST['response'] and validate it (is the value A,B,C or D)

On successful validation, you then store the value, using PDO/mysqli prepared statement (id from hidden field in form):

UPDATE test_response SET response = :response WHERE id = :id

Alternatively find another way to grab ':id' from a query on the test_responses table.

Once you successfully update, run the last query from the previous post again to get the next question.

[cool_irfan]

@Diafol, I'm still confused in selecting the fisrt question randomly as I stored all the questions in same datatable without using RAND(). As you already know the Rand() repeates the question.....
Please help me out from this situation....Also the ID's stored in datatable is not in order as we can delete the questions whichever is not necessary...

I can't see that...

Also the ID's stored in datatable is not in order as we can delete the questions whichever is not necessary...

Has any bearing at all.

As you already know the Rand() repeates the question.....

Does it? I didn't know that. How?

Looking at my schema, the QUESTIONS table holds all the questions for every test you can think of (noted by test_id). This schema does NOT allow re-use of questions for different tests though, but it would be easy to alter the schema to do so.

The TEST_RESPONSES table accepts a randomized sample of the data from the QUESTIONS (question_id) table. You set the limit on the number of questions you want to extract and by using RAND(), you get a randomized sample:

QUESTION: id - 1,2,3,4,6,7,8,11,13,14,18,20,23,24,25,26,27,29,30,31,39,40,47,51,52,54,59

TEST_RESPONSES: question_id - 31,18,13,40,29,23,6,52,51,8,59,24,54,27,3,39,25,20,2,1

[cool_irfan]

@Diafol, thank for your support. I understood that I have to add one more table by name TEST_RESPONSES.

I already tried with..... RAND() LIMIT 1..... but the questions were repeated.

sorry its my mistake for not understanding your method.

Below is a bit of my application, please refer and guide me wehter I'm going right or not. It would be greate if all this process goes in same table to reduce number of tables, as already around 12 tables are build. Based on your reply I'll change my table's.

But at present I am using single table to manage full process. In my application I already made another page for management where all the questions and options added to the database will be displayed in a psge(whole db_table with selected columns).

In my application Expert(say lecturer) will add the questions and the Controller(say Principal) will delete the unwanted questions. Hence the questions left in the table will be asked for students.

Thank's

I am using single table to manage full process

This seems to be a problem. This will cause duplication and many other issues. MySQL and its relatives are relational databases as you are aware and you need to normalize the data by creating an robust schema. I can't fathom how you'd get a single table to work anyway. Must be horrendously convoluted with duplicated data.

With regard to having 12 tables - pah! That's nothing. I'm developing an app at the moment with 27 tables with more to add. Having many tables is not an issue in itself. Creating a robust schema which ensures a high level of normalization is your goal - it's not something to avoid. Sure, you end up having to create queries with a few JOIN clauses, by hey, that's what we do.

RAND() LIMIT 1

No, this won't work as you've seen - the DB has no 'memory' of previously extracted questions. This is the reasoning behind the pulling of question ids into the TEST_RESPONSES table.

In addition for the sake of historical storage, questions shouldn't be deleted, rather 'deactivated' using a status field. Valid questions will have the default '1' value and those pulled by the Principal should be switched to '0'. However, this is only true though if the Principal decides to 'delete' questions after the test (with those questions) have been taken by some or all of the students. Otherwise, it'll be fine to delete them for real.

[cool_irfan]

@Diafol, thanks for making me understand. I thought more tables may affect the perfromance(in terms of loading the different pages).

Now I will create one more table and add the particulars and try. Surely I need your help for future development as I'm new to php and developing. Thanks