在PHP中创建JSON

Hy guys and girls :). Here is my problem. I have two tables in database(users and comments). One user can post 1 or more comments. I want to retreive data from database in JSON format. When I retrive data, I get format like this

[{"username":"kurtMarko","message":"Kako si mama"},{"username":"kurtMarko","message":"kako si tata"},{"username":"kurtMarko","message":"kako si brate"},{"username":"kurtMarko","message":"kako si sestro"}]

but I want to retrive data like

[{"username":"kurtMarko","message":[{"Kako si mama"},{"kako si tata"},{"kako si brate"},{"kako si sestro"}]]

Do you have any idea, suggestions. Every comment will help me. Thank you very much. Here is my code

require("config.inc.php");
$query = "SELECT comments.message, users.username
            FROM comments
            LEFT JOIN users ON users.username = comments.username";

try {

    $stmt = $db->prepare($query);
    $result = $stmt->execute();

} catch(PDOException $ex){

    $response["success"] = 0;
    $response["message"] = "Database Error!";
    die(json_encode($response));

}

$rows = $stmt->fetchAll();
if ($rows) {
    $response   = array();
    foreach ($rows as $row) {
        $post["username"]= $row["username"];
        $post["message"] = $row["message"];
        array_push($response, $post);
    }
    echo json_encode($response);
} else {
    $response["success"] = 0;
    $response["message"] = "No Post Available!";
    die(json_encode($response));
}

You have to make different array for that like this below:

$response   = array();
foreach ($rows as $row) {
    $response[$row["username"]]['message'][] = $row["message"]
}
echo json_encode($response);

Maybe you have to initialize for the first

Your desired DB structure makes this difficult. Since username isn't a key in the array, you'd have to repeatedly search the entire array to find WHERE a particular username is, just so you can append a new message under that user. Why not have this instead:

$arr = array();
foreach($rows as $row) {
    $user = $row['username'];
    $msg = $row['message'];
    $arr[$user][] = $msg;
}

That would give you:

Array (
    'Fred' => array('msg1', 'msg2', 'msg3');
    'Susie' => array('msg4', 'msg5', ....)
);

To be honest with you the first version you don't like is actually the better one.

You want to group records by objects.

If the values are repeated its not a problem it does not hurt.

It might be tempting to shorten your JSON string by removing redundant values but this will make it more difficult to manipulate your object.

This is the whole point of json (JavaScript Object Notation), in your example each comment represent an object and that's a perfect object graph, don't modify it.