How to fix php array format json error

Question

This is my mysql script

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

I have 2 users. user1 have 4 games. User2 have 3 games.

I am getting

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

But i have to sort and need in following format

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

]
}

how should i prepare my query to get this result?

Answer 1

try this code:

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