如何从两个不同的MySQL表中的两个变量绘制折线图

Question

我是 php 的新手，并且知道如何在 MySQL 的两个不同表中为两个不同的温度值绘制折线图。我可以从一个表中获取数据并将其绘制在图表中，但我无法弄清楚如何从两个不同的表中获取两个临时值。

第一个表1： ID 温度 1 20

第二表2： ID 温度 1 25

数据.php代码：

<?php 

 $servername = "localhost";

 $database = "test";

 $username = "test";

 $password = "12345";

 $connect = mysqli_connect($servername, $username, $password, $database);

 if (!$connect) {

  die("Connection failed: " . mysqli_connect_error());

  }

$query = ("SELECT * FROM Table1 UNION SELECT * FROM Table2");

$result = mysqli_query($connect, $query);

$data = array(); 

if ($result->num_rows > 0) { 

 while ($row = $result->fetch_assoc()) { 

 array_push($data, [$row['time'], floatval($row['temp']), $row['time'], floatval($row['temp'])]); 

 }

 }

  echo json_encode($data);

  mysqli_close($connect);

?>

如何从两个表中获取两个温度值？

Answer 1

首先，我们分别查询两个表。然后合并响应数据，Table1Table2<?php&nbsp; $servername = "localhost";&nbsp; $database = "test";&nbsp; $username = "test";&nbsp; $password = "12345";&nbsp; $connect = mysqli_connect($servername, $username, $password, $database);&nbsp; if (!$connect) {&nbsp; &nbsp; die("Connection failed: " . mysqli_connect_error());&nbsp; }&nbsp; $data = array(&nbsp; &nbsp; 'Table1' => array(),&nbsp; &nbsp; 'Table2' => array()&nbsp; );&nbsp; $query = ("SELECT * FROM Table1");&nbsp; $result = mysqli_query($connect, $query);&nbsp; if ($result->num_rows > 0) {&nbsp; &nbsp; while ($row = $result->fetch_assoc()) {&nbsp; &nbsp; &nbsp; array_push($data['Table1'], [$row['time'], floatval($row['temp'])]);&nbsp; &nbsp; }&nbsp; }&nbsp; $query = ("SELECT * FROM Table2");&nbsp; $result = mysqli_query($connect, $query);&nbsp; if ($result->num_rows > 0) {&nbsp; &nbsp; while ($row = $result->fetch_assoc()) {&nbsp; &nbsp; &nbsp; array_push($data['Table2'], [$row['time'], floatval($row['temp'])]);&nbsp; &nbsp; }&nbsp; }&nbsp; echo json_encode($data);&nbsp; mysqli_close($connect);?>绘制图表时，我们只能有一个x轴。所以我们需要在时间列上连接两个表。但首先，谷歌图表只需要加载一次。不是每次我们绘制图表时。所以我们首先加载谷歌图表，然后再进行其他操作。而且我们并不确切知道需要多长时间才能获取数据并绘制图表。使用，而不是使用 。setIntervalsetTimeoutgoogle.charts.load('current', {&nbsp; packages: ['corechart']}).then(function () {&nbsp; var options = {&nbsp; &nbsp; hAxis: {&nbsp; &nbsp; &nbsp; title: 'Time'&nbsp; &nbsp; },&nbsp; &nbsp; vAxis: {&nbsp; &nbsp; &nbsp; title: 'Sensors Scale'&nbsp; &nbsp; },&nbsp; &nbsp; colors: ['#a52714', '#097138']&nbsp; };&nbsp; var chart = new google.visualization.LineChart(document.getElementById('chart_div'));&nbsp; google.visualization.events.addListener(chart, 'ready', function () {&nbsp; &nbsp; setTimeout(drawLineColors, 5000);&nbsp; });&nbsp; drawLineColors();&nbsp; function drawLineColors() {&nbsp; &nbsp; var data1 = new google.visualization.DataTable();&nbsp; &nbsp; data1.addColumn('string', 'Date');&nbsp; &nbsp; data1.addColumn('number', 'Temperature');&nbsp; &nbsp; var data2 = new google.visualization.DataTable();&nbsp; &nbsp; data2.addColumn('string', 'Date');&nbsp; &nbsp; data2.addColumn('number', 'Temperature');&nbsp; &nbsp; $.ajax({&nbsp; &nbsp; &nbsp; url: 'data.php',&nbsp; &nbsp; &nbsp; dataType: 'json',&nbsp; &nbsp; }).done(function (jsonData) {&nbsp; &nbsp; &nbsp; data1.addRows(jsonData.Table1);&nbsp; &nbsp; &nbsp; data2.addRows(jsonData.Table2);&nbsp; &nbsp; &nbsp; var joinData = google.visualization.data.join(data1, data2, 'full', [[0, 0]], [1], [1]);&nbsp; &nbsp; &nbsp; var rowIndex = data1.getNumberOfRows() - 1;&nbsp; &nbsp; &nbsp; var lastTime = data1.getValue(rowIndex, 0);&nbsp; &nbsp; &nbsp; var lastTemp = data1.getValue(rowIndex, 1);&nbsp; &nbsp; &nbsp; var rowIndex2 = data2.getNumberOfRows() - 1;&nbsp; &nbsp; &nbsp; var lastTime2 = data2.getValue(rowIndex2, 0);&nbsp; &nbsp; &nbsp; var lastTemp2 = data2.getValue(rowIndex2, 1);&nbsp; &nbsp; &nbsp; $(".TempStatus").html(lastTemp + " &deg;C");&nbsp; &nbsp; &nbsp; $(".Temp2Status").html(lastTemp2 + " &deg;C");&nbsp; &nbsp; &nbsp; chart.draw(joinData, options);&nbsp; &nbsp; });&nbsp; }});其他注意事项...async: false在 ajax 已被弃用的情况下，请改用回调。done也一样，不需要转换经典图表的选项，'line'google.visualization.LineChartgoogle.charts.Linegoogle.charts.Line.convertOptions