php - MySQL获取2个相关表到子数组中

Question

我有一个 MySQL 数据库，其中包含以下表格:

Student 表如下所示:

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

还有一个学校表:

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

使用 PHP，我尝试获取所有学生的数组，并将他们的学校信息包含在子数组中。

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] => ...
                )
        )
)

我通过这样做实现了这一目标

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

但我认为这种方式对于大型数据库来说不是最有效的，因为我循环遍历所有学生并执行查询来获取学校行。

我是否可以仅使用 SQL 来完成所有这些工作？

Answer 1

进行连接，然后循环仅在学生发生变化时添加学生。但只需将学校详细信息添加到学生学校数组中即可。

类似这样的事情

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