php - 我的SQL查询在JSON响应中为3行返回相同的数据类型

我在json响应中的第一行，第二行和第三行获得相同的值。第二行的假定结果显示在第二列中，第一列的假定结果显示在第三列中。香港专业教育学院尝试了一切，并更改查询仍然没有好处。提前致谢

 $sql = "select
          e_name,
          a_shortcut,
          case
            when t_rank = 1 then '1st'
            when t_rank = 2 then '2nd'
            when t_rank = 3 then '3rd'
          end as t_rank
        from
          team inner join event on team.EID = event.eid Where e_type = 'nonsport' group by event.eid";

         $con = mysqli_connect($server_name,$mysql_user,$mysql_pass,$db_name);

        $result = mysqli_query($con,$sql);


         $response = array();

        while($row=mysqli_fetch_array($result))
        {
        array_push($response, array("e_name"=>$row[0],"1st"=>$row[1],
        "2nd"=>$row[2], "3rd"=>$row[2]));

        }
        echo json_encode (array("nresults"=>$response));

如果第二行和第三行分别为[2]和[2]，这就是我在服务器响应中得到的结果。

 {"nresults":[{"e_name":"Amateur Photography Contest","1st":"AAA","2nd":"3rd","3rd":"3rd"}]}

如果我改变了

1st"=>$row[1],
        "2nd"=>$row[2], "3rd"=>$row[2] into 1st"=>$row[1],
        "2nd"=>$row[1], "3rd"=>$row[1]

变成这样

{"nresults":[{"e_name":"Amateur Photography Contest","1st":"AAA","2nd":"AAA","3rd":"AAA"}]}

我的预期输出是。其中AAA为1级，BBB为2，CCC为3

像这样

{"nresults":[{"e_name":"Amateur Photography Contest","1st":"AAA","2nd":"BBB","3rd":"CCC"}]}

最佳答案

尝试这样：

$sql = "select
          e_name,
          a_shortcut,
          GROUP_CONCAT(case when t_rank = 1 then  a_shortcut end separator ',') as rank1,
          GROUP_CONCAT(case when t_rank = 2 then  a_shortcut end separator ',') as rank2,
          GROUP_CONCAT(case when t_rank = 3 then  a_shortcut end separator ',') as rank3
        from
          team inner join event on team.EID = event.eid Where e_type = 'nonsport' group by event.eid";

         $con = mysqli_connect($server_name,$mysql_user,$mysql_pass,$db_name);

        $result = mysqli_query($con,$sql);


         $response = array();

        while($row=mysqli_fetch_array($result))
        {
        //$ar=explode(',',$row['group_con']);
        array_push($response, array("e_name"=>$row[0],"1st"=>$row['rank1'],
        "2nd"=>$row['rank2'], "3rd"=>$row['rank3']));

        }
        echo json_encode (array("nresults"=>$response));