使用PHP将MySQL结果转换为JSON [英] MySQL results to JSON with PHP

问题描述

我在这里看到了很多类似的问题，但是我一直无法获得想要的输出.有人可以帮我吗?如何生成以下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

期望的输出

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

当前输出

{"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)

推荐答案

这可能会提供您想要的东西:

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

最终，您可能不使用整数强制转换，而是使用 MYSQLI_OPT_INT_AND_FLOAT_NATIVE mysqli连接选项，使PHP自动将字符串化的数值数据(这是MySQL查询结果的默认行为)转换回PHP数字. 此页面的示例#5.请注意，它要求mysqlnd库由PHP使用(您可以使用phpinfo进行检查).

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

这篇关于使用PHP将MySQL结果转换为JSON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！