The below query was working in myphpadmin, but when I moved it to .php page it did not. I found out why and tried to use the mysqli_multi_query, but the query still returns "Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result"
Can someone explain what I am doing wrong? Or is there an alternative to make this a single query? The hosting server is still using mySQL version 5.6 which doesn't support LAG.
Can someone explain what I am doing wrong? Or is there an alternative to make this a single query? The hosting server is still using mySQL version 5.6 which doesn't support LAG.
Code:
$conn = new mysqli($servername, $username, $password, $dbname);
$sql = "SET @rain = 0.00";
$sql .= "
SELECT
ObsDate,
Location,
CASE WHEN Moist12 > 0 THEN Moist12 ELSE '' END Moist12,
CASE WHEN Moist24 > 0 THEN Moist24 ELSE '' END Moist24,
CASE WHEN Moist36 > 0 THEN Moist36 ELSE '' END Moist36,
CASE WHEN Temp4 > 0 THEN Temp4 ELSE '' END Temp4,
CASE WHEN Temp12 > 0 THEN Temp12 ELSE '' END Temp12,
CASE WHEN Temp24 > 0 THEN Temp24 ELSE '' END Temp24,
CASE WHEN Temp36 > 0 THEN Temp36 ELSE '' END Temp36,
CAST((curr_Rain - lag_Rain) AS DECIMAL(4,2)) AS IntervalRain
FROM
(SELECT
ObsDate,
Location,
SUM(CASE WHEN Depth = 4 AND Type = 'Temperature' THEN Measurement ELSE 0 END) Temp4,
SUM(CASE WHEN Depth = 12 AND Type = 'Temperature' THEN Measurement ELSE 0 END) Temp12,
SUM(CASE WHEN Depth = 24 AND Type = 'Temperature' THEN Measurement ELSE 0 END) Temp24,
SUM(CASE WHEN Depth = 36 AND Type = 'Temperature' THEN Measurement ELSE 0 END) Temp36,
SUM(CASE WHEN Depth = 12 AND Type = 'Moisture' THEN Measurement ELSE 0 END) Moist12,
SUM(CASE WHEN Depth = 24 AND Type = 'Moisture' THEN Measurement ELSE 0 END) Moist24,
SUM(CASE WHEN Depth = 36 AND Type = 'Moisture' THEN Measurement ELSE 0 END) Moist36
FROM tblSoilSample
WHERE
YEAR(ObsDate) = '2022'
GROUP BY ObsDate, Location
) A
LEFT JOIN
(
SELECT
WxDate,
@rain lag_Rain,
@rain:=RainDay curr_Rain
FROM
(
SELECT
WxDate,
SUM(RainCur) AS RainDay
FROM weatherbridge
WHERE
YEAR(WxDate) = '2022'
GROUP BY WxDate
) B
)C
ON A.ObsDate = C.WxDate";
//Fetch rows from weather table
$result = mysqli_multi_query($conn, $sql);
$row_cnt = mysqli_num_rows($result);
echo $row_cnt;
exit;