iDev iDev - 19 days ago 5
MySQL Question

MySQL fetch 2 related tables into sub array

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

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);