php - 通过不使用不同查询的 PHP MYSQL 循环创建 JSON

Question

您好，我将如何循环这个在 PHP MYSQL 中创建的 Json，这样我就不需要声明单独的查询来生成我需要的输出。

我想创建一个 JSON，而无需单独声明变量和查询来生成单个数组

<?php
include("connection.php");

$sth = mysqli_query($connect,"SELECT month as month FROM data group by month ORDER BY month DESC");
$rows = array();
$rows['name'] = 'Month';
while($r = mysqli_fetch_array($sth)) {
$rows['data'][] = $r['month'];
}

$sth = mysqli_query($connect,"SELECT sum(cf) as cf FROM  data Where branch='NYC'  group by month");


$rows1 = array();
$rows1['name'] = 'NYC';
while($r = mysqli_fetch_assoc($sth)) {
$rows1['data'][] = $r['cf'];
}


$sth = mysqli_query($connect,"SELECT sum(cf) as cf FROM data Where branch='LA' group by month");
$rows2 = array();
$rows2['name'] = 'LA';
while($rr = mysqli_fetch_assoc($sth) ) {
$rows2['data'][] = $rr['cf'];
}




$result = array();

array_push($result,$rows);
array_push($result,$rows1);
array_push($result,$rows2);

echo(json_encode($result, JSON_NUMERIC_CHECK)) ;


?>

这是输出:

[
    {
        "name": "Month",
        "data": [
            "JANUARY",
            "FEBRUARY"
        ]
    },
    {
        "name": "NYC",
        "data": [
            189000,
            252000
        ]
    },
    {
        "name": "LA",
        "data": [
            3330504,
            4440672
        ]
    }
]

Answer 1

我想这会做你想做的。请注意，我已经更改了您的 ORDER BY 子句，以便它可以正确地对一年中的月份进行排序。

$sth = mysqli_query($conn, 'SELECT month, branch, sum(cf) AS cf 
                            FROM data 
                            GROUP BY month, branch
                            ORDER BY MONTH(STR_TO_DATE(month, "%M"))');
$months = array();
$branches = array();
while ($row = mysqli_fetch_assoc($sth)) {
    $month = $row['month'];
    $branch = $row['branch'];
    if (!in_array($month, $months)) $months[] = $month;
    if (!in_array($branch, array_keys($branches))) $branches[$branch] = array();
    $branches[$branch][$month] = $row['cf'];
}
$out = array();
$out[] = (object) [ 'name' => "Month", 'data' => $months ];
foreach ($branches as $branch => $data) {
    $out[] = (object) [ 'name' => $branch, 'data' => array_values($data)];
}
echo json_encode($out, JSON_NUMERIC_CHECK);