PHP中MySQL查询中的多个嵌套数组

此代码还有很大的改进空间，但我忽略了这一点，并试图在此示例中保持代码与您的代码匹配。

主要变化是：

• 创建一个临时变量，我们存储“plant_images”$user_plant_array
• 将该临时变量推送到循环末尾$site_array
• 重命名一些循环变量，以便更轻松地识别您正在引用的内容

$json_response = array();
$sites = array();
if ($result->num_rows > 0) {

    while ($site = $result->fetch_object()) {
        $sites[] = $site;
    }

    foreach ($sites as $site) {
        $site_array = (array)$site;
        $site_id = $site->site_id;

        $user_plants = "SELECT A.user_plant_id, A.site_id, A.plant_id FROM user_plants A RIGHT JOIN sites B ON A.site_id ='" . $site_id . "' 
            JOIN plants C ON A.plant_id = C.plant_id GROUP BY A.user_plant_id";
        $resultSet = $this->conn->query($user_plants);

        $user_plants = array();
        if ($resultSet->num_rows > 0) {

            while ($user_plant = $resultSet->fetch_object())
                $user_plants[] = $user_plant;

            foreach ($user_plants as $user_plant) {
                // create a temporary variable here that we will map
                // all "plant_images" to
                $user_plant_array = (array)$user_plant;

                $plant_id = $user_plant->plant_id;
                $user_plant_id = $user_plant->user_plant_id;


                $plant_images = "SELECT A.plant_image_id FROM plants_images A WHERE A.plant_id ='" . $plant_id . "' UNION SELECT B.plant_image_id FROM user_plant_image B JOIN user_plants C ON B.user_plant_id ='" . $user_plant_id . "' WHERE C.user_id ='" . $user_id . "' GROUP BY B.plant_image_id ORDER BY plant_image_id";
                $resultSet = $this->conn->query($plant_images);

                $plant_images = array();
                if ($resultSet->num_rows > 0) {

                    while ($plant_image = $resultSet->fetch_object())
                        $plant_images[] = $plant_image;


                    foreach ($plant_images as $plant_image) {
                        $user_plant_array['plant_images'][] = $plant_image;
                    }
                    
                } else if ($resultSet->num_rows == 0) {
                    $user_plant_array['plant_images'] = [];
                }
                
                // the temporary variable now contains all "plant_images"
                // now we can push that to the site array
                $site_array['user_plants'][] = $user_plant_array;
            }

            $json_response[] = $site_array;
        }
    }
}

return $json_response;

创建一个单独的答案作为替代解决方案，并改进了一些代码。

“改进”是更高的可读性和/或更高的性能。

在此示例中，我建议作为“改进”进行的一些主要更改已实现。主要有：

• 使用准备好的 SQL 语句（并不总是必需的，但使用良好的做法，尤其是在接受用户输入的任何内容中，也可以使代码更简洁）
• 减少循环数量（在一些地方，您只是为了创建一个数组而循环，然后再次循环）
• 尽可能尽早返回/继续（有助于防止不必要的嵌套）
• 删除不必要的 if 语句（例如，如果结果为空，将跳过大多数 while 循环 - 事先检查并非完全必要）
• 更易读的变量名称（对于新编码人员来说，尝试缩写很多变量是很常见的，并且经常走得太远 - 使它们可读将在调试时节省大量时间）

使用的代码可能不是最好的，因为我通常使用.mysqliPDO

function getSitesData() {
    // assumes that $user_id is set somewhere before this
    // assumes that $this->conn references a valid database connection

    $sql = "SELECT A.site_id FROM sites A WHERE A.user_id = ? GROUP BY A.site_id";

    $query = $this->conn->prepare($sql);
    $query->bind_param("i", $user_id);
    $query->execute();

    $site_result = $query->get_result();

    $sites = [];
    while ($site = $site_result->fetch_assoc()) {
        // using fetch_assoc gives us an associative array

        // initialise empty array
        $site["user_plants"] = [];

        // get user_plants
        $sql = "SELECT A.user_plant_id, A.site_id, A.plant_id FROM user_plants A RIGHT JOIN sites B ON A.site_id = ? 
            JOIN plants C ON A.plant_id = C.plant_id GROUP BY A.user_plant_id";

        $query = $this->conn->prepare($sql);
        $query->bind_param("i", $site["site_id"]);
        $query->execute();

        $user_plant_result = $query->get_result();
        while ($user_plant = $user_plant_result->fetch_assoc()) {
            // intialise empty array
            $user_plant["plant_images"] = [];

            // get plant images
            $sql = "SELECT A.plant_image_id FROM plants_images A WHERE A.plant_id = ? UNION SELECT B.plant_image_id FROM user_plant_image B JOIN user_plants C ON B.user_plant_id = ? WHERE C.user_id = ? GROUP BY B.plant_image_id ORDER BY plant_image_id";

            $query = $this->conn->prepare($sql);
            $query->bind_param("iii", $user_plant["plant_id"], $user_plant["user_plant_id"], $user_id);
            $query->execute();

            $plant_image_result = $query->get_result();
            while ($plant_image = $plant_image_result->fetch_assoc()) {
                $user_plant["plant_images"][] = $plant_image;
            }

            $sites["user_plants"][] = $user_plant;
        }

        $sites[] = $site;
    }

    return $sites;
}