Letting a PHP multi Query continue if errors occur

Question

I got this Code:

// Some starting variables
$test = new mysqli("localhost","root","","testdatabase");
$Query = array();
$Query[] = "SELECT 'wos' FROM items WHERE itemID = 3";
$Query[] = "SELECT 'wos' FROM items WHERE itemID";
$Query[] = "SELECT 'wos' FROM items WHERE itemID = 5";
$Query = implode(";",$Query);
$Errors = array();

// Execute multi query

if ($test->multi_query($Query)) {
  do {
    // fetch results
    $Result = $test->store_result();
    print_r($Result);
    if($test->errno === 0) {
          echo $Result->num_rows . "<br>";
    }
    else {
        $Errors[] = $test->error;
    }
    $Result->close();
    
    if (!$test->more_results()) {
      break;
    }
    if (!$test->next_result()) {
      $Errors[] = $test->error;
      break;
    }
  } while (true);
}

As you can see I have a number of X subsequent queries and want to perform them all. So if query 2 fails I want to continue the multi-query to let the following queries run. How can I do this?

Also, if an error occurs I want to save this error in $Errors. However, currently it isn't quite working as intended since I can only save the error message but not the corresponding query. Only the error message isn't really helpful without the query which failed. So I want to have something like: "Query 3 failed ('SELECT ...'): 'ERROR MESSAGE'".

How is this possible? I am able to get the current error in the loop with $test->error, but I don't have anything like $test->currentsubsequentquery. How can I do that?

I don't see anywhere where $test is defined. Or any of the methods it's calling. Impossible to help if we don't know what your code is doing. — Kyle, Jan 10 '13 at 12:41
If SQL errors are a possible, expected result then you're doing something wrong. And if you're doing something like an SQL client (or terminal) why do you really need to execute that many queries at once? — Andrei Bârsan, Jan 10 '13 at 12:49

score 1 · Answer 1 · answered Jan 10 '13 at 15:29

The issue here is something which could be called a limitation in the MySQL protocol. When using multi query (from MySQL perspective: Setting the multi statements option to On and then send a query) the server will split this and execute them one after another. The answers will be sent without reference to the origin. The only way a client (in this case PHP/mysqli) could figure out to which actual statement an error relates it would have to parse the fully query string and try to do some mapping. But this won't work in many cases - some statements, most notably CALL, can return multiple result sets which can't be mapped to a statement.

If you want to know more about the error there are two choices:

The ugly one is doing $Query = implode(";\n\n",$Query); and then parse the error message for the provided line and match those.

Or simply not use multi_query. multi_query is meant for handling stored procedure calls returning multiple result sets. Using multi_query in a case like the above will save you only a tiny little latency as the server can directly process the next query without waiting for the next query command, but even that can be reached by using mysqlnd's async query operations.

score -1 · Answer 2 · answered Jan 10 '13 at 15:32

Well, multiple queries in a single statement is a generally not a good idea. Good SQL clients (e.g. PDO) do not allow multiple queries at all because this way SQL injection is made much more difficult. I'd like to dissuade you from your idea and propose one of the following solutions:

Use three single statements
Since your three queries are very similar, you should use a prepared statement and execute it three times with different parameter binding. This provides better performance than three single statements and makes SQL injection impossible.
You could design a one-for-all query like this or do anything with UNION:
```
"SELECT 'wos' FROM items WHERE itemID IN ('3', '5')"
```

If you now think "what's this guy talking about? This will not be atomic any more." then you should use a transaction...

"This will not be atomic any more." - multi_query isn't atomic either. — johannes, Jan 10 '13 at 15:42

Letting a PHP multi Query continue if errors occur

2 Answers2