建立从SQL查询结果的JSON对象在PHP

Question

我的数据库看起来是这样的：

我的目标是创建一个SQL查询，这将产生以下JSON对象：

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}, 
      ], 
     ] 
    ]; 

我已经问过一些非常相似的东西。但后来我没有使用“年”（所以现在有点复杂），只有“val1”，“val2”等值。到目前为止，我已经试过这样：

$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

要建立这种以JSON结构，你必须使用对象结构的这样一个概念：

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