使用php mysql创建嵌套的json对象-嵌套组件出错

我正在尝试从 MySql 表创建嵌套的 json 输出，并且我正在努力解决出现的问题。

该表的示例如下所示：

我正在尝试创建以下输出：

[
{
    "id": "KE.CE",
    "x": "Central",
    "value": 368505,
    "market_share": 28,
    "total_share": 50,
    "revenue": [
        ["Nyandarua",1153,0.181],
        ["Nyeri",824,0.109],
        ["Kirinyaga",395,0.065],
        ["Murang",487,0.046],
        ["Kiambu",2174,0.09]
    ]
    },
    {
    "id": "KE.CO",
    "x": "Coastal",
    "value": 386097,
    "market_share": 16,
    "total_share": 20,
    "revenue": [
        ["Mombasa",2526,0.209],
        ["Kwale",956,1.103],
        ["Kilifi",1260,0.087],
        ["Tana River",180,0.057],
        ["Lamu",71,0.493],
        ["Taita–Taveta",699,0.205]
    ]
    },
 {
    "id": "KE.RV",
    "x": "Rift Valley",
    "value": 519026,
    "market_share": 29,
    "total_share": 47,
    "revenue": [
        ["West Pokot",242,0.039],
        ["Samburu",64,0.021],
        ["Trans-Nzoia",1566,0.158],
        ["Uasin Gishu",1769,0.152],
        ["Elgeyo-Marakwet",243,0.535],
        ["Nandi",1929,0.218],
        ["Baringo",504,0.076],
        ["Laikipia",483,0.931],
        ["Nakuru",3482,0.161],
        ["Narok",843,0.073],
        ["Kajiado",881,0.079],
        ["Kericho",2661,0.295],
        ["Bomet",1005,0.115]
    ]
    }
]

我创建的代码如下（请注意，我暂时使用 Rand 功能来创建一些虚拟数据）：

