针对一个PHP变量的多个MYSQL查询

Question

我已经从我的SQL表中分解了我想要的信息的单个SQL查询，但是我对如何将所有语句组合到一个变量上感到困惑。在PHP中使用这个变量来显示我的数据。

下面是我想要运行的SQL查询。

SELECT * FROM weather ORDER BY stamp DESC LIMIT 1 

SELECT SUM(rainfall) FROM weather WHERE stamp >= CURDATE()) AS total_rainfall

SELECT MAX(maxwind) FROM weather WHERE stamp >= CURDATE()) AS max_windspeed

SELECT MAX(temperature) FROM weather WHERE stamp >= CURDATE()) AS max_temperature

SELECT MIN(temperature) FROM weather WHERE stamp >= CURDATE()) AS min_temperature

这是我当前的查询，除了过去24小时的最大风速、最高温度和最低温度之外，它给出了我想要的所有信息。

 SELECT *, (SELECT SUM(rainfall) FROM weather WHERE stamp >= CURDATE()) AS total_rainfall FROM weather ORDER BY stamp DESC LIMIT 1

基本上，我只想添加当前日期内发生的最高温度、最低温度和最大风速。

MySQL data table example

下面是我尝试使用PHP显示数据的方法。

<?php

$url1=$_SERVER['REQUEST_URI'];

header("Refresh: 60; URL=$url1");

$connectinfo = mysql_connect("***", "***", "***")
or die(mysql_error());

mysql_select_db("raspberrydb001", $connectinfo);

$sql = "SELECT *, (SELECT SUM(rainfall) FROM weatherdata WHERE stamp >= CURDATE()) AS total_rainfall FROM weatherdata ORDER BY stamp DESC LIMIT 1; ";


$result = mysql_query($sql, $connectinfo);

while($row = mysql_fetch_array($result)) {

$windspeed = $row['windspeed'];
$maxwind = $row['maxwind'];
$temperature = $row['temperature'];
$humidity = $row['humidity'];
$rainfall = $row['rainfall'];
$stamp = $row['stamp'];
$d=mktime();
$total_rainfall = $row['total_rainfall'];

echo "<div style='text-align:center'><h5>Temperature:  " . $temperature . "(F)" . "<br>" . "Rainfall: " . $total_rainfall . "(in)" . "<br>" . "Wind: " . $windspeed . "(MPH)" . "<br>" .  "Humidity: " . $humidity . "(%)" . "<br>" .  "</h5></div>";
echo "<div style='text-align:right'><h6>Updated at: " . $stamp . "</h6></div>";
echo "<br>";

}

?>

谢谢

Answer 1

我建议将其分为两个查询。

首先，收集您的所有数据：

SELECT * FROM weather ORDER BY stamp DESC LIMIT 1 

其次，要收集最小/最大数据：

SELECT
    SUM(rainfall) AS total_rainfall, 
    MAX(maxwind) AS max_windspeed, 
    MAX(temperature) AS max_temperature, 
    MIN(temperature)  AS min_temperature 
FROM 
    weather
WHERE stamp >= CURDATE())
LIMIT 1