Mysql/PHP Json 嵌套数组 [英] Mysql/PHP Json nested array

问题描述

我遇到了嵌套数组的问题，由于某种原因，它似乎不是 Json 对象.当我尝试访问例如 jsonData[user"] 时，它可以工作，但是当我尝试更深入时，例如 jsonData[user"][photo_url"] 然后它被当作一个字符串处理，我无法访问该值.

I got issue with nested array which seems are not Json object for some reason. When i try for e.g access jsonData["user"] it works, but when i try go deeper such as jsonData["user"]["photo_url"] then it's treated like a string and i cant access the value.

当前代码:

<?php
    require_once("db_connection.php");

    $userId = $_GET["user_id"];
    $query = "WITH user AS (SELECT id, JSON_OBJECT('display_name', u.display_name, 'photo_url', u.photo_url) AS user FROM users u WHERE id = :userId), info AS (SELECT id, JSON_ARRAYAGG(JSON_OBJECT('text', text, 'start_at', start_at, 'end_at', end_at, 'status', status)) AS information FROM report GROUP BY id), img AS (SELECT report_id, JSON_ARRAYAGG(JSON_OBJECT('name', name)) AS images FROM report_images GROUP BY report_id), cmt AS (SELECT report_id, COUNT(*) AS totalcomments FROM report_comments rc JOIN users u ON rc.user_id = u.id GROUP BY report_id) SELECT u.user, info.information, img.images, cmt.totalcomments FROM report r JOIN user u ON u.id = r.user_id LEFT JOIN info ON info.id = r.id LEFT JOIN img ON img.report_id = r.id LEFT JOIN cmt ON cmt.report_id = r.id";
    $stmt = $db->prepare($query);

    // Bind our variables.
    $stmt->bindValue(":userId", $userId);

    // Execute.
    $stmt->execute();

    $result = $stmt->fetchAll();
    if (count($result) > 0) {
        $toJson = json_encode($result);
        echo $toJson;
    } else {
        $toJson = json_encode("Error");
        echo $toJson;
    }
?>

有效的旧代码:

<?php
    require_once("db_connection.php");
    require_once("functions.php");

    $userId = $_GET["user_id"];
    $query = "SELECT * FROM report WHERE `user_id` = :userId";
    $stmt = $db->prepare($query);

    // Bind our variables.
    $stmt->bindValue(":userId", $userId);

    // Execute.
    $stmt->execute();

    $result = $stmt->fetchAll();
    if (count($result) > 0) {
        foreach ($result as $key => $value) {
            $result[$key]["user"] = getUserById($db, $value["user_id"]);
        }

        $toJson = json_encode($result);
        echo $toJson;
    } else {
        $toJson = json_encode("Error");
        echo $toJson;
    }
?>

推荐答案

因为您将查询中的某些值聚合为 JSON，所以您需要先解码这些值，然后再尝试使用这些值，然后对整个结果进行 JSON 编码放.您需要在获取数据时执行此操作，因此请替换:

Because you are aggregating some of the values as JSON in your query, you need to decode those first before attempting to use the values and then JSON encode the whole result set. You need to do that as you fetch the data, so replace:

$result = $stmt->fetchAll();

与:

$result = array();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $row['user'] = json_decode($row['user']);
    $row['images'] = json_decode($row['images']);
    $row['comments'] = json_decode($row['comments']);
    $result[] = $row;
}

这篇关于Mysql/PHP Json 嵌套数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！