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; 
Talk To Other Members
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
// Create connection with variables in _your_keys.php $conn = new mysqli($servername, $username, $password, $dbname); $sql = "SET @rain = 0.00;"; $sql .= " 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 ) A "; //Fetch rows from weather table $result = mysqli_multi_query($conn, $sql); if ($result) { do { // grab the result of the next query if (($result = mysqli_store_result($conn)) === false && mysqli_error($conn) != '') { echo "Query failed: " . mysqli_error($conn); } } while (mysqli_more_results($conn) && mysqli_next_result($conn)); // while there are more results $row_cnt = mysqli_num_rows($result); echo $row_cnt; } else { echo "First query failed..." . mysqli_error($conn); } mysqli_close($conn);