MySQL fetch 2 related tables into sub array

Question

I have a MySQL database that contains the following tables:

Student table that looks like that:

|------------------------------------|
| studentId | studentName | school   |
|------------------------------------|
| 1         | Student A   | 1        |
| 2         | Student B   | 2        |
| 3         | Student C   | 2        |
|------------------------------------|

And a School table:

|------------------------------|
| schoolId | schoolName | desc |
|------------------------------|
| 1        | School A   | ...  |
| 2        | School B   | ...  |
| 3        | School C   | ...  |
|------------------------------|

Using PHP, I'm trying to fetch an array of all students and include their school information in a sub array.

Array
(
    [0] => Array
        (
            [studentId] => 1
            [studentName] => Student A
            [school] => Array
                (
                    [schoolId] => 1
                    [schoolName] => School A
                    [desc] => ...
                )
        )
    [1] => Array
        (
            [studentId] => 1
            [studentName] => Student A
            [school] => Array
                (
                    [schoolId] => 1
                    [schoolName] => School A
                    [desc] => ...
                )
        )
    [2] => Array
        (
            [studentId] => 1
            [studentName] => Student A
            [school] => Array
                (
                    [schoolId] => 1
                    [schoolName] => School A
                    [desc] => ...
                )
        )
)

I was able to achieve that by doing this

$studentsResult = $conn->query("SELECT * FROM STUDENT");
$studentsArray = $studentsResult->fetch_all(MYSQLI_ASSOC);

$finalArray = array();

foreach ($studentsArray as &$student) { 
    $schoolSQL = "SELECT * FROM SCHOOL WHERE schoolId = ".$student['schoolId'];
    $schoolResult = $conn->query($schoolSQL);
    $schoolArray = $schoolResult->fetch_all(MYSQLI_ASSOC);

    unset($student['schoolId']);
    $student['school'] = $schoolArray[0];
    $finalArray[] = $student;
}
echo '<pre>'; print_r($finalArray);

But I don't think this way is the most efficient for a large database since I'm looping through all the students and executing a query to get school row.

Is there anyway I can do all that by using only SQL?

Answer 1

You should read on joining, very simple to use.

SELECT a.studentName, b.schoolName, b.desc
FROM students AS a
INNER JOIN school AS b
ON a.school = b.schoolId

This way, you combine all the data from multiple tables as 1 result.

echo $row['studentName'], ' is in this school ', $row['schoolName'], ' etc..';

Answer 2

Do a join, then loop around only adding a student when the student changes. But just add the school details to the students school array.

Something like this

<?php

$studentsResult = $conn->query("SELECT a.studentId,
                                        a.studentName,
                                        b.schoolId,
                                        b.schoolName,
                                        b.desc
                                FROM STUDENT a
                                LEFT OUTER JOIN SCHOOL b
                                ON a.schoolId = b.schoolId");

$prev_student = 0;
$studentsArray = $studentsResult->fetch_all(MYSQLI_ASSOC);

$finalArray = array();

foreach ($studentsArray as &$student) 
{ 
    if ($prev_student != $student['studentId'])
    {
        $finalArray[] = array('studentId' => $student['studentId']
                                'studentName' => $student['studentName']
                                'school' => array());
        $prev_student = $student['studentId'];
    }
    $finalArray[key($finalArray)][] = array('schoolId ' => $student['schoolId']
                                            'schoolName ' => $student['schoolName']
                                            'desc ' => $student['desc']);
}

echo '<pre>'; print_r($finalArray);