Fetching database records with MySQL - random row selection with PDO

Question

I'm trying o fetch data from a database specific to a quiz. I have two tables named 'questions' and 'options'. Each question has 2 corresponding options and 'questionID' row is present in 'options' table as a foreign key. I'm fetching 4 random questions and their corresponding answers with this query:

    SELECT * FROM questions INNER JOIN options ON
    questions.questionID=options.questionID, (SELECT questionID AS sid FROM questions 
    ORDER BY RAND( ) LIMIT 4 ) tmp WHERE questions.questionID = tmp.sid AND
options.questionID=questions.questionID ORDER BY questions.questionID

This query runs fine and retrieves appropriate rows from both questions and options tables on phpMyAdmin but when I fetch the array in php and then retrieve the arrays with AJAX all the options are random and not matching with their questions - some of them do not match with any of the questions selected. Thanks in advance for any advice.

I checked the php fiel on its own and it seems like it's only the AJAX's fault. Now I see why - I'm making two calls to the file and I think it might be running the query again and that's why the answers do not match. Any solution?

Here's my AJAX call the same is for answers just with different url parameter:

var dataQuest = (function() {  
    var questions;
    function load(){
        $.ajax({ 
            type: "GET",
            url: "randomQ.php?q=0",
            dataType: 'json',
            async: false,
            success: function(data){
                questions = data;
                //alert(questions);
                alert(questions);
            },
            error: function(XMLHttpRequest, textStatus, errorThrown){
            $('#place1').text("Error: " +textStatus +" "+ errorThrown);
            }
        });//end ajax
    } //end of load
    return {
        load: function() { 
            if(questions) return;
            load();
        },
        getQuest: function(){
             if(!questions) load();

             return questions;
        }
    }
})();//end of dataQuest function

As requested - the php bit:

while( $row = $stmt->fetch()) { 
            $quests[] = $row['question'];
            $answers[] = $row['option'];
}

//$questions = array_unique($quests);
//$answs = array_chunk($answers, 2);

if($_GET['q']==0)
{
    echo json_encode($quests);

}
else
{
    echo json_encode($answers);
}

I've changed that into:

$arrays[] = $quests;
$arrays[] = $answers;


    echo json_encode($arrays);
    //echo json_encode($answers);

And that returns the appropriate answers and questions to AJAX - however to make that work - I need to split this 2D array in Javascript - any recommendations on how to achieve that appreciated.

Answer 1

Try this...

SELECT * FROM questions 
LEFT JOIN options ON
questions.questionID=options.questionID
WHERE options.questionID=questions.questionID 
ORDER BY RAND()
LIMIT 8 //You will need 8 rows as each question has 2 option rows

Hard to know without seeing your db schema but this should work.

In your PHP script this change may help you associate the data in your jQuery:

while( $row = $stmt->fetch()) { 
        ///use the PK of the question instead of arbitrary index
        $quests[$row['question_id']] = $row['question'];
        $answers[$row['question_id']] = $row['option'];
}

Or maybe..

while( $row = $stmt->fetch()) { 
        //create a single array indexed by question pk with sub array of options
        $quests[$row['question_id']]['question'] = $row['question'];
        $quests[$row['question_id']]['options'][] = $row['option'];
}