php - 在 MySQL 数据库中，如何通过 php 和 json 为连续重复多次的字段创建不同的键

Question

我在phpMyAdmin上实验时创建了一个表和另一个表的字段连续两次重复的关系，结果是新的，但是在PHP上实验并打印为Gausson时，结果没有按要求显示，他打印了一个字段两次而没有打印另一个字段

<?php
  include_once("include/config.php");
  header('Content-Type:text/html; charset=utf-8');
  $sql = "
  SELECT po.post_id
       , po.title_post
       , po.user_id
       , po.admin_id 
       , us.id
       , us.fname
       , us.lname 
       , aus.id
       , aus.fname
       , aus.lname 
    FROM postmy po 
    JOIN dataUser us 
    JOIN dataUser aus 
      ON po.user_id = us.id 
     AND po.admin_id = aus.id 
   ORDER 
      BY po.post_id ASC
  ";
$r = mysqli_query($conn,$sql);
$result = array();
 while($row = mysqli_fetch_array($r)){
   array_push($result,array(
  "post_id"=>$row['post_id'],
  "title_post"=>$row['title_post'],
  "user_id"=>$row['user_id'],
  "admin_id"=>$row['admin_id'],
  "us.id"=>$row['id'],
  "us.fname"=>$row['fname'],
  "us.lname"=>$row['lname'],
  "aus.id"=>$row['id'],
  "aus.fname"=>$row['fname'],
  "aus.lname"=>$row['lname']        
 ));    
 }
 echo json_encode(array('result'=>$result));
 mysqli_close($conn);   
?>

Answer 1

您应该为重命名的列使用别名

  $sql = "SELECT po.post_id
      , po.title_post
      , po.user_id
      , po.admin_id 
      , us.id
      , us.fname
      , us.lname 
      , aus.id ausid
      , aus.fname ausfname
      , aus.lname auslname 
    FROM postmy po 
    INNER JOIN dataUser us ON po.user_id = us.id  
    INNER  JOIN dataUser aus  ON po.admin_id = aus.id
   ORDER BY po.post_id ASC";

$r = mysqli_query($conn,$sql);
....


"post_id"=>$row['post_id'],
"title_post"=>$row['title_post'],
"user_id"=>$row['user_id'],
"admin_id"=>$row['admin_id'],
"us.id"=>$row['id'],
"us.fname"=>$row['fname'],
"us.lname"=>$row['lname'],
"aus.id"=>$row['iduas'],
"aus.fname"=>$row['fnameaus'],
"aus.lname"=>$row['lnameaus']   

(并将特定的 ON 应用于每个表)