mysqli_multi_query not not returning any results

[waubain]

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.

    $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;

 

[feherke]

Hi

Why using [tt]mysqli_multi_query()[/tt] ?

Anyway, based on documentation, looks like you need a [tt]mysqli_store_result()[/tt] call :

[ ]       [gray]//Fetch rows from weather table[/gray]
          [COLOR=orange]mysqli_multi_query[/color][teal]([/teal][navy]$conn[/navy][teal],[/teal] [navy]$sql[/navy][teal]);[/teal]
          [highlight][navy]$result[/navy] [teal]=[/teal] [COLOR=orange]mysqli_store_result[/color][teal]([/teal][navy]$conn[/navy][teal]);[/teal][/highlight]
 
        [navy]$row_cnt[/navy] [teal]=[/teal] [COLOR=orange]mysqli_num_rows[/color][teal]([/teal][navy]$result[/navy][teal]);[/teal]
        [b]echo[/b] [navy]$row_cnt[/navy][teal];[/teal]
        [b]exit[/b][teal];[/teal]

Feherke.
feherke.github.io

 

[waubain]

I found this link which helped. It also explains the need for mysql_multi_query to prevent sql injection.
Link

I don't fully understand the syntax, but I got it working. Here is a part of the code.

               // 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);