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

Question

我在我的json响应中获得了第1行，第2行和第3行的相同值。第2行显示的假设结果显示在第2列，第1列假设的第3个结果显示在第3列。我试过一切，并改变查询仍然没有好处。在此先感谢

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