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 Resultsarray(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  }}
Extract a joined result set into a parent-child hierarchy
php;sql;array
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 ...}