How do I retrieve results as multidimensional array from mySQL and PHP?

Question

I'm just starting to learn more advanced SQL along with PHP and I'm really struggling trying to find out how to query my database for a quiz I'm building.

Ultimately, I'm trying to return a json object with the following structure which gives me a list of questions and all possible answers as a multidimensional array:

{
    "questions":
        [
            {
                "question": "question text here",
                "answers":
                    [
                         { "answer": "answer text here", "points": 10 },
                         { "answer": "answer text here", "points": 20 },
                         { "answer": "answer text here", "points": 30 },
                         { "answer": "answer text here", "points": 40 }
                    ]
            },
            {
                "question": "question text here",
                "answers":
                    [
                         { "answer": "answer text here", "points": 10 },
                         { "answer": "answer text here", "points": 20 },
                         { "answer": "answer text here", "points": 30 },
                         { "answer": "answer text here", "points": 40 }
                    ]
            }
        ]
{

...from my mySQL tables of the following structure:

quiz

id | title
1  | quiz title here

quiz_question

id | quiz_id (FK) | question_text
1  |       1      | question text here
2  |       1      | question text here

quiz_answer

id | quiz_question_id (FK) | answer_text      | points
1  |            1          | answer text here |   10
2  |            1          | answer text here |   20
3  |            1          | answer text here |   30
4  |            1          | answer text here |   40

...with the following foreign keys:

quiz_question.quiz_id is FK to quiz.id
quiz_answer.quiz_question_id is FK to quiz_question.quiz_id

...using the following PHP (in it's simplest form which is currently only returning my questions):

//query the db
$query = mysql_query("
    SELECT quiz_question.question_text
    FROM quiz_question
    JOIN quiz ON quiz.id = quiz_question.quiz_id
    WHERE quiz.id = 1;
");

$numrows = mysql_num_rows($query);
for ($i = 0; $i < $numrows; $i++) {
    $row = mysql_fetch_assoc($query);
    $quiz_data[$i] = array("question" => $row["question_text"]);
}

//echo JSON to page
$response = $_GET["jsoncallback"] . "(" . json_encode($quiz_data) . ")";
echo $response;

...and using jQuery's $.getJSON() in my JavaScript which gets my a JSON formatted object from my PHP which gets me back the following:

[
    {"question":"question text here"},
    {"question":"question text here"}
]

So my question is, how can I write my SQL and PHP to create a multidimensional array like the very above instead of a single array like I'm currently getting back now? I need to figure out how to include the questions and all associated answers as a multidimensional array.

Answer 1

You can't retrieve a multi-dimensional array purely with mysql (at least as far as I know). You will have to do some php processing. This doesn't sound too crazy.

First, update your query to select answers at the same time by joining quiz_answers on quiz_questions using the question ID. Then, in your loop:

$quiz = array();
while ($row = mysql_fetch_assoc($result)) {
   // you don't need to check num_rows
   // fetch_assoc returns false after the last row, so you can do this
   // which is cleaner
   if (!isset($quiz[$row['question_id'])) {
      $quiz[$row['question_id']] = array(
         'question' => $row['question_text']
         , 'answers' => array()
      );
   }
   $quiz[$row['question_id']]['answers'][] = $row['answer_text'];
}
$full = json_encode(array('questions' => $quiz'));

This will give you the array you want after it's json encoded.

Note that you will end up selecting the question text/id once per each answer, which is inefficient. You can use GROUP_CONCAT on the answers, but the above will still work almost identically, you just have to split the answer string.

I also suggest you use PDO or some other wrapper over mysql_*.