Grouping JSON output according to specific mysql column

Question

I have a mysql table (name:"messages") that has three columns as below:

messageID, fromUserID, content

I wish to have a json output using php script like following format; I need to seprate messages of each user (fromUserID column).

JSONOutput:

{
"newCount":"x",
"messages":
[
    {
        "fromUserID":"x",
        "messagesArray":
        [
            {"messageID":"x","content":"xxx"},
            {"messageID":"x","content":"xxx"},
            {"messageID":"x","content":"xxx"}                
        ]
    },
    {
        "fromUserID":"y",
        "messagesArray":
        [
            {"messageID":"x","content":"xxx"},
            {"messageID":"x","content":"xxx"},
            {"messageID":"x","content":"xxx"}                
        ]
    },
    {
        "fromUserID":"z",
        "messagesArray":
        [
            {"messageID":"x","content":"xxx"},
            {"messageID":"x","content":"xxx"},
            {"messageID":"x","content":"xxx"}                
        ]
    }
]
}

My PHP Script:

$query = mysqli_query($con,"SELECT * FROM messages ORDER BY fromUserID");

$outputArray = array();

$outputArray['hasNew'] = mysqli_num_rows($query);

$messagesArray = array();

if($query)
{
    while($row = mysqli_fetch_assoc($query))
    {
        $MSGArray = array();
        $messagesArray['fromUserID'] = $row['fromUserID'];
        $MSGArray['messageID'] = $row['messageID'];
        $MSGArray['content'] = $row['content'];
        $messagesArray['MessagesArray'][] = $MSGArray;      
    }
    $outputArray['Messages'][] = $messagesArray;
}

echo json_encode($outputArray);

But with above script I give a wrong result as below:

{
"hasNew":6,
"Messages":
[
 {
  "fromUserID":"24",
  "MessagesArray":
  [
   {"messageID":"4","content":"test"},
   {"messageID":"3","content":"test"}, 
   {"messageID":"6","content":"test"},
   {"messageID":"5","content":"test"},
   {"messageID":"1","content":"test"},
   {"messageID":"2","content":"test"}
  ]
 }
]
}

My PHP Script just using last fromUserID value to grouping messages !!!

Please let me know where I'm wrong ...

Answer 1

Try it

if($query)
{
    while($row = mysqli_fetch_assoc($query))
    {
        $MSGArray = array();
        $messagesArray[$row['fromUserID']]['fromUserID'] = $row['fromUserID'];
        $MSGArray['messageID'] = $row['messageID'];
        $MSGArray['content'] = $row['content'];
        $messagesArray[$row['fromUserID']]['MessagesArray'][] = $MSGArray;      
    }
foreach($messagesArray as $value) {
    $outputArray['Messages'][] =  $value;
}
}