mysqli_multi_query not not returning any results
mysqli_multi_query not not returning any results
(OP)
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 --> mySQL
$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;
RE: mysqli_multi_query not not returning any results
Why using mysqli_multi_query() ?
Anyway, based on documentation, looks like you need a mysqli_store_result() call :
CODE --> PHP ( fragment )
Feherke.
feherke.github.io
RE: mysqli_multi_query not not returning any results
Link
I don't fully understand the syntax, but I got it working. Here is a part of the code.
CODE --> mySQL