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 catch all errors, check out the example you use (which is from here).

This example only contains SELECT statements, which is not common for multi-statement scripts.

If you also use INSERT, UPDATE, DELETE or at even SET - it will work differently.

multi_query - Returns false when the first statement fails. FIRST STATEMENT - this is all we can control. All subsequent statements are a 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 wrap your SQL script on a transaction (START TRANSACTION; ... commit;), you can be sure: if anything fails, everything fails. This is good, as this helps us to see if "all fails".

To do this, just add in the end of your "insert - update" script a little select, if last statement returns data: all scripts 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;
}

Answer 3

There is no way to catch all errors from mysqli_multi_query() because there will only ever be one error. If any of these queries fails, MySQL stops processing and returns an error.

If you have mysqli error reporting enabled then you will receive an exception either from multi_query() or from next_result(). You don't need to do anything.

If you are using manual error reporting then just check for errors after the loop.

    } while ($mysqli->next_result());
}
if ($mysqli->error) {
    // Handle the error here
}

To receive the information about the processing of each statement in the list you must call next_result(). The response from MySQL will either contain information about pending results, affected rows or the error message. If your code doesn't fetch all responses you will get "Out of sync" error message.