mysql return multi level array

Question

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

        }

    }

}

that would require the use of a LEFT JOIN in my opinion maybe? — Zathrus Writer
– Zathrus Writer, Commented Aug 22, 2012 at 12:14

nik · Accepted Answer · 2012-08-22 13:05:08Z

3

$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'];
}

edited Aug 22, 2012 at 13:05

answered Aug 22, 2012 at 12:28

nik

3,6783 gold badges24 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

newfurniturey Over a year ago

As the mysql_* functions are no longer recommended for use, please do not recommend their usage in answers that the OP does not show they use them in. Instead, recommend usage of Mysqli or PDO methods.

nik Over a year ago

Oh, thanks newfurniturey. Didn't knew that. Will change this to mysqli instead

newfurniturey · Accepted Answer · 2012-08-22 12:23:54Z

MySQL can't return an array structure in the format you desire - the best it can do is a 2D-array to represent the columns/rows.

Two alternatives would be both implemented in PHP.

Run a query for each test; iterate through each test and run a query to get all questions for this test; iterate through each question and run a query for the answers for this question.

Example:

// get each test
$tests = array();
$results = $mysqli->query('SELECT id, name FROM tests;');
while ($test = $results->fetch_object()) $tests[] = $test;
foreach ($tests as $t => $test) {
    // get all questions for this test
    $tests[$t]['questions'] = array();
    $results = $mysqli->query('SELECT id, name FROM questions WHERE test_id = ' . $test['id']);
    while ($question = $results->fetch_object()) $tests[$t]['questions'][] = $question;
    foreach ($tests[$t]['questions'] as $q => $question) {
        // get all answers for this question
        $tests[$t]['questions'][$q]['answers'] = array();
        $results = $mysqli->query('SELECT id, name FROM answers WHERE question_id = ' . $question['id']);
        while ($answer = $results->fetch_object()) $tests[$t]['questions'][$q][] = $answer;
    }
}

2. Run a single query using MySQL joins and pre-iterate through the list of results to build an array in the format you desire.

Ivan Pintar · Accepted Answer · 2012-08-22 12:33:17Z

In plain SQL you could do a join, but that would get you flattened rows, the number of rows would be tests * questions * answers. You'd then have to parse and map the properties to appropriate objects. Something like this:

$sql = "SELECT * FROM tests t, questions q, answers a  WHERE q.t_id = t.id AND a.q_id = q.id";
$rows = // do your fetching, pdo, mysqli, whatever, just get the rows

$tests = array();
foreach($rows as $row) {
    // map test
    if(!isset($tests[$row['t_id']])) $tests[$row['t_id']] = array(
       // set test attributes here (without the questions)
    );

    // map question
    if(!isset($tests[$row['t_id']]['questions'][$row['q_id']])) = array(
       // set question attributes here (without the answers)
    );

    // map answer
    if(!isset($tests[$row['t_id']]['questions'][$row['q_id']]['answers'][$row['a_id']])) = array(
       // set answer attributes here 
    );
}

The $tests array should now have the hierarchy you required, but be sure to test this, but even if it doesn't work, you get the idea.

Having said that, take a look at some ORM-s. For one of the simplest (in a good way) take a look at this: http://phpmaster.com/database-interaction-made-easy-with-notorm/

The tutorial is quite easy to go through, and in the end you get just what you asked, a set of objects with properties and a nice hierarchy based on table relations.

J V · Accepted Answer · 2012-08-22 12:20:49Z

0

You would LEFT JOIN so that the results are something like:

test1|question1|answer1
test1|question1|answer2
test1|question2|answer1
test1|question2|answer2
test2|question1|answer1

Then you loop through it in such a way that they always come in that order, so write a loop that fetches a row, if the test has changed since the last one, move to the next test in the array. Repeat for question and finally add the answers.

Some pseudocode

fetcharray()
if test > lasttest
  arraytest++
  arrayquestion = 0
elif question > lastquestion
  arrayquestion++

array[arraytest][arrayquestion][] = fetched array

answered Aug 22, 2012 at 12:20

J V

12.1k10 gold badges56 silver badges78 bronze badges

Collectives™ on Stack Overflow

mysql return multi level array

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related