Extract a joined result set into a parent-child hierarchy

Question

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') {
                $question_array['question_'.$value['question_id']]['answers'][$value['answer_id']]['hint'] = $v;
            }

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


            if ($k == 'explanation' ) {
                $question_array['question_'.$value['question_id']]['explanation'] = $v;
            }

        }


    }

    //echo $wpdb->last_query;
    return array_values($question_array);
}

I ended up with this solution.

Update: Query Results

array(6) {
  [0]=>
  array(8) {
    ["question_id"]=>
    string(1) "1"
    ["question"]=>
    string(34) "Question 1. What is the question ?"
    ["explanation"]=>
    string(38) "This is the explanation for question 1"
    ["sort_order"]=>
    string(1) "0"
    ["answer_id"]=>
    string(2) "20"
    ["answer"]=>
    string(16) "this is answer 4"
    ["correct"]=>
    string(1) "0"
    ["hint"]=>
    string(29) "this is the hint for answer 4"
  }
  [1]=>
  array(8) {
    ["question_id"]=>
    string(1) "1"
    ["question"]=>
    string(34) "Question 1. What is the question ?"
    ["explanation"]=>
    string(38) "This is the explanation for question 1"
    ["sort_order"]=>
    string(1) "0"
    ["answer_id"]=>
    string(2) "19"
    ["answer"]=>
    string(16) "this is answer 3"
    ["correct"]=>
    string(1) "0"
    ["hint"]=>
    string(29) "this is the hint for answer 3"
  }
  [2]=>
  array(8) {
    ["question_id"]=>
    string(1) "1"
    ["question"]=>
    string(34) "Question 1. What is the question ?"
    ["explanation"]=>
    string(38) "This is the explanation for question 1"
    ["sort_order"]=>
    string(1) "0"
    ["answer_id"]=>
    string(2) "18"
    ["answer"]=>
    string(16) "this is answer 2"
    ["correct"]=>
    string(1) "0"
    ["hint"]=>
    string(29) "this is the hint for answer 2"
  }
  [3]=>
  array(8) {
    ["question_id"]=>
    string(1) "1"
    ["question"]=>
    string(34) "Question 1. What is the question ?"
    ["explanation"]=>
    string(38) "This is the explanation for question 1"
    ["sort_order"]=>
    string(1) "0"
    ["answer_id"]=>
    string(2) "17"
    ["answer"]=>
    string(16) "this is answer 1"
    ["correct"]=>
    string(1) "1"
    ["hint"]=>
    string(29) "this is the hint for answer 1"
  }
  [4]=>
  array(8) {
    ["question_id"]=>
    string(1) "2"
    ["question"]=>
    string(10) "Question 2"
    ["explanation"]=>
    string(26) "Explanation for question 2"
    ["sort_order"]=>
    string(1) "0"
    ["answer_id"]=>
    string(2) "24"
    ["answer"]=>
    string(28) "test answer 2 for question 2"
    ["correct"]=>
    string(1) "0"
    ["hint"]=>
    string(13) "answer 2 hint"
  }
  [5]=>
  array(8) {
    ["question_id"]=>
    string(1) "2"
    ["question"]=>
    string(10) "Question 2"
    ["explanation"]=>
    string(26) "Explanation for question 2"
    ["sort_order"]=>
    string(1) "0"
    ["answer_id"]=>
    string(2) "23"
    ["answer"]=>
    string(28) "test answer 1 for question 2"
    ["correct"]=>
    string(1) "1"
    ["hint"]=>
    string(13) "answer 1 hint"
  }
}

Answer 1

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 ...
}

Answer 2

From the structure of the sql query, it looks like your output is going to have duplicate rows, due to the fact that a single question can have multiple answers. This means you're going to have to do some screwy stuff, like what you did with the nested foreach loops. I would recommend splitting this into two separate queries, one to get the questions and one to get the answers, and then just assigning the responses straight into the array without all the if(key==whatever) { // assign something } magic.

As for the array itself, it looks quite good. Some pretty minor points:

1. question_array doesn't really need to have a string-based key (e.g., $question_array['question_1']); it may be easier to work with if you just use numbers (e.g., $question_array[1]).

2. You don't have a field for how many points each question is worth.