×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

mysqli_multi_query not not returning any results

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.

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

Hi

Why using mysqli_multi_query() ?

Anyway, based on documentation, looks like you need a mysqli_store_result() call :

CODE --> PHP ( fragment )

        //Fetch rows from weather table
          mysqli_multi_query($conn, $sql);
          $result = mysqli_store_result($conn);
 
        $row_cnt = mysqli_num_rows($result);
        echo $row_cnt;
        exit; 

Feherke.
feherke.github.io

RE: mysqli_multi_query not not returning any results

(OP)
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.

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close