php - 如何修复php数组格式json错误

Question

这是我的 mysql 脚本

$result = mysql_query("SELECT users.*,games.game_names,games.id AS games_id
FROM users
LEFT JOIN games ON FIND_IN_SET(games.id,users.games)") or die(mysql_error());   
while($user = mysql_fetch_array($result, MYSQL_ASSOC)) {        
    $array = $user['games'];
    $name   =   $user['game_names'];
    $pieces = explode(",", $name);          
    $users[] = array(
                'users'=>array(
                    'id' => $user['id'],
                    'user' => $user['user'],
                    'games' =>  $pieces
                )
    );      

}   
$output = json_encode(array('statics' => $users));
echo $output;

我有 2 个用户。 user1 有 4 款游戏。用户 2 有 3 款游戏。

我正在

{
statics: [
    {
        users: {
            id: "1",
            user: "user1",
            games: [
                "football"
            ]
        }
    },
    {
        users: {
            id: "1",
            user: "user1",
            games: [
                "cricket"
            ]
        }
    },
    {
    users: {
        id: "1",
            user: "user1",
            games: [
                "athletics"
            ]
        }
    },
    {
        users: {
            id: "1",
            user: "user1",
            games: [
                "badminton"
            ]
        }
    },
    {
        users: {
            id: "2",
            user: "user2",
            games: [
                "athletics"
            ]
        }
    },
        {
        users: {
            id: "2",
            user: "user2",
            games: [
                "badminton"
            ]
        }
    },
    {
        users: {
            id: "2",
            user: "user2",
            games: [
                "basketball"
            ]
        }
    }
]
}

但我必须排序并需要以下格式

    {
statics: [
    {
        users: {
            id: "1",
            user: "user1",
            games: [
                "football",
                "cricket",
                "athletics",
                "badminton"
            ]
        }
    },        
    {
        users: {
            id: "2",
            user: "user2",
            games: [
                "athletics",
                "badminton",
                "basketball"
            ]
        }
    }

]
}

我应该如何准备查询才能获得此结果？

Answer 1

试试这个代码:

$result = mysql_query("SELECT users.*,games.game_names,games.id AS games_id
FROM users
LEFT JOIN games ON FIND_IN_SET(games.id,users.games)") or die(mysql_error());   
$result_array=array();
while($user = mysql_fetch_array($result, MYSQL_ASSOC)) {        
    $name   = $user['game_names'];
    $pieces = explode(",", $name);
    array_push($result_array, array(
        'users'=>array(
            'id' => $user['id'],
            'user' => $user['user'],
            'games' =>  $pieces
        )
    ));
}   
$output = json_encode(array('statics' => $result_array));
echo $output;