Mysql/PHP Json nested array

Question

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.

Current code:

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

Old code which worked:

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

Nick · Accepted Answer · 2021-01-06 21:46:52Z

1

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

with:

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

answered Jan 6, 2021 at 21:46

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Printer Over a year ago

I suspected i needed a loop through, but i was not sure. You're such a legend. It works beautifully

Nick Over a year ago

@Printer good to hear - I just needed to be able to see the PHP code to be able to figure out where to put the decodes.

Printer Over a year ago

I understand, once again thank you for the help :)

Collectives™ on Stack Overflow

Mysql/PHP Json nested array

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related