Create JSON in PHP

Question

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

{"Kako si mama"},{"kako si tata"} it is not a json and you can not create it by json_encode. Only by direct manipulation with the string. — Cheery
– Cheery, Commented Oct 16, 2014 at 20:12
@Cheery Good catch. He probably meant "Kako si mama","kako si tat" as a elements of a string array, and not as objects. OP can you confirm/deny? — thatidiotguy
– thatidiotguy, Commented Oct 16, 2014 at 20:13
JSON requires a {key:value} pair, so {key} is invalid. I would have your strings in an array only, as such: ["Kako si mama","kako si tata", "kako si brate", "kako si sestro"]. A json library should be able to do that for you. — NuclearPeon
– NuclearPeon, Commented Oct 16, 2014 at 20:14
could value ["Kako si mama","kako si tata", "kako si brate", "kako si sestro"] be json with key message. If I put $post["message"] = array() insted $post["message"] = $row["message"], then put foreach loop, i get what I want, but then I get as many as I have usernames in database. Is there solution to get only one username with all messages — marko kurt
– marko kurt, Commented Oct 16, 2014 at 20:21

Adam Fentosi · Accepted Answer · 2014-10-16 20:15:30Z

1

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

answered Oct 16, 2014 at 20:15

Adam Fentosi

1,26811 silver badges10 bronze badges

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

2 Comments

marko kurt Over a year ago

Looks good, but what if I want JSONArray where I will have for every message key for that message? Now it prints "message":"message":["Kako si mama","kako si tata","kako si brate","kako si sestro"]. What you will change in code to print

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

. Do you have any idea?

Adam Fentosi Over a year ago

As you can see here: ideone.com/fork/Mx3qGq, it's work, maybe there is something different in your $rows array, use print_r() to check out!

Marc B · Accepted Answer · 2014-10-16 20:15:09Z

0

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', ....)
);

answered Oct 16, 2014 at 20:15

Marc B

362k44 gold badges433 silver badges508 bronze badges

1 Comment

marko kurt Over a year ago

returns null. Somthing like [].

meda · Accepted Answer · 2014-10-16 20:22:54Z

0

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.

edited Oct 16, 2014 at 20:22

answered Oct 16, 2014 at 20:16

meda

45.5k14 gold badges96 silver badges123 bronze badges

Collectives™ on Stack Overflow

Create JSON in PHP

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related