How do I ensure I caught all errors from MySQLi::multi_query?

Question

The docs for multi_query say:

Returns FALSE if the first statement failed. To retrieve subsequent errors from other statements you have to call mysqli_next_result() first.

The docs for next_result say:

Returns TRUE on success or FALSE on failure.

Finally, the example posted in the docs for multi_query use the return value from next_result to determine when there are no more queries; e.g. to stop looping:

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query  = "SELECT CURRENT_USER();";
$query .= "SELECT Name FROM City ORDER BY ID LIMIT 20, 5";

    /* execute multi query */
    if ($mysqli->multi_query($query)) {
        do {
            /* store first result set */
            if ($result = $mysqli->store_result()) {
                while ($row = $result->fetch_row()) {
                    printf("%s\n", $row[0]);
                }
                $result->free();
            }
            /* print divider */
            if ($mysqli->more_results()) {
                printf("-----------------\n");
            }
        } while ($mysqli->next_result()); // <-- HERE!
    }

    /* close connection */
    $mysqli->close();
    ?>

I don't know the number of queries provided, nor do I know anything about the SQL that I'm going to execute. I therefore can't just compare the number of queries against the number of returned results. Yet I want to display an error message to the user if, say, the third query was the broken query. But I don't seem to have a way to tell if next_result failed because there were no more queries to execute, or if it's because there was an error in the SQL syntax.

How can I check all the queries for errors?

Answer 1

Despite the code example in the docs, perhaps the better method would be something like this:

if ($mysqli->multi_query(...)) {
  do {
    // fetch results

    if (!$mysqli->more_results()) {
      break;
    }
    if (!$mysqli->next_result()) {
      // report error
      break;
    }
  } while (true);
}

Answer 2

There is no way to caught all errors, check out the example you use (which is from here)

This example has only "select" statements in it, which is not common for multi-statement scripts.

If you will put "insert", "update", "delete" or at least "set" - it will work differently.

"multi_query" - Returns FALSE if the first statement failed. FIRST STATEMENT - this is all we can control. All subsequent statement are mystery. If it is not a "select" statement - it will give an error on "$mysqli->store_result();" and we never know was it successful or not.

BUT

If you will have your SQL script in "START TRANSACTION; ... commit;" wrapper, you can be sure - if something fails, everything fails. This is good, this is helping us to see if "all fails".

To do this, just add in the end of you "insert - update" script a little "select", if last statement returns data - all script completed successfully.

Use SQL like:

START TRANSACTION;    
  set @q=1;
  select "success" as response from dual;
commit;

The PHP function:

function last_of_multi_query ($mysqli, $query) {
    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); //After that all mysql errors will be transferred into PHP exceptions.
    $mysqli->multi_query($query);               
    do { null; } while($mysqli->next_result());
    $result = $mysqli->store_result();
    if (!$result){
        throw new Exception('multi_query failed');
    }
    return $result;
}// end function