php - Strict Standards: mysqli_next_result() error with mysqli_multi_query

Question

I have tried using multi_query but I keep getting a strict Standards message popping up.

$querystring = "INSERT INTO responses VALUES('1', '2', '3', '4'); INSERT INTO responses VALUES('1', '2', '3', '4')";

if (mysqli_multi_query($db, $querystring)) {
   do {
       if ($result = mysqli_store_result($db)) {
           //
       }
   } while (mysqli_next_result($db));
}
echo "end";

The error message I get is:

Strict Standards: mysqli_next_result(): There is no next result set. Please, call mysqli_more_results()/mysqli::more_results() to check whether to call this function/method

I've tried adding and removing -; but had no luck.

Answer 1

While pipodesign corrected the error within the $querystring and alleviated the problem, the actual solution was not provided regarding the Strict Standards error.

I disagree with SirBT's advice, changing from DO WHILE to WHILE is not necessary.

The Strict Standards message that you receive is quite informative. To obey, use this:

do{} while(mysqli_more_results($db) && mysqli_next_result($db));

Then, there is no need for you to write a conditional exit or break inside of the loop because the while condition will break the loop on the first occurrence of an error. *note, the if statement before the do-while will deny entry to the loop if the first query has an error.

In your example, you are only running INSERT queries, so you won't receive any result sets to process. If you want to count how many rows you've added, use mysqli_affected_rows().

As a complete solution for your question:

if(mysqli_multi_query($db,$querystring)){
    do{
        $cumulative_rows+=mysqli_affected_rows($db);
    } while(mysqli_more_results($db) && mysqli_next_result($db));
}
if($error_mess=mysqli_error($db)){echo "Error: $error_mess";}
echo "Cumulative Affected Rows: $cumulative_rows";

Output:

 // if no errors
Cumulative Affected Rows: 2

// if error on second query
Error: [something]
Cumulative Affected Rows: 1

// if error on first query
Error: [something]
Cumulative Affected Rows: 0

---

LATE EDIT:

Since people new to mysqli are stumbling across this post, I'll offer a general yet robust snippet to handle queries with/without result sets using multi_query() and add a feature to display which query in the array is being handled...

Classic "IF(){DO{} WHILE}" Syntax:

if(mysqli_multi_query($mysqli,implode(';',$queries))){
    do{
        echo "<br><br>",key($queries),": ",current($queries);  // display key:value @ pointer
        if($result=mysqli_store_result($mysqli)){   // if a result set
            while($rows=mysqli_fetch_assoc($result)){
                echo "<br>Col = {$rows["Col"]}";
            }
            mysqli_free_result($result);
        }
        echo "<br>Rows = ",mysqli_affected_rows($mysqli); // acts like num_rows on SELECTs
    } while(next($queries) && mysqli_more_results($mysqli) && mysqli_next_result($mysqli));
}
if($mysqli_error=mysqli_error($mysqli)){
    echo "<br><br>",key($queries),": ",current($queries),"Syntax Error:<br>$mysqli_error";  // display array pointer key:value
}
//if you want to use the snippet again...
$mysqli_error=null; // clear variables
reset($queries); // reset pointer

---

Reinvented Wheel "WHILE{}" Syntax (...for those who don't like post-test loops):

while((isset($multi_query) && (next($queries) && mysqli_more_results($mysqli) && mysqli_next_result($mysqli))) || (!isset($multi_query) && $multi_query=mysqli_multi_query($mysqli,implode(';',$queries)))){
    echo "<br><br>",key($queries),": ",current($queries);  // display array pointer key:value
    if($result=mysqli_store_result($mysqli)){
        while($rows=mysqli_fetch_assoc($result)){
            echo "<br>Col = {$rows["Col"]}";
        }
        mysqli_free_result($result);
    }
    echo "<br>Rows = ",mysqli_affected_rows($mysqli); // acts like num_rows on SELECTs
}
if($mysqli_error=mysqli_error($mysqli)){
    echo "<br><br>",key($queries),": ",current($queries),"Syntax Error:<br>$mysqli_error";  // display array pointer key:value
}
//if you want to use the snippet again...
$multi_query=$mysqli_error=null; // clear variables
reset($queries); // reset pointer

So, either snippet given the following queries will offer the same output:

Query array:

$queries[]="SELECT * FROM `TEST`";
$queries[]="INSERT INTO `TEST` (Col) VALUES ('string1'),('string2')";
$queries[]="SELECT * FROM `TEST`";
$queries[]="DELETE FROM `TEST` WHERE Col LIKE 'string%'";

Output:

0: SELECT * FROM `TEST`
Rows = 0

1: INSERT INTO `TEST` (Col) VALUES ('string1'),('string2')
Rows = 2

2: SELECT * FROM `TEST`
Col = string1
Col = string2
Rows = 2

3: DELETE FROM `TEST` WHERE Col LIKE 'string%'
Rows = 2

Modify my snippets per your needs. Leave a comment if you discover a bug.