Exporting data from MySQL to JSON file

March 13, 2023

In the mysql database in the weather table I have weather data taken from my weather station. I would like to create a JSON file from this data, thanks to which the data will be shown on the chart.
Unfortunately, each single record is written to a separate "column", look at:

[{
"0":
"date": "08:57:00"
"temperatura": "7.9"
"wspeed": "5.8"
"1":
"date": "09:57:00"
"temperatura": "9.9"
"wspeed": "4.7"
"2":
"date": "10:57:00"
"temperatura": "11.9"
"wspeed": "6.7"
}]

I would like to make it so that the structure of the JSON file is like this:

[{
"temperatura":
[2023-03-13 08:57:00, 7.9],[2023-03-13 09:57:00, 9.9],[2023-03-13 10:57:00, 11.9]
"wspeed":[2023-03-13 08:57:00, 5.8],[2023-03-13 09:57:00, 4.7],[2023-03-13 10:57:00, 6.7]
}]

So that the whole thing can be read by HighCharts.

my code:

$sql = "select * from weather";
$result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection));

//create an array
$emparray = array();
while($row =mysqli_fetch_assoc($result))
{
    $emparray[] = $row;
}
echo json_encode($emparray);

>Solution :

This will solve your problem:

$sql = "SELECT * FROM weather";
$result = mysqli_query($connection, $sql) or die("Error in Selecting " . 
mysqli_error($connection));

$data = array();
while($row = mysqli_fetch_assoc($result)) {
    $date = $row['date'];
    $temperature = $row['temperatura'];
    $wspeed = $row['wspeed'];

    $data['temperatura'][] = array($date, $temperature);
    $data['wspeed'][] = array($date, $wspeed);
}

echo json_encode(array($data));