How to identify the query that caused the error using mysqli_multi_query?

Using a example from elsewhere on SO to better catch ‘hiding’ errors. While the code below will catch and return an error, is it possible to improve this to report for which query the error occurred?

With the code below, the output is:

Columns: 18
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FRO inventory' at line 1

Code being tested:

$query = "SELECT * FROM orders WHERE location = 'IN' ORDER BY orderNum DESC LIMIT 20;";
$query .= "SELECT * FRO inventory";             //  With error
$ord = array();
$invent = array();

if(mysqli_multi_query($link, $query)) {
    do {
        // fetch results
        if($result = mysqli_store_result($link)) {
           echo 'Columns: ' . mysqli_field_count($link) . "<br>"; 
           while($row = mysqli_fetch_assoc($result)) {
                if(count($row) > 17)
                    $orders[] = $row;
                elseif(count($row) == 6)
                    $inv[] = $row;
            }
        }
        if(!mysqli_more_results($link))
            break;
        if(!mysqli_next_result($link)) {
            // report error
            echo 'Error: ' . mysqli_error($link);
            break;
        }
    } while(true);
    mysqli_free_result($result);
}

Answer

Here is an approach that will not only improve the quality of your error messages, it will improve the way you handle your result sets.

$q["Orders"] = "SELECT * FROM orders WHERE location = 'IN' ORDER BY orderNum DESC LIMIT 20";
$q["Inventory"] = "SELECT * FRO inventory";

if (!$link = mysqli_connect("host", "user", "pass", "db")) {
    echo "Failed to connect to MySQL: " , mysqli_connect_error();
} elseif (mysqli_multi_query($link, implode(';', $q))) {
    do {
        $q_key = key($q);                                 // current query's key name (Orders or Inventory)
        if ($result = mysqli_store_result($link)) {       // if a result set... SELECTs do
            while ($row = mysqli_fetch_assoc($result)) {  // if one or more rows, iterate all
                $rows[$q_key][] = $row;
            }
            mysqli_free_result($result);
            echo "<div><pre>" . var_export($rows[$q_key], true) . "</pre></div>";
        }
    } while (next($q) && mysqli_more_results($link) && mysqli_next_result($link));
}
if ($mysqli_error = mysqli_error($link)) {                // check & declare variable in same step to avoid duplicate func call
    echo "<div style="color:red;">Query Key = " , key($q) , ", Query = " , current($q) , ", Syntax Error = $mysqli_error</div>";
}

Error on first query: If your first query tries to access a table that doesn’t exist in the nominated database like: ordersXYZ Array $rows will not exist, no var_export() will occur, and you will see this response:

Query Key = Orders, Query = SELECT * FROM ordersXYZ WHERE location=’IN’ ORDER BY orderNum DESC LIMIT 20, Syntax Error = Table ‘[someDB].ordersXYZ’ doesn’t exist

Error on second query: If your first query is successful, but your second query tries to access a non-existent table like: inventory2
$rows["Orders"] will hold the desired row data and will be var_export()‘ed, $row["Inventory"] will not exist, and you will see this response:

Query Key = Inventory, Query = SELECT * FROM inventory2, Syntax Error = Table ‘[someDB].inventory2’ doesn’t exist

No errors: If both queries are error free, your $rows array will be filled with the desired data and var_export()‘ed, and there will be no error response. With the queried data saved in $rows, you can access what you want from $rows["Orders"] and $rows["Inventory"].

---

Things to note:

1. You may notice that I am making variable declarations and conditional checks at the same time, this makes the code more concise but some devs prefer to avoid this.

2. As my approach uses implode() with a semi-colon on the elseif line, be sure not to add a trailing semi-colon to your queries.

3. This set of queries always returns a result set because all are SELECT queries, if you have a mixed collection of queries that affect_rows, you may find some useful information at this link(https://stackoverflow.com/a/22469722/2943403).

4. mysqli_multi_query() will stop running queries as soon as there is an error. If you are expecting to catch “all” errors, you will discover that there will never be more than one.

5. Writing conditional break points like in the OP’s question and solution is not advisable. While custom break points may be rightly used in other circumstances, for this case the break points should be positioned inside of the while() statement of the do() block.

6. A query that returns zero rows will not cause a error message — it just won’t create any subarrays in $rows because the while() loop will not be entered.

7. By using the key() function, the OP’s if/elseif condition that counts the columns in each resultset row can be avoided. This is better practice because running a condition on every iteration can become expensive in some cases. Notice that the array pointer is advanced inside of $q at the end of each do() iteration. This is an additional technique that you will not find on the php manual page; it allows key() to work as intended.

8. And, of course, the <div><pre>var_export()...</pre></div> line can be removed from your working code — it was purely for demonstration.

9. If you are going to run any more queries after this code block that reuse variables, be sure to clear all used variables so that residual data does not interfere. e.g. $mysqli_error=null; // clear errors & reset($q); // reset array pointer.

10. Take heed to this somewhat vague warning at your own discretion: http://php.net/manual/en/mysqli.use-result.php :

One should not use mysqli_use_result() if a lot of processing on the client side is performed, since this will tie up the server and prevent other threads from updating any tables from which the data is being fetched.

11. Lastly and MOST IMPORTANTLY for security reasons, do not display query or query error information publicly — you don’t want sinister people to see this kind of feedback. Equally important, always protect your queries from injection hacks. If your queries include user-provided data, you need to filter/sanitize the data to death before using it in mysqli_multi_query(). In fact when dealing with user input, my very strong recommendation is to move away from mysqli_multi_query() and use either mysqli or pdo prepared statements for your database interactions for a higher level of security.