组合mysql列以获取嵌套的json结果

I have a sql table like this:

我有一个像这样的SQL表:

id  a  b  c1  c2  c3  d
 1  x  y  z1  z2  z3  w
 ....

I want the following json output

我想要以下json输出

{
"data": {
    "id": "1",
    "a": "x",
    "b": "y",
    "c": {
        "c1": "z1",
        "c2": "z2",
        "c3": "z4"
        },
    "d": "w"
    }
}

How should the mysqli query be for this case? What should be the array format and looping be to convert the query result into the required json?

对于这种情况,mysqli查询应该如何?什么应该是数组格式和循环是将查询结果转换为所需的json?

For a new row, it would be added to data. So it would become "data": {data_of_row1, data_of_row2}

对于新行,它将添加到数据中。所以它会变成“数据”:{data_of_row1,data_of_row2}

2 个解决方案

#1

Simple preg_match with foreach can do that:

使用foreach的简单preg_match可以做到这一点:

    <?php
        $link = mysqli_connect('host', 'user', 'pass', 'db') or die(mysqli_error($link));
        $query = "SELECT * FROM sql_table ORDER BY id";
        $result = mysqli_query($link, $query) or die(mysqli_error($link));
        $json = array();
        $rc = 0;
        while($row = mysqli_fetch_assoc($result)){
            foreach($row as $rowName => $rowValue){
                if(preg_match_all('/\d+/', $rowName) !== 0){
                    $index = substr($rowName, 0,1);
                    $json[$rc]['data'][$index][$rowName] = $rowValue;
                }else{
                    $json[$rc]['data'][$rowName] = $rowValue;
                }
            }
            $rc++;
        }
        print_r(json_encode($json));
    ?>

Output:

[{
    "data": {
        "id": "1",
        "a": "x",
        "b": "y",
        "c": {
            "c1": "z1",
            "c2": "z2",
            "c3": "z3"
        },
        "d": "w"
    }
}, {
    "data": {
        "id": "2",
        "a": "xx",
        "b": "yy",
        "c": {
            "c1": "zz1",
            "c2": "zz2",
            "c3": "zz3"
        },
        "d": "ww"
    }
}]

This will work with any columns similar to yours (f4, f5, r, t etc).

这适用于任何类似于你的列(f4,f5,r,t等)。

#2

You can make use of CONCAT and explode();

你可以使用CONCAT和explode();

$q = $db->prepare("SELECT id, a, b, CONCAT(c1, "|", c2, "|", c3) AS c, d FROM mytable WHERE id = 1");
$rows = $q->fetchAll();

$data = [];

foreach($rows as $row){
    $explC = explode("|", $row['c']);

    $cValues = [];
    foreach($explC as $key => $c){
       $cValues["c".($key+1)] = $c;
    } 

    $data['data'][] = [
      "id" => $row['id'],
      "c" => $cValues
    }
}

echo json_encode($data);

#1