Result Shuffling problem in Mysql

Ex094 · 04-11-2014, 07:00 PM

Well I've this script which fetches questions from Mysql database according to the user chosen subject, now the question must appear randomized to the user so that they might not memorize it or guess it but I've tried to implement this feature many times and I've been failing. It seems that this Submit button is causing the problem as when it refreshes the page the question gets changed so as the options and in turn the answer becomes wrong..

Code:
//Ex094's Script
$com = "select * from mcqs where subject LIKE '%$sub%' ORDER BY RAND() LIMIT $start, $limit";

$query=mysql_query($com);

while($query2 = mysql_fetch_array($query)) {

  echo "Q) " . $query2['question'] . "</br>";

    $question = $query2['question'];
    $opt1 = $query2['opt1'];
    $opt2 = $query2['opt2'];
    $opt3 = $query2['opt3'];
    $opt4 = $query2['opt4'];
    $ans = $query2['ans'];



    echo "<form id='formstyle' method='post' action=''>";

    echo "<ul id='center'>";

  echo "<li><input type='radio' name='answer' value='$opt1' id='choice'>".$query2['opt1']."</input></br></li>";
  echo "<li><input type='radio' name='answer' value='$opt2' id='choice'>".$query2['opt2']."</input></br></li>";
  echo "<li><input type='radio' name='answer' value='$opt3' id='choice'>".$query2['opt3']."</input></br></li>";
  echo "<li><input type='radio' name='answer' value='$opt4' id='choice'>".$query2['opt4']."</input></br></li>";

  echo "</ul>";

  echo "</br><input id='1' type='submit' value='Submit' name='submit' style='margin-bottom:15px;'></form>";

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

    $_POST['answer'] == " ";

    if($_POST['answer'] == $ans) {

      echo "<h3>Correct!</h3>";

    } else {

      echo "<h3>Wrong</h3>";
      echo "The correct answer was <b>$ans</b> </br>";
    }
  }
}

I don't understand what else should I do, Immediate help would be appreciated Smile

invisal · 04-12-2014, 12:35 AM

Not entirely surprised that it does not work as you expect because your logic is at
the complete mess.

You should do something like this. Have a hidden field that store the ID of the
question so that we can query for their answer later. Noted that it is only a
pesudocode, not a working code.

Code:
<?php
   // check the answer
   if (isset($_POST['answer'])) {
        $result = query("SELECT ans FROM question WHERE id = :id");
        if ($result['ans'] == $_POST['answer']) {
             echo "Correct Answer";
        } else {
             echo "The answer is " . $result['ans'];
        }
        exit();
   }

   // get a random question
   $question = query("SELECT * FROM question WHERE subject LIKE '%...%' ORDER BY RAND() LIMIT 1");
   
   // display the question out.
?>

Ex094 · 04-12-2014, 05:33 AM

@invisal Ok Here's what I'm trying to do,

This is a script from my project, There are 5 columns namely Subject, Question, Option 1, 2, 3, 4 and then the Answer. This script fetches the rows for the respective subject that the user has chosen for Example User chose Physics. Now all the items print perfectly but the problem is when I choose an option and press submit, at the instance all the options and the question gets changed for example my quiz is

What is an atom?
1) Particle
2) Neutral Ion
3) Proton
4) None of These

When I select Particle(Radio Button) and press Submit this happens:

[Image: FvBTsRy.png]

All my options and the question change after Submit

invisal · 04-12-2014, 06:56 AM

