Visualization of data from MySQL Database with php and Highcharts

Question

I use PHP and Highcharts to connect to my MySQL Database and visualize the date in a graph. It would be helpful if you can give me hints on how to solve it. (i am new to php and highcharts...)

Here is the following code i use (comments are in german...):

<?php
// Verbindung mit der MySQL-Datenbank herstellen
$con = mysqli_connect('ip', 'user', 'passwd', 'Sensordata');

// Abfrage an die Datenbank senden, um die Daten abzurufen
//$result = mysqli_query($con, 'SELECT * FROM `temp_humidity`');
//$query = "SELECT TIME(datetime) AS datetime, temp FROM temp_humidity";

$query = "SELECT DATE(datetime) AS datetime, CAST(temp AS INT) AS temp FROM temp_humidity";
//$query = "SELECT datetime, temp FROM temp_humidity";
$result = mysqli_query($con, $query);

// Array erstellen, um die Daten aus der Datenbank zu speichern
$data = array();
$temps = array();
// Schleife durch die Daten in der Datenbank
while ($row = mysqli_fetch_array($result)) {
    // Daten aus der Spalte 'datetime' verwenden, um sie als X-Achse im Diagramm darzustellen
    $data['xAxis'][] = $row['datetime'];
    // Daten aus der Spalte 'temp' verwenden, um sie als Y-Achse im Diagramm darzustellen
    $data['yAxis1'][] = $row['temp'];
    //$temps[] = $row['temp'];
}

// Datenarray in JSON-Format konvertieren, um sie in Highcharts verwenden zu können
$json_data = json_encode($data);
//$json_temps = json_encode($temps);

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>SensMo</title>
</head>
<body>
    <!-- Highcharts-Bibliothek importieren -->
    <script src="https://code.highcharts.com/highcharts.js"></script>
    <script src="https://code.highcharts.com/modules/exporting.js"></script>
    <script src="https://code.highcharts.com/modules/export-data.js"></script>
    <script src="https://code.highcharts.com/modules/accessibility.js"></script>

    <!-- Diagrammbereich definieren -->
    <div id="container" style="width:100%; height:400px;"></div>

    <!-- Diagramm erstellen -->
    <script type='text/javascript'>

        // Daten aus dem JSON-Array verwenden
        var data = <?php echo $json_data; ?>;
        // Diagramm erstellen und in den definierten Bereich einfügen
        Highcharts.chart('container', {
            chart: {
                type: 'line' // Diagrammtyp (Linie)
            },
            title: {
                text: 'Device pi-002 Temperaturverlauf' // Titel des Diagramms
            },
            xAxis: {
                categories: data.xAxis, // X-Achse verwendet die Werte aus dem 'datetime'-Array
                title: {
                    text: 'Zeit'
                }
            },
            yAxis: {
                title: {
                    text: 'Temperatur (°C)'
                }
            },
            legend: {
                layout: 'vertical', // Legenden-Bereich vertikal anordnen
                align: 'right', // Legenden-Bereich rechtsbündig ausrichten
                verticalAlign: 'middle' // Legenden-Bereich mittig ausrichten
            },
            series: [{
                name: 'Temperatur', // Name der Datenreihe (wird im Legenden-Bereich angezeigt)
                data: data.yAxis1 // Daten für die Y-Achse verwenden
                //color: '#ff0000' // Farbe der Linie (rot)
            }]
        });
    </script>
</body>
</html>

i dont why the temperature dont show shows diagramm only datetime but not temperature

but data is there (xAxis and yAxis1): and it doesnt show in the diagram

var data = {
    "xAxis":["2022-11-21", "2022-11-21", "2022-11-21", "2022-11-21",  
             "2022-11-21", "2022-11-21", "2022-11-21", "2022-11-21", 
             "2022-11-21", "2022-11-21", "2022-11-21", "2022-11-21", 
             "2022-11-21", "2022-11-21", "2022-11-21", "2022-11-21", 
             "2022-11-21", "2022-11-21", "2022-11-21", "2022-11-21", 
             "2022-12-10"
            ],
    "yAxis1":[ "22", "22", "22", "22", 
               "23", "23", "23", "23",
               "22", "23", "23", "22", 
               "22", "22", "23", "23",
               "23", "23", "23", "23", 
               "25" 
            ]
};

this is how the database looks just wanted to display temperature for now

(SQL file: when i created the table first

DROP TABLE IF EXISTS temp_humidity;
CREATE TABLE IF NOT EXISTS temp_humidity (
    device VARCHAR(50), 
    datetime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    temp VARCHAR(60), 
    hum VARCHAR(60)
);
)

additional info ^^ (in device there only stand the name of device (pi-002))

i thought the problem was that "temp" format was a varchar and not int or other datatype so in code i used CAST(...).. (query) but it didnt change anything

does someone know what i have done wrong or am i missing somthing?

i mean xAxis shows up like in picture but yAxis1 (temperature) not everything else works... — juhuDom, Dec 10 '22 at 15:42

score 0 · Answer 1 · answered Dec 10 '22 at 16:09

0

Try passing the temps as numbers rather than strings, change this line to do that

$data['yAxis1'][] = (int)$row['temp'];

Everything returned to you from the mysqli extension will be a string even if you cast it in the query

answered Dec 10 '22 at 16:09

RiggsFolly

93,638
21
103
149

Compare as well: [`JSON_NUMERIC_CHECK`](https://www.php.net/manual/en/json.constants.php#constant.json-numeric-check) (int) Encodes numeric strings as numbers as flag for [`json_encode(php)`](https://php.net/json_encode). – hakre Dec 10 '22 at 16:24
Thank you very much RiggsFolly, now it works. – juhuDom Dec 10 '22 at 17:29

Visualization of data from MySQL Database with php and Highcharts

1 Answers1