php - 根据特定的 mysql 列对 JSON 输出进行分组

Question

我有一个 mysql 表 (name:"messages")，它有如下三列:

messageID, fromUserID, content

我希望使用如下格式的 php 脚本输出 json；我需要将每个用户的消息分开(fromUserID 列)。

JSON 输出:

{
"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"}                
        ]
    }
]
}

我的 PHP 脚本:

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

但是使用上面的脚本我给出了如下错误的结果:

{
"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"}
  ]
 }
]
}

我的 PHP 脚本只使用最后一个 fromUserID 值来分组消息!!!

请让我知道哪里错了...

Answer 1

尝试一下

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