在数组内部创建从mysql数据到json的数组 - Creating array inside array from mysql data to json

我正试图用php从两个mysql表创建json：
-类别（唯一）
-子类别或权限（多个在同一类别中）

但我无法在一个类别下列出多个子类别。相反，对于每个子类别，都会生成一组新的结果，其中也包含类别数据
这是php代码：

$sql = "SELECT a.id as rid,a.name as rname,a.img as rimg,a.price,b.id as cid,b.name as cname FROM rights a INNER JOIN categories b ON a.category=b.id";
$result = $mysqli->query($sql);
    if ($result->num_rows > 0) {
        $json_response = array();
        while($row = $result->fetch_assoc()) {
            $row_array['idCategory'] = $row['cid'];
            $row_array['nameCategory'] = $row['cname'];
            $row_array['rights'] = array([
                'idRight' => $row['rid'],
                'name' => $row['rname'],
                'price' => $row['price'],
                'image' => $row['rimg']
            ]);
            array_push($json_response,$row_array);
        }
        echo json_encode($json_response);
    }

有了这个，我得到了：

[{
    "idCategory": "1",
    "nameCategory": "Cat1",
    "rights": [{
        "idRight": "1",
        "name": "Right1 in Cat1",
        "price": "10",
        "image": "img1.jpg"
    }]
}, {
    "idCategory": "2",
    "nameCategory": "Cat2",
    "rights": [{
        "idRight": "2",
        "name": "Right1 in Cat2",
        "price": "20",
        "image": "img2.jpg"
    }]
}, {
    "idCategory": "2",
    "nameCategory": "Cat2",
    "rights": [{
        "idRight": "3",
        "name": "Right2 in Cat2",
        "price": "30",
        "image": "img3.jpg"
    }]
}]

我试着用GROUP_CONCT和GROUP BY更改mysql-select，但后来我得到了一行数据，如下所示：

"rights": [{
            "idRight": "2,3",
            "name": "Right1 in Cat2,Right2 in Cat2",
            "price": "20,30",
            "image": "img2.jpg,img3.jpg"
        }]

但我需要这样：

[{
    "idCategory": "1",
    "nameCategory": "Cat1",
    "rights": [{
        "idRight": "1",
        "name": "Right1 in Cat1",
        "price": "10",
        "image": "img1.jpg"
    }]
}, {
    "idCategory": "2",
    "nameCategory": "Cat2",
    "rights": [{
        "idRight": "2",
        "name": "Right1 in Cat2",
        "price": "20",
        "image": "img2.jpg"
    },
    {
        "idRight": "3",
        "name": "Right2 in Cat2",
        "price": "30",
        "image": "img3.jpg"
    }]
}]

如何做到这一点？

您需要重新映射您的数组，然后为权限键初始化一个数组。。。所以，把while循环改成这样：

$json_response = array();
while($row = $result->fetch_assoc()) {
    if (!isset($json_response[ $row['idCategory'] ])) {
        $json_response[ $row['idCategory'] ] = [
            'idCategory' => $row['idCategory'],
            'nameCategory' => $row['nameCategory'],
            'rights' => [],
        ];
    }
    $json_response[ $row['idCategory'] ]['rights'][] = [
        'idRight' => $row['rid'],
        'name' => $row['rname'],
        'price' => $row['price'],
        'image' => $row['rimg']
    ];
}
// We want the final result to ignore the keys and to create a JSON array not a JSON object 
$data = [];
foreach ($json_response as $element) {
    $data[] = $element;
}
echo json_encode($data);

代码$json_response[ $row_array['idCategory'] ]的这一部分有助于维护数据的唯一分组，因为它基于idCategory创建了一个散列。一个数组只能有一个键，由于idCategory总是唯一的，我们可以将其用作分组的键。然后，因为我们现在有了一个基于哈希的数组，所以当它被转换为JSON时，我们必须创建一个新的"真实"数组。在这种情况下，您不希望使用GROUP BY或GROUP _CONCAT。

我修改了上面的代码以满足我的需求，但我在制作该数组的另一个数组时遇到了麻烦。。。

发件人：…

$json_response[ $row['idCategory'] ]['rights'][] = [
    'idRight' => $row['rid'],
    'name' => $row['rname'],
    'price' => $row['price'],
    'image' => $row['rimg']
];

至：

    $json_response[ $row['regCode'] ]['provinces'][] = $row['provDesc'];

实际上这是一个后续问题，那个数组里面的数组怎么样。。。

像这样：

[
 {
  rid: "1",
  region: "REGION I", //array 1
  provinces: [
       {
        pid: "128", 
        province: "ILOCOS NORTE", //array 2
        cities[
              "Adams", //array 3
              "Bacarra"
             ],
       "ILOCOS SUR"
      ]
 },
 {
  rid: "2",
  region: "REGION II",
  provinces: [
       "BATANES",
       "CAGAYAN"
      ]
 }

]