Display a random set of 10 questions and answers with MySQL and PHP

Question

I'm making a survey with PHP and MySQL. I'm having trouble figuring out what method to use to display a random set of 10 questions, and their answers. Each question can have between 2 and 5 answers.

Here is how the database is set up:

questions:
ID | quID | question

answers:
ID | an_quID | anID | answer

quID and an_quID link the tables together.

If I do the following (not exact code; edited for brevity). I end up doing 20 database calls.

$sql = "SELECT * FROM questions ORDER BY RAND() LIMIT 10";

while($row = mysql_fetch_array($result)) {

    $quID = $row[quID];

    echo "HTML code here";

    "SELECT * FROM answers WHERE an_quID = '$quID'"

    while($inside_row = mysql_fetch_array($inside_result)) {

        $answer = $inside_row[answer];

        echo "HTML code here";

    }

}

I have also tried using array_push(), with just 2 database calls, to push all the results into arrays. But the problem with array_push is that, as far as I can tell, you can't make associative arrays where you specify the keys.

What would be the ideal way to display a random set of 10 questions, and their answers?

Answer 1

Something like this:

SELECT * FROM questions AS q INNER JOIN answers AS a ON q.quID = a.an_quID 
ORDER BY RAND() LIMIT 10

Then in your code you can filter the results based on rows returned with the same quID

Answer 2

Instead of 1 call per question to pull the answers, you could pull all the question ID's out and use the IN () syntax in MySQL to pull all the answers at once. Then loop through to flatten the data as you see fit

SELECT * FROM answers WHERE an_quID IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);

Answer 3

joshtonic is really close. I'd do something like this:

SELECT * FROM answers WHERE an_quID IN (SELECT quID FROM questions ORDER BY RAND() LIMIT 10);

As Kuchen mentioned, you need to do a bit of normalization on your DB. You shouldn't be using id and question_id (id should be all you need);