Create json format by 2 SQL query

[nitiphone2021]

Dear All,

I would like to create a json a below format

{
    "data": [
        {
            "datetime": "2021-05-12",
            "history": [
                {
				"name": "ABC",
				"feel": 1,
				"cough": 0,
				"fever": 1,
				"headache": 1,
				"tired": 0,
				"appetite": 0,
				},
				{
				"name": "DEF",
				"feel": 1,
				"cough": 0,
				"fever": 1,
				"headache": 1,
				"tired": 0,
				"appetite": 0,
				}
            ]
        },
		 {
            "datetime": "2021-05-13",
            "history": [
                {
				"name": "ABC",
				"feel": 1,
				"cough": 0,
				"fever": 1,
				"headache": 1,
				"tired": 0,
				"appetite": 0,
				},
				{
				"name": "DEF",
				"feel": 1,
				"cough": 0,
				"fever": 1,
				"headache": 1,
				"tired": 0,
				"appetite": 0,
				}
            ]
        },
		 {
            "datetime": "2021-05-14",
            "history": [
                {
				"name": "ABC",
				"feel": 1,
				"cough": 0,
				"fever": 1,
				"headache": 1,
				"tired": 0,
				"appetite": 0,
				},
				{
				"name": "DEF",
				"feel": 1,
				"cough": 0,
				"fever": 1,
				"headache": 1,
				"tired": 0,
				"appetite": 0,
				}
            ]
        }
		
    ]
}

But I need to select database 2 time because information need to group by date

so my PHP like below

 $myArray = array();
   
    $stmt = $conn->prepare("SELECT survay_date FROM tb_feedback WHERE app_ID = ? GROUP BY CAST(survay_date AS DATE) ORDER BY survay_date DESC");
    $stmt->bind_param("s", $data->{'app_id'});
   
    $stmt->execute();
    $res = $stmt->get_result(); 

    if($res->num_rows==0){
      
      header('Content-Type: application/json');
	  $json = array();
	  $myArray['data'] = $json;
       echo json_encode($myArray);die();
    }

    while($row = $res->fetch_array(MYSQLI_ASSOC)) {
        $myArray['data'][] = array_push($row,array("A","B"));
		
		$statement = $conn->prepare("SELECT tb_register.firstname,tb_feedback.feel,tb_feedback.cough,tb_feedback.fever,tb_feedback.headache,tb_feedback.tired,tb_feedback.appetite,tb_feedback.swelling FROM tb_feedback INNER JOIN tb_register ON tb_register.id = tb_feedback.user_id WHERE tb_feedback.app_ID = ? AND tb_feedback.survay_date = ?");
		$statement->bind_param("s", $data->{'app_id'});
		$statement->bind_param("s", $row['survay_date']);
	   
		$statement->execute();
		$result = $statement->get_result(); 
		
		while($infor = $result->fetch_array(MYSQLI_ASSOC)) {
			
			$myArray['data'][] = $infor;
			
		}
		
	}
	
    header('Content-Type: application/json');
    echo json_encode($myArray);

So, How can I create the json format like that?

[Barand]

First of all, don't run queries inside a loop like that. Just use a single query.

When processing the query results put the data into an array with the structure required for your JSON data

json_encode the array.

[Barand]

Something like ...

        $appID = 1;
        
        $statement = $conn->prepare("SELECT f.survey_date
                                         , r.firstname
                                         , f.feel
                                         , f.cough
                                         , f.fever
                                         , f.headache
                                         , f.tired
                                         , f.appetite
                                         , f.swelling 
                                    FROM tb_feedback f
                                         INNER JOIN tb_register r ON r.id = f.user_id 
                                    WHERE f.app_ID = ?
                                    ");
        $statement->bind_param('i', $appID);
        $statement->execute();
        $res = $statement->get_result();
        $data = [];
        foreach ($res as $r) {
            if (!isset($data[$r['survey_date']])) {
                $data[$r['survey_date']] = [ 'datetime' => $r['survey_date'], 
                                             'history'  => []
                                           ];
            }
            $data[$r['survey_date']]['history'][] = array_slice($r, 1);
        }
        $jdata = json_encode( ['Data' => array_values($data)] );
        echo $jdata;