MySQL results to JSON with PHP

Question

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 "<pre>$json</pre>";

        mysqli_free_result($result);
    }
}

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

Answer 1

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).

Answer 2

You need an associative array. Something like this should get you started:

$data = array(
    'players' => array(
        array(
           'id' => 271,
           'fname': 'Carlos',
           'lname': 'Beltran',
           'position': 'OF',
           'stats' => array(
               array(
                   'year' => 2010,
                   'hr': 32,
                   'rbi': 99,
                   'team': 'NYM'
               ),
               ...
           )
        ),
        ...
    )
);

To encode it into json, use json_encode:

$json = json_encode($data);