在从MySQL数据到JSON的数组内部创建数组 [英] Creating array inside array from mysql data to json

问题描述

我正试图从两个mysql表中用php创建json:
-类别(唯一)
-子类别或版权(同一类别中的多个)

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

I am trying to create json with php from two mysql tables:
- Category(unique)
- Subcategories or Rights(multiple in same category)

But I can't list multiple subcategories under one category. Instead, for every subcategory new set of results is made that contains category data also.
This is php code:

$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_CONCAT和GROUP BY更改mysql select，但是随后我在一行中获取了数据:

I tried changing mysql select with GROUP_CONCAT and GROUP BY but then I get data in one row like this:

"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循环，如下所示:

You need to remap your array and then initialize an array for the rights key... so, change your while loop something like this:

$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.

This part of the code $json_response[ $row_array['idCategory'] ] helps to maintain a unique grouping of the data because it creates a hash based on the idCategory. An array can only have one key and since idCategory is always unique we can use that as the key for grouping on. Then because we now have a hash-based array, we have to create a new array that is a 'real' array for when it is converted to JSON. You do not want to use GROUP BY or GROUP_CONCAT in this situation.

这篇关于在从MySQL数据到JSON的数组内部创建数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！