mankee mankee - 1 year ago 147
JSON Question

Fetch data from mysql into JSON using PHP

I am using the following PHP code to fetch some data from my database. It contains chats and messages between users in those chats. I want to return the information of both users plus the messages they exchanged. My test data has two chats with ID's 1 and 2. There are two messages, both in chat 1, however for some reason they are returned for both chats 1 and 2. I'm not sure what the problem in my code is.

$response = array();

$myArray = array();
while($row = $user_chats->fetch_array())
$myArray["chatId"] = $row["chat_id"];
$myArray["user1_id"] = $row["user1"];
$myArray["user2_id"] = $row["user2"];
$myArray["user1_name"] = $user1_name;
$myArray["user2_name"] = $user2_name;
$myArray["user1_profile_pic"] = $result_user1["profile_pic"];
$myArray["user2_profile_pic"] = $result_user2["profile_pic"];
$messages = array();
$chat_idd = $row["chat_id"];
$chat_messages = mysqli_query($conn,"SELECT * FROM messages WHERE chatID = '$chat_idd' ORDER BY timestamp ASC");
$count = 1;
while($roww = $chat_messages->fetch_array()) {
if ($row["chat_id"] == $roww["chatID"]) {
$messages["message_id"] = $roww["message_id"];
$messages["sender"] = $roww["sender"];
$messages["chatId"] = $roww["chatID"];
$messages["text"] = $roww["text"];
$messages["timestamp"] = $roww["timestamp"];
$myArray["message"][$count] = $messages;
$count = $count + 1;
else {
$myArray["message"]= 0;

$response[] = $myArray;

echo json_encode($response);

produces the following response:

"user2_name":"dqdwdw","user1_profile_pic":"http:\/\/\/dwqwqdqdwdw\/picture?type=large","user2_profile_pic":"WDQdwqwqddqwdqwdq","message":{"1":{"message_id":"24242241","sender":"32132132","chatId":"1","text":"hello i am",
"timestamp":"2016-05-24 17:13:08"},"2":{"message_id":"421421","sender":"32132132",
"chatId":"1","text":"great","timestamp":"2016-05-24 17:15:08"}}},{"chatId":"2","user1_id":"23413524635","user2_id":"32132132","user1_name":false,
WDQdwqwqddqwdqwdq" ,"user2_profile_pic":"http:\/\/\/32132132\/picture?type=large",
"message":{"1":{"message_id":"24242241","sender":"32132132","chatId":"1","text":"hello i am",
"timestamp":"2016-05-24 17:13:08"},"2":{"message_id":"421421","sender":"32132132","chatId":"1",
"text":"great","timestamp":"2016-05-24 17:15:08"}}}]

Answer Source

You need to initialize $myArray at each iteration through the loop, e.g.

while($row = $user_chats->fetch_array()) { $myArray = array();

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download