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

Question

我在 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"}]}

Answer 1

尝试这样:

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