如何修改server.php实现仪表盘与折线图实时数据同时输出

1. 重写server.php代码 #

你现有server.php存在重复连接数据库、返回数据格式错误、未同时返回两种图表所需数据的问题，直接替换为以下代码即可：

<?php
error_reporting(E_ALL);
header("content-type: application/json");
// 单次连接数据库
$connection = mysqli_connect('localhost', 'root', '', 'adminpanel');

// 1. 查询最新1条数据用于仪表盘
$gaugeQuery = 'SELECT temperature, pH, DO, Turbidity FROM tbl_waterquality ORDER BY id DESC LIMIT 1';
$gaugeResult = mysqli_query($connection, $gaugeQuery);
$gaugeRow = mysqli_fetch_array($gaugeResult);
// 组装仪表盘所需数据格式
$gaugeData = [
    ["Label", "Value"],
    ["Temperature", $gaugeRow["temperature"]],
    ["pH", $gaugeRow["pH"]],
    ["DO", $gaugeRow["DO"]],
    ["Turbidity", $gaugeRow["Turbidity"]]
];

// 2. 查询历史时序数据用于折线图，LIMIT限制条数避免加载过慢
$lineQuery = 'SELECT water_level, UNIX_TIMESTAMP(CONCAT_WS(" ", sensor_date , sensor_time)) AS datetime 
FROM tbl_waterquality ORDER BY sensor_date DESC, sensor_time DESC LIMIT 50';
$lineResult = mysqli_query($connection, $lineQuery);
// 组装折线图所需数据格式
$lineData = [
    ["Date Time", "Water Level (ft)"]
];
while ($row = mysqli_fetch_array($lineResult)) {
    // 转换为Google Charts支持的js日期格式
    $lineData[] = [
        "Date(" . $row["datetime"] . "000)",
        (float)$row["water_level"]
    ];
}

// 统一返回两种数据
echo json_encode([
    'gauge' => $gaugeData,
    'line' => $lineData
]);
?>

注意：如果折线图需要展示温度而非水位，把查询语句里的water_level替换为temperature即可

2. 修改index.php前端逻辑 #

现有前端共用同一个数据源渲染两种图表的逻辑不成立，两种图表所需数据结构完全不同，替换js部分代码为以下内容：

<script src="https://www.gstatic.com/charts/loader.js"></script>
<script>
let lineChart, gauge;
const lineOptions = {
    title: "Sensors Data",
    legend: { position: "bottom" },
    chartArea: { width: "80%", height: "70%" }
};
const gaugeOptions  = {
    width: 500,
    height: 200,
    minorTicks: 5,
};

// 分别渲染两个图表
const drawCharts = (gaugeArr, lineArr) => {
    // 初始化仪表盘
    if (!gauge) gauge = new google.visualization.Gauge(document.getElementById('gauge_div'));
    const gaugeData = google.visualization.arrayToDataTable(gaugeArr);
    gauge.draw(gaugeData, gaugeOptions);

    // 初始化折线图
    if (!lineChart) lineChart = new google.visualization.LineChart(document.getElementById('linechart_div'));
    const lineData = google.visualization.arrayToDataTable(lineArr);
    lineChart.draw(lineData, lineOptions);
}

const getData = () => {
    fetch('server.php')
        .then(response => response.json())
        .then(res => {
            drawCharts(res.gauge, res.line);
            setTimeout(getData, 2000); // 2秒刷新一次
        });
};

window.addEventListener("load", () => {
    google.charts.load('current', {
        'packages': ['gauge','corechart']
    });
    google.charts.setOnLoadCallback(getData);
})
</script>
</head>
<body>
    <div id="gauge_div" style="width: 400px; height: 120px; margin-top:30px"></div>
    <div id="linechart_div" style="width: 900px; height: 300px; margin-top:30px;"></div>
</body>

注意：原有折线图的高度设置过小，可自行调整div的宽高参数适配页面展示

内容的提问来源于stack exchange，提问作者Corbin Jones