Building a JSON object from SQL query results in PHP

Question

My database looks like this:

My goal is to create an SQL-query which will produce the following JSON-object :

  var data = [
        "year1":[
            "val1":[
                {"DATE":a1, "HEADER":b1, "MESSAGES":c1},
                {"DATE":a2, "HEADER":b2, "MESSAGES":c2},
                {"DATE":a6, "HEADER":b6, "MESSAGES":c6},
            ],
            "val2":[
                {"DATE":a5, "HEADER":b5, "MESSAGES":c5},
                {"DATE":a8, "HEADER":b8, "MESSAGES":c8},
            ],
        ],
        "year2":[
            "val3":[
                {"DATE":a3, "HEADER":b3, "MESSAGES":c3},
                {"DATE":a4, "HEADER":b4, "MESSAGES":c4},
                {"DATE":a7, "HEADER":b7, "MESSAGES":c7},
            ],
        ]
    ];

I already asked something really similar to this. But then I didn't use "year" (so it's a little bit more complex now), only "val1", "val2" etc. with it's values. So far I have tried this:

$connect = mysqli_connect("localhost", "root", "root", "Data");

$sql_year = "SELECT jaar FROM Data";
$result_year = mysqli_query($connect, $sql_year);
$data_year = array();

while ($row = mysqli_fetch_array($result_year, MYSQLI_ASSOC)) {
  $data_year[$row['jaar']][] = array(
    "maand"=> $row['maand'],
  );

  $sql_month = "SELECT * FROM Data WHERE jaar =".$row['jaar'];
  $result_month = mysqli_query($connect, $sql_month);
  $data_month = array();

  while ($row = mysqli_fetch_array($result_month, MYSQLI_ASSOC)) {
    $data_month[$row['maand']][] = array(
      "day"=> $row['day'],
      "weekday"=> $row['weekday'],
      "zaal"=> $row['zaal'],
      "stad" => $row['stad']
    );
  }
}

header('Content-Type: application/json');
echo json_encode($data_year);

Answer 1

To build this kind of structure in JSON, you have to use a concept of object structure like this :

<?php  
$username='XXX';  
$password='XXX';  
$hostname = 'localhost';  
$db_name = 'stack1';

//connection string with database  
$dbhandle = mysqli_connect($hostname,$username,$password,$db_name)
or die("Unable to connect to MySQL");  


//query fire  
$result = mysqli_query($dbhandle,"select DISTINCT year from mydata;");  
$json_response = array();

$array = array();  

// fetch data in array format  
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {  

    $p_year = $row['year'];

    $result_year = mysqli_query($dbhandle,"select month from mydata where year='$p_year';");  
    while ($row = mysqli_fetch_array($result_year, MYSQLI_ASSOC)) {  

          $p_month = $row['month'];

          $result_month = mysqli_query($dbhandle,"select * from mydata where month='$p_month';"); 
          while ($row = mysqli_fetch_array($result_month, MYSQLI_ASSOC)) {
              $array[$p_year][$p_month][] = array(
               "DATE" => $row['date'],
               "HEADER" => $row['header'],
               "MESSAGE" => $row['message']
              );

          }

    }

}  

array_push($json_response,$array);
mysqli_free_result($result);
?>