php - 使用 PHP 将一张 mysqli 表转换为 JSON 格式

Question

我正在尝试使用特定的 mysqli 表获得所需的返回，该表具有以下结构:

id | name  | parentid
--------------------
1  | Boss    | 0
2  | Bob     | 1
3  | Chef1   | 1
4  | Chef2   | 1
5  | Lara    | 3
6  | Kim     | 4
7  | Nick    | 1
63 | Oldboss | 20 

我需要为每个 parent 的名字获取一个新的:

[
  {  
    "name": "Boss",
    "attributes": {
      "data-id": "1"
    },
    "children": [
      {      
        "name": "Bob",
        "attributes": {
          "data-id": "2"
        }
      },
      {
        "name": "Chef1",
        "attributes": {
          "data-id": "3"
        }
      },
      {
        "name": "Chef2",
        "attributes": {
          "data-id": "4"
        }
      },
      {
        "name": "Nick",
        "attributes": {
          "data-id": "7"
        }
      }
    ]
  },
  {  
    "name": "Chef1",
    "attributes": {
      "data-id": "3"
    },
    "children": [
      {      
        "name": "Lara",
        "attributes": {
          "data-id": "5"
        }
      }
    ]
  },
  {  
    "name": "Chef2",
    "attributes": {
      "data-id": "4"
    },
    "children": [
      {      
        "name": "Kim",
        "attributes": {
          "data-id": "6"
        }
      }
    ]
  }
]

这就是我需要得到的，对于每个有子项的名称需要位于顶部，但也显示在其父项下方。当使用 while 格式的 echo 时，这将产生相同的结果。但我就是无法获取数组/JSON 来构建我想要的结果。这是我目前拥有的:

$returnarray = array();

$sql = "SELECT DISTINCT
  t_names.name AS Name2,
  t_names.id AS ID2
FROM t_names
  INNER JOIN t_names t_names_1
    ON t_names.id = t_names_1.parentid
WHERE t_names.parentid <> 63
AND t_names.id <> 63";
$result = mysqli_query($conn, $sql);
while($row = mysqli_fetch_array($result)){


$subarray = array(
        "data-id"=> $row['ID2']
    );  

$headarray = array(
    "name"=> $row2['Name2'],
    "attributes"=> $subarray,
    "children"=>$rowarray2
);

$sql2 = "SELECT DISTINCT id, name, parentid FROM t_names WHERE parentid = '{$row[ID2]}' ";
$result2 = mysqli_query($conn, $sql2);
while($row2 = mysqli_fetch_assoc($result2)){

$subarray2 = array(
        "data-id"=> $row2['id']
    ); 

$rowarray = array(
        "name"=> $row2['Name'],
        "attributes"=> $subarray2
    );  

$rowarray2[] = $rowarray;

}

$returnarray[] = $headarray;

}

echo "<pre>";

echo json_encode($returnarray, JSON_PRETTY_PRINT);

echo "</pre>";

上面的结果确实创建了一个 json 格式，结果是代码:

- Boss
Bob
Chef1
Chef2
Nick

-Chef1
Bob
Chef1
Chef2
Nick
Lara

-Chef2
Bob
Chef1
Chef2
Nick
Lara
Kim

因此它创建了组，但它继续使用先前组中的数据并在其后添加正确的名称。

我的数组出了什么问题？

Answer 1

我只需在该一元关系上加入一次表，迭代同一父索引下分组的行，然后折叠顶层。

function transformEmployee($user)
{
    $output['name'] = $user['employee'];
    $output['attributes']['data-id'] = $user['emp_id'];
    return $output;
}

$mysqli = new mysqli('server', 'username', 'password', 'database');

$sql = '
    SELECT bosses.id AS boss_id, bosses.name AS boss,
        employees.id AS emp_id, employees.name AS employee
    FROM names bosses
    JOIN names employees
      ON bosses.id = employees.parent_id
';

$names = $mysqli->query($sql)->fetch_all(MYSQLI_ASSOC);

foreach ($names as $name) {
    $result[$name['boss_id']]['name'] = $name['boss'];
    $result[$name['boss_id']]['attributes']['data-id'] = $name['boss_id'];
    $result[$name['boss_id']]['children'] []= transformEmployee($name);
}

echo json_encode(array_values($result), JSON_PRETTY_PRINT);