Loop JSON Creation via PHP MYSQL not using different queries

Question

Hi how will I loop this Json being created in PHP MYSQL so that I wont need to declare individual queries just to produce the output I need.

I would like to create a JSON without the need of individually declaring variables and queries just to produce a single array

<?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)) ;


?>

Here is the output:

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

You forgot to tell us what your problem is! What is "the output I need"? — Jeff
– Jeff, Commented May 4, 2018 at 1:05
Hi Jeff the output I need is the same as the output of the code I only need to reduce the queries I'm doing as the number of branches in my query is not only 2 and it may increase overtime. — user6537067
– user6537067, Commented May 4, 2018 at 1:34

Nick · Accepted Answer · 2018-05-04 02:08:50Z

I think this will do what you want. Note that I have changed your ORDER BY clause so that it will properly order the months in a year.

$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);

Collectives™ on Stack Overflow

Loop JSON Creation via PHP MYSQL not using different queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related