# 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') {$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"
string(2) "20"
["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"
string(2) "19"
["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"
string(2) "18"
["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"
string(2) "17"
["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"
string(2) "24"
string(28) "test answer 2 for question 2"
["correct"]=>
string(1) "0"
["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"
string(2) "23"
string(28) "test answer 1 for question 2"
["correct"]=>
string(1) "1"
["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 ... } • Wow, thanks David. That is definitely more succinct and presumably more performant without the extra loop. I plan to release the quiz code as a plugin at some point. I will definitely consider encapsulating the answers and questions in objects during refactoring. – codecowboy Feb 11 '11 at 6:17 • Would this be a candidate for dependency injection do you think? A quiz would contain several question objects which in turn would contain several answer objects – codecowboy Feb 11 '11 at 6:31 • @codecowboy - DI is more geared toward configuring system services such as a Data Access Object with a DB connection or an API Service with a REST Connection Manager which probably doesn't mean much to you now. It's a tougher sell (for me) in PHP since the PHP processes are short lived. In a Java web application you can build all of the necessary services and reuse them. With PHP you must build them for every request so you want to build only what you'll use. I haven't investigated DI libraries for PHP yet, however. – David Harkness Feb 11 '11 at 7:14 • Thanks again. You could take a look at components.symfony-project.org/dependency-injection – codecowboy Feb 11 '11 at 18:28 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.

• Thanks for the reply:) It does have some duplication of data but not duplicate rows. My motivation for doing it in one query was to try and minimise the number of hits on the DB. I realise that for a simple quiz this probably isn't an issue but like to try and employ 'best practice' to improve my programming. Thanks for your additional pointers re the array. – codecowboy Feb 9 '11 at 7:06