The thing is that HTTP is a stateless. State where you print the random question
and the state where you check the correct answer is different. To fix this problem
you need to do the following (all the 3 piece of code is in the same file. I just
separate it here in forum to provide some more explanation.

Code:
<?php
	if (isset($_POST["answer"])) {
		// check for correct answer, you need to query
		// the question and answer again to compare
		
		$sh = $db->prepare("SELECT * FROM question WHERE id = :id");
		$sh->bindValue(":id", $_POST["question_id"]);
		$question = $sh->fetch();
	} else {
		$sh = $db->prepare("SELECT * FROM question ORDER BY RAND() LIMIT 1");
		$question = $sh->fetch();
	}
?>

This is where you show your question. I put a hidden field to store the ID of the question,
so that we can get back the same question again when we submit the answer.

Code:
<form action="" method="POST">
	<input type="hidden" name="question_id" value="<?php echo $question["id"]; ?>">
	
	<ul>
		<li><input type="radio" name="answer" value="<?php echo $question["opt1"]; ?>" id="choice"><?php echo $question["opt1"]; ?></input></li>
		<li><input type="radio" name="answer" value="<?php echo $question["opt2"]; ?>" id="choice"><?php echo $question["opt2"]; ?></input></li>
		<li><input type="radio" name="answer" value="<?php echo $question["opt3"]; ?>" id="choice"><?php echo $question["opt3"]; ?></input></li>
		<li><input type="radio" name="answer" value="<?php echo $question["opt4"]; ?>" id="choice"><?php echo $question["opt4"]; ?></input></li>
	</ul>
	
	<input type="submit" value="Submit">
</form>

This is how to state if the submit answer is correct.

Code:
<?php 
	if (isset($_POST["answer"])) {
		if ($_POST["answer"] == $question["ans"]) {
			echo "Correct";
		} else {
			echo "Wrong";
		}
	}
?>

Ex094 · 04-12-2014, 11:19 AM

@invisal I am not famailar with PDO hence I'm getting this error "Call to a member function prepare() on a non-object"

Here's my code:

Code:
<?php
    if (isset($_POST["answer"])) {
        // check for correct answer, you need to query
        // the question and answer again to compare

        //global $prepare;
         
        $sh = $db->prepare("SELECT * FROM mcqs WHERE question LIKE '%$sub%'");
        $sh->bindValue("$sub", $_POST["question_id"]);
        $question = $sh->fetch();
    } else {
        $sh = $db->prepare("SELECT * FROM mcqs ORDER BY RAND() LIMIT 1");
        $question = $sh->fetch();
    }
?>

I've edited your code according to my needs. I am totally mind boggled at the moment since I have a deadline till Monday and I want this to get solved today :S

P.S IF you want I can send you my whole script

invisal · 04-12-2014, 01:26 PM

Well, I was assuming that you know PDO so that's why I skip where you need to
make a connection. You can add this line at the top of the code to make MySQL
connection with PDO.

Code:
$db = new PDO("mysql:host=localhost;dbname=test", "user", "password");

Now you can use the object $db.

Now, you did not correctly change the code to your need yet. It should be this way

Code:
<?php
    if (isset($_POST["answer"])) {
        // check for correct answer, you need to query
        // the question and answer again to compare

        $sh = $db->prepare("SELECT * FROM mcqs WHERE id = :id");
        $sh->bindValue(":id", $_POST["question_id"]);
        $question = $sh->fetch();
    } else {
        $sh = $db->prepare("SELECT * FROM mcqs WHERE sub LIKE :sub ORDER BY RAND() LIMIT 1");
        $sh->bindValue(":sub", "%{$sub}%")

        $question = $sh->fetch();
    }
?>

I wonder if there is ID field in table mcqs?

Ex094 · 04-12-2014, 01:30 PM

@invisal No, I am using the subject field for that purpose. I'll give this a try

My table has:
- Subject
- Question
- Ans
- Opt1
- Opt2
- Opt3
- Opt4

The list is in order

invisal · 04-12-2014, 01:44 PM

Well, you cannot use Subject to uniquely identify the current question.
You can use question field, but I advice you create ID field for each
question. I am surprised you design this table without ID field.

Ex094 · 04-12-2014, 02:07 PM

(04-12-2014, 01:44 PM)invisal Wrote: Well, you cannot use Subject to uniquely identify the current question.
You can use question field, but I advice you create ID field for each
question. I am surprised you design this table without ID field.

Life saver, You are. Working like a charm now! Thank you very much..
Closed and marked solved!