php - 如何从多对多关系中获取数组到对象

Question

我使用 PHP 作为后端和 MySql 数据库，我有这种多对多关系

Drivers{
id,
name
}

Units{
id,
name
}

Driver_Units{
id,
unitId,
driverId
}

当我运行脚本通过驱动程序 ID 获取驱动程序详细信息时，我想要像这样的 json 响应

{
firstName: driver_firstName,
lastName: driver_lastName
units: [unitObject1, unitObject2, unitObject...]
}

所以我想在一个响应中拥有驱动程序对象和他的单元对象数组。我是 PHP 和 MySql 的初学者，我希望有人擅长帮助我处理这个脚本，并告诉我我当前的脚本是否“好”，以及如何获取单元对象数组作为此响应的一部分，以及通常如何我可以改进这个脚本。

编辑:

我尝试了在这个问题中得到的答案，我有这样的代码

<?php
require_once '../../dbConnect.php';

$driver_id = '%';

if (isset($_GET['driver_id'])) {
    $driver_id = $_GET['driver_id'];
}

$driver_id = 3; //hardcode to test it

$stmt = $mysqli->prepare("
    SELECT d.firstName, d.lastName, u.make, u.model FROM drivers AS d
    LEFT JOIN drivers_units AS du ON d.driver_id = du.driverId
    LEFT JOIN units AS u ON u.unit_id = du.unitId
    WHERE d.driver_id = ?");

if($stmt) {
    $stmt->bind_param("i", $driver_id);
    $stmt->execute();
    $stmt->bind_result($first_name, $last_name, $unit_make, $unit_model);
    $driver = null;
    while ($stmt->fetch()) {
        if (empty($driver)) { // On first row populate all the driver info
            $driver = array(
                'firstName' => $first_name,
                'lastName' => $last_name,
                'units' => array()
            );
        }
        if ($unit_name) {
            $driver['units'][] = array(
                'make' => $unit_make,
                'model' => $unit_model
            );
        }
    }

    $mysqli->close();
    echo $json_response = json_encode($driver); 
}

?>

但是这段代码返回了一个 NULL，所以就好像我没有从这个查询中得到任何东西。

Answer 1

加入表格。并学习使用准备好的查询来替代用户输入。

我使用LEFT JOIN，因此即使他没有单位，您也可以获得驾驶员信息。在这种情况下，循环使用 if ($unit_name) 跳过 LEFT JOIN 返回的空单元信息。

$stmt = $mysqli->prepare("
    SELECT d.first_name, d.last_name, d.otherAttribute, u.name, u.attr1 FROM Drivers AS d
    LEFT JOIN Driver_Units AS du ON d.id = du.driverId
    LEFT JOIN Units AS u ON u.id = du.unitId
    WHERE d.id = ?");
$stmt->bind_param("i", $driver_id);
$stmt->execute();
$stmt->bind_result($first_name, $last_name, $driver_other, $unit_name, $unit_other);
$driver = null;
while ($stmt->fetch()) {
    if (empty($driver)) { // On first row populate all the driver info
        $driver = array(
            'firstName' => $first_name,
            'lastName' => $last_name,
            'otherAttribututes' => $driver_other,
            'units' => array()
        );
    }
    if ($unit_name) {
        $driver['units'][] = array(
            'name' => $unit_name,
            'otherAttribute' => $unit_other
        );
    }
}

要获取有关所有驱动程序的信息，请使用二维数组，其中第一级与驱动程序 ID 无关。

$stmt = $mysqli->prepare("
    SELECT d.id, d.first_name, d.last_name, d.otherAttribute, u.name, u.attr1 FROM Drivers AS d
    LEFT JOIN Driver_Units AS du ON d.id = du.driverId
    LEFT JOIN Units AS u ON u.id = du.unitId");
$stmt->execute();
$stmt->bind_result($driver_id, $first_name, $last_name, $driver_other, $unit_name, $unit_other);
$drivers = array();
while ($stmt->fetch()) {
    if (empty($drivers[$driver_id])) { // On first row populate all the driver info
        $drivers[$driver_id] = array(
            'firstName' => $first_name,
            'lastName' => $last_name,
            'otherAttribututes' => $driver_other,
            'units' => array()
        );
    }
    if ($unit_name) {
        $drivers[$driver_id]['units'][] = array(
            'name' => $unit_name,
            'otherAttribute' => $unit_other
        );
    }
}