php SQL 如何分组查询后分类输出为json？

Question

数据库结构如下：
分类表class

cid	cname
1	分类 1
2	分类 2

详情表detail

did	cid	simpleW	detailW	uid
1	1	简介1	详情1	common
2	1	简介2	详情2	common

想要根据分类输出为json，有没有大佬教教我如何才能实现，对这方面不太懂

分割线------
目前使用php实现按照分类遍历输出，但是还没实现json，还有我认为这个方法对性能影响有点大。。代码如下

$queryClass = mysqli_query($conn,"SELECT cid,cname FROM class_wtool WHERE uid = 'common'");
    while($rclass = mysqli_fetch_array($queryClass))
    {
        echo "<h2>" . $rclass['cname'] ."</h2>";
        echo "<br>";
        $queryDetail = mysqli_query($conn,"SELECT wid,simpleW,detailW FROM word_wtool WHERE uid = 'common' AND cid = " . $rclass['cid']);

        while($rdetail = mysqli_fetch_array($queryDetail))
        {
            echo $rdetail['wid']. " " . $rdetail['simpleW']. " " . $rdetail['detailW'];
            echo "<br>";
        }
    }

Answer 1

大概就是这个样子，主要就是把原本的 echo 的内容，改成存到数组，先构建对应的数组结构，最后再使用 json_encode 来把数组转为 JSON 后输出；

<?php

// 初始化一个数组来放响应
$response = [
    'code' => 0,
    'msg'  => 'ok',
    'data' => null,
];

// 用来暂存结果。
$result = [];

$queryClass = mysqli_query($conn, "SELECT cid,cname FROM class_wtool WHERE uid = 'common'");
while ($rclass = mysqli_fetch_array($queryClass)) {
    // 创建空的元素
    $item = [];
    // 赋值 name
    $item['name'] = $rclass['cname'];

    $queryDetail = mysqli_query(
        $conn,
        "SELECT wid,simpleW,detailW FROM word_wtool WHERE uid = 'common' AND cid = " . $rclass['cid']
    );

    while ($rdetail = mysqli_fetch_array($queryDetail)) {
        // 赋值到 list 的键，并且为一个数组
        $item['list'][] = [
            'wid'     => $rdetail['wid'],
            'simpleW' => $rdetail['simpleW'],
            'detailW' => $rdetail['detailW'],
        ];
    }

    // 最后丢进去
    $result[] = $item;
}
// 赋值
$response['data'] = $result;
// 设置响应的 header
header('Content-Type: application/json');

// 数组转为 JSON 字符串，并且输出
echo json_encode($response, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
// 退出脚本
die();

• 比较版

<?php

+ // 初始化一个数组来放响应
+ $response = [
+     'code' => 0,
+     'msg'  => 'ok',
+     'data' => null,
+ ];
+ 
+ // 用来暂存结果。
+ $result = [];
+ 
$queryClass = mysqli_query($conn, "SELECT cid,cname FROM class_wtool WHERE uid = 'common'");
while ($rclass = mysqli_fetch_array($queryClass)) {
+   // 创建空的元素
+   $item = [];
+   // 赋值 name
+   $item['name'] = $rclass['cname'];
-   echo "<h2>" . $rclass['cname'] ."</h2>";
-   echo "<br>";
    $queryDetail = mysqli_query($conn,"SELECT wid,simpleW,detailW FROM word_wtool WHERE uid = 'common' AND cid = " . $rclass['cid']);

    while ($rdetail = mysqli_fetch_array($queryDetail)) {
+       // 赋值到 list 的键，并且为一个数组
+       $item['list'][] = [
+           'wid'     => $rdetail['wid'],
+           'simpleW' => $rdetail['simpleW'],
+           'detailW' => $rdetail['detailW'],
+       ];
+       
-       echo $rdetail['wid']. " " . $rdetail['simpleW']. " " . $rdetail['detailW'];
-       echo "<br>";
    }
+
+   // 最后丢进去
+   $result[] = $item;
}
+ // 赋值
+ $response['data'] = $result;
+ // 设置响应的 header
+ header('Content-Type: application/json');
+ 
+ // 数组转为 JSON 字符串，并且输出
+ echo json_encode($response, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE);
+ // 退出脚本
+ die();