if (mysqli_connect_errno($conn)) {
        echo "Failed to connect to DataBase: " . mysqli_connect_error();
    }
    else {

        /* Declare arrays */
        $output_array =array();
        $region_array = array();
        $county_array = array();

        $fetch_region = mysqli_query($conn, "SELECT distinct region_code as id, region as x, sum(entrants) as value,
        FLOOR(RAND()*(60-10+1))+20 as market_share,
        FLOOR(RAND()*(70-50+1))+20 as total_share
        from reporting_summary_county
        group by region_code") or die(mysqli_error($conn));

    while ($row_region = mysqli_fetch_assoc($fetch_region)) {
        $region_array['id'] = $row_region['id'];
        $region_array['x'] = $row_region['x'];
        $region_array['value'] = $row_region['value'];
        $region_array['market_share'] = $row_region['market_share'];
        $region_array['total_share'] = $row_region['total_share'];
        $region_array['revenue'] = array();

        $fetch_county = mysqli_query($conn,"SELECT county, sum(entrants) as v1, round((sum(entrants)/sum(population))*100,3) as v2
        from reporting_summary_county
        where region_code = ".$row_region['id']."
        group by county"
        ) or die(mysqli_error($conn));

        while ($row_county = mysqli_fetch_assoc($fetch_county)) {
            $county_array['county']=$row_county['county'];
            $county_array['v1']=$row_county['v1'];
            $county_array['v2']=$row_county['v2'];
            array_push($region_array['revenue'],$county_array);
        }

        array_push($output_array,$region_array);
}

$jsonData = json_encode($output_array, JSON_PRETTY_PRINT);


echo $jsonData; 
}

运行代码时，输​​出以下消息：

Unknown column 'KE.CE' in 'where clause'

我相信问题源于这一行：

where region_code = ".$row_region['id']."

我尝试了不同的输出选项，并尝试不转义变量，但没有成功。

有趣的是，当我用硬编码区域替换 $row_region 变量时，它返回预期的输出，但是，硬编码区域的内部收入数据会重复。

输出还突出了另一个问题，即字段名称也显示在收入部分输出中，而我只想查看值。我需要进一步研究这个。

where region_code = 'KE.CE'

此更改的输出：

[ { "id": "KE.CE", "x": "Central", "value": "5033", "market_share": "51", "total_share": "20", "revenue": [ { "county": "Kiambu", "v1": "2174", "v2": "0.090" }, { "county": "Kirinyaga", "v1": "395", "v2": "0.065" }, { "county": "Muranga", "v1": "487", "v2": "0.046" }, { "county": "Nyandarua", "v1": "1153", "v2": "0.181" }, { "county": "Nyeri", "v1": "824", "v2": "0.109" } ] }, { "id": "KE.CO", "x": "Coast", "value": "5692", "market_share": "26", "total_share": "24", "revenue": [ { "county": "Kiambu", "v1": "2174", "v2": "0.090" }, { "county": "Kirinyaga", "v1": "395", "v2": "0.065" }, { "county": "Muranga", "v1": "487", "v2": "0.046" }, { "county": "Nyandarua", "v1": "1153", "v2": "0.181" }, { "county": "Nyeri", "v1": "824", "v2": "0.109" } ] }, { "id": "KE.NA", "x": "Nairobi(Province)", "value": "10870", "market_share": "25", "total_share": "38", "revenue": [ { "county": "Kiambu", "v1": "2174", "v2": "0.090" }, { "county": "Kirinyaga", "v1": "395", "v2": "0.065" }, { "county": "Muranga", "v1": "487", "v2": "0.046" }, { "county": "Nyandarua", "v1": "1153", "v2": "0.181" }, { "county": "Nyeri", "v1": "824", "v2": "0.109" } ] }, { "id": "KE.NE", "x": "North Eastern", "value": "4726", "market_share": "59", "total_share": "23", "revenue": [ { "county": "Kiambu", "v1": "2174", "v2": "0.090" }, { "county": "Kirinyaga", "v1": "395", "v2": "0.065" }, { "county": "Muranga", "v1": "487", "v2": "0.046" }, { "county": "Nyandarua", "v1": "1153", "v2": "0.181" }, { "county": "Nyeri", "v1": "824", "v2": "0.109" } ] }, { "id": "KE.NR", "x": "Eastern", "value": "5467", "market_share": "45", "total_share": "20", "revenue": [ { "county": "Kiambu", "v1": "2174", "v2": "0.090" }, { "county": "Kirinyaga", "v1": "395", "v2": "0.065" }, { "county": "Muranga", "v1": "487", "v2": "0.046" }, { "county": "Nyandarua", "v1": "1153", "v2": "0.181" }, { "county": "Nyeri", "v1": "824", "v2": "0.109" } ] }, { "id": "KE.NY", "x": "Nyanza", "value": "12130", "market_share": "63", "total_share": "31", "revenue": [ { "county": "Kiambu", "v1": "2174", "v2": "0.090" }, { "county": "Kirinyaga", "v1": "395", "v2": "0.065" }, { "county": "Muranga", "v1": "487", "v2": "0.046" }, { "county": "Nyandarua", "v1": "1153", "v2": "0.181" }, { "county": "Nyeri", "v1": "824", "v2": "0.109" } ] }, { "id": "KE.RV", "x": "Rift Valley", "value": "15855", "market_share": "30", "total_share": "20", "revenue": [ { "county": "Kiambu", "v1": "2174", "v2": "0.090" }, { "county": "Kirinyaga", "v1": "395", "v2": "0.065" }, { "county": "Muranga", "v1": "487", "v2": "0.046" }, { "county": "Nyandarua", "v1": "1153", "v2": "0.181" }, { "county": "Nyeri", "v1": "824", "v2": "0.109" } ] }, { "id": "KE.WE", "x": "Western", "value": "5163", "market_share": "43", "total_share": "25", "revenue": [ { "county": "Kiambu", "v1": "2174", "v2": "0.090" }, { "county": "Kirinyaga", "v1": "395", "v2": "0.065" }, { "county": "Muranga", "v1": "487", "v2": "0.046" }, { "county": "Nyandarua", "v1": "1153", "v2": "0.181" }, { "county": "Nyeri", "v1": "824", "v2": "0.109" } ] } ]

更新：如果我更改代码以使用连接字符串：

where region_code = '".$row_region['id']."'

输出返回一个空白屏幕。

我还修改了代码以使用绑定变量，这将返回相同的空白输出。

$query = "SELECT county, sum(entrants) as v1, round((sum(entrants)/sum(population))*100,3) as v2
        from reporting_summary_county
        where region_code =?
        group by county";
        $sql = $conn->prepare($query);
        $sql->bind_param("s", $row_region['id']);
        if (!$sql->execute()) {
            echo "Error: " . $query . "<br>" . $sql->error;
        }
        $fetch_county = $sql->get_result();

例如，如果我在 bind_param 部分出错并将其更改为不存在的内容：

$sql->bind_param("s", $test['id']);

这将返回一个输出，减去收入部分。

[ { "id": "KE.CE", "x": "Central", "value": "5033", "market_share": "44", "total_share": "37", "revenue": [] }, { "id": "KE.CO", "x": "Coast", "value": "5692", "market_share": "65", "total_share": "30", "revenue": [] }, { "id": "KE.NA", "x": "Nairobi(Province)", "value": "10870", "market_share": "45", "total_share": "25", "revenue": [] }, { "id": "KE.NE", "x": "North Eastern", "value": "4726", "market_share": "59", "total_share": "27", "revenue": [] }, { "id": "KE.NR", "x": "Eastern", "value": "5467", "market_share": "50", "total_share": "37", "revenue": [] }, { "id": "KE.NY", "x": "Nyanza", "value": "12130", "market_share": "41", "total_share": "35", "revenue": [] }, { "id": "KE.RV", "x": "Rift Valley", "value": "15855", "market_share": "52", "total_share": "36", "revenue": [] }, { "id": "KE.WE", "x": "Western", "value": "5163", "market_share": "68", "total_share": "29", "revenue": [] } ]

这似乎在我看来，$query 部分的代码存在问题。我已经看这个太久了，显然看不到树木的木材。

任何帮助或建议将不胜感激。