PHP: Drawing graphs in PHP using temperature as an example

This application in PHP connects to an external Microsoft SQL Server database, reading temperature data from two tables: MinskTemp and WarsawTempand then presents them as an interactive line graph using the Chart.js library. The chart compares Minsk and Warsaw temperatures based on the dates stored in the database, allowing visual analysis of temperature changes in both cities. The application runs as a single PHP file that automatically retrieves data from the database each time it is run and displays the updated graph in the user's browser.

The application is running and you can check its operation: https://wiki.ostrowski.net.pl/php_mysql/temp_chart.php

The data for this application is collected flow in Node-Red.

This application is about Minsk Mazowiecki, not the capital of Belarus. ;-)

temp_chart.php
<?php
// Database connection settings
$serverName = "localhost"; // or "IP\SQLEXPRESS"
$database = "DB";
$username = "USER";
$password = "PASS";
 
// Connect using PDO (recommended)
try {
    $conn = new PDO("sqlsrv:Server=$serverName;Database=$database", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("Błąd połączenia: " . $e->getMessage());
}
 
// Fetch Minsk data
$minskData = [];
$stmt = $conn->query("SELECT DATE, TEMP FROM dbo.MinskTemp ORDER BY ID");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $minskData[] = $row;
}
 
// Fetch Warsaw data
$warsawData = [];
$stmt = $conn->query("SELECT DATE, TEMP FROM dbo.WarsawTemp ORDER BY ID");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $warsawData[] = $row;
}
 
// Close DB connection
$conn = null;
 
// Prepare labels (dates) and temperature arrays
$labels = array_column($minskData, 'DATE');
$minskTemps = array_column($minskData, 'TEMP');
$warsawTemps = array_column($warsawData, 'TEMP');
?>
 
<!DOCTYPE html>
<html>
<head>
    <title>Temperature Chart</title>
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
</head>
<body>
    <h2>Temperature Comparison: Minsk vs Warsaw</h2>
    <canvas id="tempChart" width="800" height="400"></canvas>
 
    <script>
        const ctx = document.getElementById('tempChart').getContext('2d');
        const tempChart = new Chart(ctx, {
            type: 'line',
            data: {
                labels: <?= json_encode($labels) ?>,
                datasets: [
                    {
                        label: 'Minsk',
                        data: <?= json_encode($minskTemps) ?>,
                        borderColor: 'rgba(255, 99, 132, 1)',
                        fill: false,
                        tension: 0.1
                    },
                    {
                        label: 'Warsaw',
                        data: <?= json_encode($warsawTemps) ?>,
                        borderColor: 'rgba(54, 162, 235, 1)',
                        fill: false,
                        tension: 0.1
                    }
                ]
            },
            options: {
                responsive: true,
                scales: {
                    y: {
                        beginAtZero: false,
                        title: {
                            display: true,
                            text: 'Temperature (°C)'
                        }
                    },
                    x: {
                        title: {
                            display: true,
                            text: 'Date'
                        }
                    }
                }
            }
        });
    </script>
</body>
</html>