php将mysql转换为json字符串,使用PHP将MySQL结果转换为JSON

I've seen lots of similar question on here, but I haven't been able to get my desired output. Could someone please help me out? How can generate the following JSON structure? My query is fine, I just can't figure out how to loop through and get this. Thanks

DESIRED OUTPUT

{

"players": [

{

"id": 271,

"fname": "Carlos",

"lname": "Beltran",

"position": "OF",

"stats": [

{

"year": "2010",

"hr": 32,

"rbi": 99,

"team": "NYM"

},

{

"year": "2011",

"hr": 35,

"rbi": 100,

"team": "STL"

},

{

............

}

]

},

{

........

}

]

}

CURRENT OUTPUT

{"0":{"cbs_id":"18817","fname":"Carlos","lname":"Beltran"},"stats":[{"year":"2007","hr":"33","rbi":"112"}]}

{"0":{"cbs_id":"174661","fname":"Willie","lname":"Bloomquist"},"stats":[{"year":"2007","hr":"2","rbi":"13"}]}

{"0":{"cbs_id":"1208693","fname":"Brennan","lname":"Boesch"},"stats":[{"year":"2010","hr":"14","rbi":"67"}]}

Which is generated with: (I know I'm way off)

if ($result = mysqli_query($link, $sql)) {

$player = array();

while ($row = $result->fetch_assoc())

{

$player[] = array (

'cbs_id' => $row['cbs_id'],

'fname' => $row['fname'],

'lname' => $row['lname']

);

$player['stats'][] = array(

'year' => $row['year'],

'hr' => $row['hr'],

'rbi' => $row['rbi']

);

}

$json = json_encode($player);

echo "

$json

";

mysqli_free_result($result);

}

}

NOTE: Each player can have more than one "stats" record (year, hr, rbi, etc)

解决方案

This may give what you want:

$players = array();

while ($row = $result->fetch_assoc())

{

$id = (int)$row['cbs_id'];

if ( ! isset($players[$id]))

{

// New player, add to $players array.

// For the moment index players by ID so stats can be easily added

// to an existing player. Without indexing (using $players[] = ...),

// the same player would be added for each stats record related to

// him.

$players[$id] = array(

'id' => $id,

'fname' => $row['fname'],

'lname' => $row['lname'],

'stats' => array()

);

}

// Add the stats

$players[$id]['stats'][] = array(

'year' => (int)$row['year'],

'hr' => (int)$row['hr'],

'rbi' => (int)$row['rbi']

);

}

// Players are indexed by their ID in $players but need to be contained in

// a JSON array, so use array_values() to remove indices, e.g. convert

//

// array(

// 271 => array('id' => 271, ...),

// ...

// )

//

// to

//

// array(

// array('id' => 271, ...),

// ...

// )

//

$data = array('players' => array_values($players));

$json = json_encode($data);

Eventually you might leave out the integer casts and let PHP automatically convert stringified numeric data (that's the default behaviour with MySQL query results) back to PHP numbers by using the MYSQLI_OPT_INT_AND_FLOAT_NATIVE mysqli connection option as described in example #5 of this page. Note that it requires the mysqlnd library to be used by PHP (you can check that with phpinfo).