Extract a joined result set into a parent-child hierarchy

I'm writing a quiz application in PHP and am querying the DB for questions and their associated answers. I then wrangle the result set into a usable array for my front end. However, it always seems like a wrestling match and I'm wondering if I could have got to my desired array structure more efficiently.

What I wanted was an associative array / dictionary which was structured so that the questions were at the top level with their associated answers underneath. I also wanted a numeric index above the question level so that I can later use that to match my current_step session variable for stepping through the form.

```
function get_question_data( $quiz_id)
{
global $wpdb;

if ( !isset($quiz_id)) {
return FALSE;
}

$sql = $wpdb->prepare("SELECT q.ID AS 'question_id', question, explanation, q.sort_order, qa.ID AS 'answer_id', qa.answer, qa.correct, qa.hint
FROM {$wpdb->prefix}my_quiz_questions q
LEFT JOIN wp_nmsi_quiz_answers AS qa ON q.ID = qa.question_id
WHERE quiz_id=%d ORDER BY q.ID", $quiz_id);
$quiz_data = $wpdb->get_results($sql, ARRAY_A);
fb($quiz_data,'DATABASE RESULTS');

//build into a user-friendly array which we can use to manage quiz steps and questions later.
$question_array = array();

foreach ($quiz_data as $key=>$value) {

foreach ($value as $k => $v) {
if ($k == 'question' ) {
if (!array_key_exists('question_'.$value['question_id'], $question_array)) {
$question_array['question_'.$value['question_id']]['question_text'] = $v;
$question_array['question_'.$value['question_id']]['question_id'] = $value['question_id'];
}
}

if ($k == 'answer'){
$question_array['question_'.$value['question_id']]['answers'][$value['answer_id']]['text'] = $v;
}

if ($k == 'hint') {

First Cut: Since every row contains the same set of fields, you can clean up the code considerably by skipping the inner loop.

$question_array = array();
foreach ($quiz_data as $row) {
    $qId = $row['question_id'];
    $aId = $row['answer_id'];
    $answer = array(
            'id' => $aId,
            'text' => $row['answer'],
            'hint' => $row['hint'],
            'correct' => $row['correct'],
        );
    if (!array_key_exists($qId, $question_array)) {
        $question_array[$qId] = array(
                'id' => $qId,
                'text' => $row['question'],
                'explanation' = $row['explanation'],
                'sort' = $row['sort_order'],
            );
    }
    $question_array[$qId]['answers'][$aId] = $answer;
}

Second Cut: Use objects instead of arrays. While it may seem to have little payoff at first, as you start performing more complex operations on questions and answers you'll gain much from the encapsulation.

$question_array = array();
foreach ($quiz_data as $row) {
    $qId = $row['question_id'];
    $answer = new Answer($row['answer_id'], $row['answer'], $row['hint'], $row['correct']);
    if (!array_key_exists($qId, $question_array)) {
        $question_array[$qId] = $question 
                = new Question($qId, $row['question'], $row['explanation'], $row['sort_order']);
    }
    else {
        $question = $question_array[$qId];
    }
    $question->addAnswers($answer);
}

Here are the most basic definitions for the Question and Answer classes to get you started.

class Question {
    private $id;
    private $text;
    private $explanation;
    private $sortOrder;
    private $answers = array();
    public function __construct($id, $text, $explanation, $sortOrder) {
        $this->id = $id;
        $this->text = $text;
        $this->explanation = $explanation;
        $this->sortOrder = $sortOrder;
    }
    public addAnswer(Answer $answer) {
        $this->answers[$answer->getId()] = $answer;
    }
    ... property accessors and other methods ...
}

class Answer {
    private $id;
    private $text;
    private $hint;
    private $correct;
    public function __construct($id, $text, $hint, $correct) {
        $this->id = $id;
        $this->text = $text;
        $this->hint = $hint;
        $this->correct = $correct;
    }
    ... property accessors and other methods ...
}

StackExchange Code Review Q#692, answer score: 4