MySQL to JSON via PHP

Question

让我用这个问题序言，我知道这个问题已经在这里提出了很多次(即在其他页面中(，我已经拖到了每个答案中，尝试了解决方案，但是保持空的JSON阵列。

根据下面的MySQL查询：

$myQuery = $wpdb->get_results('SELECT player_name, player_team, 
SUM(ruckContests) AS contests FROM afl_master 
WHERE player_team = "COLL" AND year = 2019 AND ruckContests > 0 
GROUP BY player_id ORDER BY contests DESC LIMIT 2');

此查询的输出如下：

Array ( [0] => stdClass Object ( [player_name] => Brodie Grundy [player_team] => COLL [contests] => 661 ) [1] => stdClass Object ( [player_name] => Mason Cox [player_team] => COLL [contests] => 51 ) )

我想做的是将其转换为PHP中的以下JSON对象：

{
 player_name: "Brodie Grundy", 
 player_team: "COLL", 
 contests: 661
},
{
 player_name: "Mason Cox", 
 player_team: "COLL", 
 contests: 51
}

我尝试了mysql_fetch_array()以及fetch_assoc()使用该方法，但无济于事。我感谢任何可用的帮助。

---

在有效的评论中建议的这个简单解决方案：

json_encode($myQuery)

Answer 1

$JsonData=array();
foreach($myQuery as $val){

            $JsonData[]='
               {
                 "player_name": "'.$val->player_name.'", 
                 "player_team": "'.$val->player_team.'", 
                 "contests": $val->contests 
               }
             ';
}
$expectedJson=implode(',',$JsonData);

Answer 2

只需将您的结果带到 fetch_array((或(array($ myquery的阵列中：

json_encode($myQuery);
json_last_error(); // to see the error

Answer 3

您可以在单个SQL语句中进行(我只是以多行格式化了它，以更好地理解(。内部选择语句使用您的查询作为子查询，同时使用JSON_Object生成每一行的JSON对象，最后使用group_concat将每行与逗号组合在一起，然后在最终结果中添加" [和"]用于创建完美的JSON阵列。(另一种方式，除了 json_encode($ myquery(，更简单的版本：((。

SELECT CONCAT('[', 
    GROUP_CONCAT( 
        JSON_OBJECT( 
            'player_name',   player_name, 
            'player_team' ,  player_team, 
            'contests', contests) 
    ) , ']') as json  
FROM (
        SELECT  player_name,  player_team, SUM(ruckContests) as contests
        FROM afl_master 
        WHERE player_team = "COLL" AND year = 1995 AND ruckContests > 0 
        GROUP BY player_name, player_team 
        ORDER BY SUM(ruckContests) DESC 
        LIMIT 2 
) AS TT