Combine JSON Object based on month PHP MYSQL

Question

Hi I am having problem generating a json object that will return branch, cf of that month under 1 array like this:

Desired Output

 [{
"month": "Jan",
"Branch2":  550965.96 ,
"Branch1": 663134.44
}, {
"month": "Feb",
"Branch2": 472793.10,
"Branch1": 492784.54
}, {
"month": "March",
"Branch2": 394616.65,
"Branch1": 757639.93  
}, {
"month": "April",
"Branch2": 376403.65 ,
"Branch1": 569404.61  
}]

Here is my current Code

<?php
  $dbhost = 'localhost';
  $dbname = 'spmdb';  
  $dbuser = 'root';                  
  $dbpass = ''; 


  try{

    $dbcon = new PDO("mysql:host={$dbhost};dbname={$dbname}",$dbuser,$dbpass);
    $dbcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

  }catch(PDOException $ex){

    die($ex->getMessage());
  }
$stmt = $dbcon->prepare("SELECT month.monthname,coll.branch,coll.cf FROM coll INNER JOIN month ON month.id = coll.`month` group by coll.month,coll.branch");
$stmt->execute();
$row1 = [];
while($row=$stmt->fetch(PDO::FETCH_ASSOC))
    {
        extract($row);
        $row1[]= ['month' => $monthname, $branch => $cf];

    }
    echo json_encode($row1,JSON_NUMERIC_CHECK);
?>

and currently ouputs this:

[{"month":"Jan","Branch4":40275}
,{"month":"Feb","Branch5":165173.91}
,{"month":"Feb","Branch1":93360.33}
,{"month":"Apr","Branch1":65415.75}]

Is there a way I could do this without modifying the mysql ?

How are you supposed to get 2 branches in one object, when you only have one branch in the row? — Barmar
– Barmar, Commented Jun 1, 2018 at 5:00
Why are you using GROUP BY when you have no aggregation functions like SUM() or MAX()? — Barmar
– Barmar, Commented Jun 1, 2018 at 5:01

Jaydp · Accepted Answer · 2018-06-01 05:05:11Z

1

You have to use GROUP_CONCAT and CONCAT to your query

$stmt = $dbcon->prepare("SELECT month.monthname, GROUP_CONCAT(CONCAT(coll.branch,':',coll.cf)) as branchs FROM coll INNER JOIN month ON month.id = coll.`month` group by month.monthname");
$stmt->execute();
$row1 = [];
while($row=$stmt->fetch(PDO::FETCH_ASSOC))
{
    $branchs = $row['branchs'];
    $branchs = explode(",",$branchs);
    $branch_data = array();
    foreach($branchs as $branch)
    {
        $branch = explode(":", $branch);
        $branch_data[$branch[0]] = $branch[1];
    }       
    $row1[] = array_merge(array('name' => $row['monthname']), $branch_data);

}
echo json_encode($row1,JSON_NUMERIC_CHECK);

Check SQLFiddle: http://sqlfiddle.com/#!9/535f2/1

answered Jun 1, 2018 at 5:05

Jaydp

1,0499 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user6537067 Over a year ago

It was good but do you have idea on how to get the sum using grou concat?

Jaydp Over a year ago

@kkk - for what data you want to do sum?

user6537067 Over a year ago

coll.cf column is where i want to have the sum

Andreas · Accepted Answer · 2018-06-01 04:08:24Z

0

It looks to me like you need to change the month placing in the loop/building of the array.

while($row=$stmt->fetch(PDO::FETCH_ASSOC))
{
     extract($row);
     $row1['month' => $monthname]= [$branch => $cf];

}

answered Jun 1, 2018 at 4:08

Andreas

24k7 gold badges34 silver badges75 bronze badges

1 Comment

Barmar Over a year ago

He's not trying to create an associative array.

Collectives™ on Stack Overflow

Combine JSON Object based on month PHP MYSQL

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related