mysql return multi level array

multidimensional-array, mysql, php

Solution

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");


$query = "SELECT testname, question, answer
    FROM tests 
    JOIN questions ON (tests.id = question.test_id)
    JOIN answers ON (questions.id = answers.id)
    WHERE 'your condition'
    ORDER BY tests.id, question.id"

$result = $mysqli->query($query);

You can use in_array function to get your desired result (you can make array or print result). Here is an example of printing values. (you can use id instead of name for betterment)

$tests_arr = array();
$questions_arr = array();

while( $row = $result->fetch_array(MYSQLI_ASSOC) )
{
    if(!in_array($row['testname'], $test_arr)
    {
        $test_arr[] = $row['testname'];
        echo $row['testname'];
    }

    if(!in_array($result['question'], $question_arr))
    {
        $questions_arr[] = $row['question'];
        echo $row['question'];
    }

    echo $row['answer'];
}

Problem

I'm working with three MySQL tables that are related in a PHP application. There are tests which have many questions, and the questions have many answers. What I would like to do is loop through the data as follows: ``` foreach($tests as $test) { echo $test->testName; foreach($test->questions as $question) { echo $question->questionText; foreach($question->answers as $answer) { echo $answer->answerText; } } } ``` What I would like to know is what the MySQL query and PHP code would be to loop through it in this manner? edit MySQL can't return arrays like this, what I should have said is what would the MySQL + PHP code look like. For clarity, the tables are tests, questions and answers. The questions table contains a test_id column, and the answers contains a question_id Thanks! The structure I'm looking to get back would be something along the lines of: ``` array( 'testName' = 'Test name string', 'questions' = array( array( 'questionId' = 1, 'questionText' = 'Question string', 'answers' = array( array( 'answerId' = 1, 'answerText' = 'Answer string' ), array( 'answerId' = 2, 'answerText' = 'Answer string' ) ) ) ) ); ``` edit My current implementation is as follows, what I wanted to do was eager load the data rather than perform so many queries ``` $tests = getTests(); foreach($tests as $test){ $questions = getQuestions($test->id); foreach($questions as $question){ $answers = getAnswers($question->id); foreach($answers as $answer){ // do answer things } } } ```

Original source