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

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?

---

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);

---

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);

---

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

---

You don't need that many IDs, one for each Table and there are can be many answers to a question, so we need a non-unique field within answers that point to the questions id:

questions: 
id | question

answers:
id | id_questions | answer | correct

And database queries within loops are (99% of the time) a bad thing.
Get the questions, and use WHERE IN with the question ids for example to get the entries you want.

A basic, working example:

/** Tables

    CREATE TABLE `questions` (
      `id` INT  NOT NULL AUTO_INCREMENT,
      `question` VARCHAR(255)  NOT NULL,
      PRIMARY KEY (`id`)
    )
    ENGINE = MyISAM;

    CREATE TABLE `test`.`answers` (
      `id` INT  NOT NULL AUTO_INCREMENT,
      `id_questions` INT  NOT NULL,
      `answer` VARCHAR(255)  NOT NULL,
      `correct` BOOL  NOT NULL,
      PRIMARY KEY (`id`)
    )
    ENGINE = MyISAM;

 */

/** Test Data

    INSERT INTO `questions` (id,question) VALUES
    (null,'Pick A!'),(null,'Pick B!'),(null,'Pick C!'),(null,'Pick D!');

    INSERT INTO `answers` (id,id_questions,answer,correct) VALUES
    (null,1,'Answer A',1),(null,1,'Answer B',0),(null,1,'Answer C',0),(null,1,'Answer D',0),
    (null,2,'Answer A',0),(null,2,'Answer B',1),(null,2,'Answer C',0),(null,2,'Answer D',0),
    (null,3,'Answer A',0),(null,3,'Answer B',0),(null,3,'Answer C',1),(null,3,'Answer D',0),
    (null,4,'Answer A',0),(null,4,'Answer B',0),(null,4,'Answer C',0),(null,4,'Answer D',1);

 */

define('NUM_OF_QUESTIONS_TO_DISPLAY',3);

// DB login data
$db_login = array('host' => 'localhost',
                  'user' => 'test',
                  'pass' => 'tset',
                  'base' => 'test'
                 );

// DB connect
$db = new mysqli($db_login['host'],$db_login['user'],$db_login['pass'],$db_login['base']);

if (mysqli_connect_errno()) 
    trigger_error('DB connection failed: '.mysqli_connect_errno());


// Get questions
if ($res = $db->query('SELECT q.id, q.question FROM questions q 
                       ORDER BY RAND() LIMIT '.(int)NUM_OF_QUESTIONS_TO_DISPLAY.';')) {
    $questions = array();
    $questions_ids = array();
    while ($r = $res->fetch_assoc()) {
        $questions[] = $r;
        $questions_ids[] = $r['id'];
    }
}

// Get the answers
if ($res = $db->query('SELECT a.id_questions, a.answer, a.correct FROM answers a 
                       WHERE (a.id_questions IN ('.implode(',',$questions_ids).')) 
                       ORDER BY RAND();')) {
    $answers = array();

    while ($r = $res->fetch_assoc())
        $answers[$r['id_questions']][] = $r;
}

?>
<html>
    <head>
        <title>Foo!</title>
    </head>
    <body style="margin: 100px;">
        <?php foreach ($questions as $question) : ?>
            <strong><?php echo $question['question']; ?></strong>
            <small>(Question id:<?php echo $question['id']; ?>)</small>
            <ul>
                <?php foreach ($answers[$question['id']] as $answer) : ?>
                    <li><?php echo $answer['answer'].($answer['correct'] ? '(*)' : ''); ?></li>
                <?php endforeach; ?>
            </ul>
        <?php endforeach; ?>
    </body>
</html>

(Not a ready-to-go script, but it should help you out with the stuff you're having trouble with)