How to combine multiple tables data and encode json as one array?

Question

I have encoded json array from one month table data as below but how can I combine multiple data table with other month in one json array and GROUP BY dept after combining all data?

<?php

include("dbconfig.php");

$sql = "SELECT dept, SUM(ttlot) AS sum_ot, SUM(ttlnorm) AS sum_norm
        FROM month_jan
        GROUP BY dept
        ORDER BY sum_ot DESC";

$result = mysqli_query($dbconfig, $sql) or die("Error in Selecting " . mysqli_error($dbconfig));

$category = array();
$category['name'] = 'Dept';

$series1 = array();
$series1['name'] = 'Normal';

$series2 = array();
$series2['name'] = 'OT';

$emparray = array();
while ($row = mysqli_fetch_assoc($result)) {

$category['data'][] = $row['dept'];
$series1['data'][] = $row['sum_norm'];
$series2['data'][] = $row['sum_ot'];

}

$result = array();
array_push($result,$category);
array_push($result,$series1);
array_push($result,$series2);

$json = json_encode($result,JSON_NUMERIC_CHECK);

echo $json;

mysqli_close($dbconfig);
?>

Output in January month:

[{"name":"Dept","data":["CNC","MACH","ANOD","BUFF","CAST","POLISH","SL","EPT","TUMB","TOOL","SHOT","QC","LOG","MAIN","LC","WWT","OG","NPD","E-COAT","SFT"]},{"name":"Normal","data":[47429.1,39975.7,34553.8,49075.9,28316.3,21237.1,13492.5,5848.2,7691.1,6963.9,5636.1,7555.8,5821.9,2161.2,1812,1191.7,1479.1,1299.6,11542.6,602]},{"name":"OT","data":[20041,17874,14431,13535.5,8800.5,5613.5,3569.5,3101,2327,2278,2237,2142,1810,942,690,456,297,110.5,66,50.5]}]

What result I want after combining four months:

[{"name":"Month","data":["Jan","Feb","Mac","Apr"]},{"name":"Normal","data":[504291,409757,295538,430759]},{"name":"OT","data":[89041,96874,81431,80535]}]

Does anyone can help me to solve the problem?

Answer 1

I did it manually by printing them, I think this is an alternative solution if you are having the some problem encoding to JSON of two tables. Sometime rows with the same name produces an error on json_encode() function.

<?php
session_start();

require("../config.php");
$output = array();

$id = $_GET['id'];

if(isset($_SESSION['user'])){
    $f_data ='';
    $sql = "SELECT * FROM quiz WHERE subject_id=$id ORDER BY id DESC";
    $query=$conn->query($sql);
    while($row=$query->fetch_array()){
        $sql2 = "SELECT * FROM q_details WHERE id=$row[2] ORDER BY id DESC LIMIT 1";
        $query2=$conn->query($sql2);
        while($row2=$query2->fetch_array()){
            $f_data .= '{
                    "id":"'.$row[0].'",
                    "subject_id":"'.$row[1].'",
                    "questionaire_id":"'.$row[2].'",
                    "name":"'.$row[3].'",
                    "description":"'.$row[4].'",
                    "start":"'.$row[5].'",
                    "end":"'.$row[6].'",
                    "date":"'.$row[7].'",
                    "questionaire_name":"'.$row2[2].'",
                    "questionaire_description":"'.$row2[3].'"
                    },';
        }
    }
     $f_data = substr($f_data, 0, -1);
     echo '['.$f_data.']';
}
else{
    echo"<script>window.open('http://localhost/lnhs_app/#/','_self')</script>";
}

?>

If you have rows with the same name you should rename the other like what I did on the example above (questionaire_name, questionaire_description).

It will produce this output:

[{ "id":"1", "subject_id":"2", "questionaire_id":"1", "name":"Quiz 1", "description":"Answer this quiz within 1 hour", "start":"7:30AM", "end":"8:30AM", "date":"08-01-18", "questionaire_name":"Right triangle", "questionaire_description":"Questionaire # 1" }]

Answer 2

With MySQL's JSON features and a combination of GROUP_CONCAT you can do some pretty awesome things with combining tables:

Your desired result:

[{"name":"Month","data":["Jan","Feb","Mac","Apr"]},{"name":"Normal","data":[504291,409757,295538,430759]},{"name":"OT","data":[89041,96874,81431,80535]}]

Utilizing GROUP_CONCAT you don't need joining tables but simply a group by field by which your fields are aggregated:

SELECT
  CONCAT('{\"NAME\" : \"NORMAL\",',
  '\"data\": [',  GROUP_CONCAT( '"', SUM(ttlnorm), '"'), ']},',
  '{\"NAME\" : \"OT\",',
  '\"data\": [',  GROUP_CONCAT( '"', SUM(ttlot), '"'), ']}',
  )
  from month_jan
  GROUP BY dept;

If you are using a table structure that stores by month ("month_jan"), you will need to perform a union amongst your months or change your table structure so that all months needed are included within the same table. You can perform your concatenation on multiple levels based on your GROUP BY's. For example, if you have two group by fields, you will be able to nest your JSON: ex: GROUP BY DEPT, MONTH

SELECT
  CONCAT('{',
  '\"DEPT\" :\"', dept,'\",',
  '{\"NAME\" : \"Month\",',
  '\"data\": [',  GROUP_CONCAT( '"', month, '"'), ']},',
  '{\"NAME\" : \"NORMAL\",',
  '\"data\": [',  GROUP_CONCAT( '"', SUM(ttlnorm), '"'), ']},',
  '{\"NAME\" : \"OT\",',
  '\"data\": [',  GROUP_CONCAT( '"', SUM(ttlot), '"'), ']}',
  )
  from ttl_ot_norm
  GROUP BY dept, month;

As a result, your data within your GROUP_CONCAT's will work out according to the form you expressed.

An alternate solution is to create a table that updates based on a cron job or table trigger and places this data into a JSON field type so it's continuously ready for retrieval with a cheap CPU cost. This way, you won't have the additional overhead of the concatenation and table joining on each query.

You can skip a lot of your PHP aggregation by combing GROUP_CONCAT and grouping your tables. I've saved hundreds of hours of programming over the years with this and a combination of MySQL's JSON features (link below)

Version 8 introduced these features where you don't need to create your own JSON like strings with JSON_ARRAYAGG and other great JSON features. Although, the above will work with version 5 and up.

https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

https://dev.mysql.com/doc/refman/5.7/en/json-creation-functions.html#function_json-array

VERSION 8: https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_group-concat