CODEWITHSUNDEEP
phpmysqljoinwhile-loop
Iain Simpson
Iain Simpson

Reputation: 8141

Join + While loop only outputting one result

I am trying to make a quiz and am using the following to compare the results, it seems to work and outputs once but that's all it does, I don't get any more records processed even though there are two question in the quiz, so it should be outputting correct, correct or correct, incorrect etc.

          <?php
    // Make a MySQL Connection
    // Construct our join query
    $query = "SELECT * FROM itsnb_chronoforms_data_answerquiz a, itsnb_chronoforms_data_createquestions
    q WHERE a.quizID='$quizID' AND a.userID='$userID' and q.quizID=a.quizID and
    a.quizselectanswer = q.correctanswer" or die("MySQL ERROR: ".mysql_error());

    $result = mysql_query($query) or die(mysql_error());


    // Print out the contents of each row into a table 
    while($row = mysql_fetch_array($result)){
        if ($row['correctanswer'] == $row['quizselectanswer']){
            echo 'CORRECT';}
            else { echo 'INCORRECT';
            }

        echo "<br />";
    }
    ?>

EDIT >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

Table structures as follows >>

itsnb_chronoforms_data_answerquiz cf_id , cf_uid , cf_created , cf_modified , cf_ipaddress, cf_user_id, questionID, quizselectanswer ,quizID ,userID

itsnb_chronoforms_data_createquestions cf_id ,cf_uid,cf_created ,cf_modified, cf_ipaddress, cf_user_id, quizID, questionID, quizquestion, quizanswer1, quizanswer2, quizanswer3, quizanswer4, questionformat ,correctanswer

Upvotes: 0

Views: 146

Answers (1)

femtoRgon
femtoRgon

Reputation: 33351

You are getting four answers when removing the selectanswer=correctanswer condition (which is indeed not what you want, since you want to fetch both correct and incorrect answers) because you don't relate the answer ids.

$query = "SELECT * FROM itsnb_chronoforms_data_answerquiz a, itsnb_chronoforms_data_createquestions
q WHERE a.quizID='$quizID' AND a.userID='$userID' and q.quizID=a.quizID and
a.quizselectanswer = q.correctanswer" or die("MySQL ERROR: ".mysql_error());

should be:

$query = "SELECT * FROM itsnb_chronoforms_data_answerquiz a, itsnb_chronoforms_data_createquestions
q WHERE a.quizID='$quizID' AND a.userID='$userID' and q.quizID=a.quizID and
a.questionID = q.questionID" or die("MySQL ERROR: ".mysql_error());

You were getting a cross product of questionIDs without that condition. The four records you were seeing being (if the questionIDs are 1 and 2):

  1. a.questionID=1 and q.questionID=1
  2. a.questionID=1 and q.questionID=2 (don't want this)
  3. a.questionID=2 and q.questionID=1 (or this)
  4. a.questionID=2 and q.questionID=2

Upvotes: 2

Related Questions