How to Effectively Handle Multiple Errors in mysqli_stmt::$error_list?

gitbox 2025-08-22

When executing prepared statements with PHP’s MySQLi extension, mysqli_stmt::$error_list is an extremely important debugging tool. Instead of just returning the last error like $stmt->error, it provides an array containing all error messages. This becomes especially critical when multiple errors occur during a single execution or preparation.

However, developers often feel confused when faced with multiple errors returned by $stmt->error_list: how should these errors be effectively extracted, handled, and displayed in a way that accurately pinpoints the issue without making the error-handling logic messy?

This article explores strategies for handling multiple errors from the following perspectives:

1. Understanding the Structure of error_list

$stmt->error_list returns an array where each element is an associative array containing the following keys:

errno: Error code (integer)
sqlstate: SQLSTATE error identifier (string)
error: Error message (string)

A typical structure looks like this:

<span><span>[
    [
        </span><span><span class="hljs-string">&#039;errno&#039;</span></span><span> =&gt; </span><span><span class="hljs-number">1064</span></span><span>,
        </span><span><span class="hljs-string">&#039;sqlstate&#039;</span></span><span> =&gt; </span><span><span class="hljs-string">&#039;42000&#039;</span></span><span>,
        </span><span><span class="hljs-string">&#039;error&#039;</span></span><span> =&gt; </span><span><span class="hljs-string">&#039;You have an error in your SQL syntax; check the manual...&#039;</span></span><span>
    ],
    [
        </span><span><span class="hljs-string">&#039;errno&#039;</span></span><span> =&gt; </span><span><span class="hljs-number">1048</span></span><span>,
        </span><span><span class="hljs-string">&#039;sqlstate&#039;</span></span><span> =&gt; </span><span><span class="hljs-string">&#039;23000&#039;</span></span><span>,
        </span><span><span class="hljs-string">&#039;error&#039;</span></span><span> =&gt; </span><span><span class="hljs-string">&#039;Column \&#039;name\&#039; cannot be null&#039;</span></span><span>
    ]
]
</span></span>

This means you need to evaluate and handle each error separately, rather than assuming there’s only one error.

2. Encapsulating Error Output Logic

To avoid repeating error-handling logic in every SQL execution, it’s best to encapsulate the handling in a function, such as:

<span><span><span class="hljs-function"><span class="hljs-keyword">function</span></span></span><span> </span><span><span class="hljs-title">handle_stmt_errors</span></span><span>(</span><span><span class="hljs-params">mysqli_stmt <span class="hljs-variable">$stmt</span></span></span><span>) {
    </span><span><span class="hljs-keyword">foreach</span></span><span> (</span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;error_list </span><span><span class="hljs-keyword">as</span></span> </span><span><span class="hljs-variable">$error</span></span><span>) {
        </span><span><span class="hljs-title function_ invoke__">error_log</span></span><span>(</span><span><span class="hljs-string">"[SQL Error] Code: <span class="hljs-subst">{$error[&#039;errno&#039;]}</span></span></span><span> | SQLSTATE: </span><span><span class="hljs-subst">{$error[&#039;sqlstate&#039;]}</span></span><span> | Message: </span><span><span class="hljs-subst">{$error[&#039;error&#039;]}</span></span><span>");
    }
}
</span></span>

This way, whenever execute() or prepare() fails, you can simply call:

<span><span><span class="hljs-keyword">if</span></span><span> (!</span><span><span class="hljs-variable">$stmt</span></span>-&gt;<span><span class="hljs-title function_ invoke__">execute</span></span>()) {
    </span><span><span class="hljs-title function_ invoke__">handle_stmt_errors</span></span>(<span class="hljs-variable">$stmt</span>);
}
</span></span>

3. Categorizing Errors by Severity

Not all errors are equally important. It’s recommended to categorize based on errno, for example:

Logical errors (e.g., empty data, unique constraint violations): Can be reported to users or logged as business exceptions.
Syntax errors or database failures (e.g., SQL typos, lost connections): Should trigger alerts or terminate the request immediately.

You can use a simple mapping table to determine severity:

<span><span>$criticalErrors</span> = [<span>1064</span>, <span>2006</span>, <span>2013</span>];
<p>foreach ($stmt->error_list as $error) {<br>
if (in_array($error['errno'], $criticalErrors)) {<br>
trigger_error("Critical database error: {$error['error']}", E_USER_ERROR);<br>
} else {<br>
error_log("Non-critical error: {$error['error']}");<br>
}<br>
}<br>

4. Don’t Overlook prepare() Errors

One common oversight is that prepare() failures can also produce multiple errors. You cannot rely solely on a false return value—you must also check error_list:

<span><span>$stmt</span> = <span>$mysqli</span>-&gt;<span class="hljs-title function_ invoke__">prepare</span>(<span>$sql</span>);
<span><span class="hljs-keyword">if</span> (!<span>$stmt</span>) {
    <span class="hljs-keyword">foreach</span> (<span>$mysqli</span>-&gt;error_list <span class="hljs-keyword">as</span> <span>$error</span>) {
        <span class="hljs-title function_ invoke__">error_log</span>("Prepare Error: {$error[&#039;error&#039;]}");
    }
}
</span>

5. Enable Detailed Logging in Development

During development, it’s advisable to display error logs in the browser or console, but in production they should only be recorded in logs without being exposed:

<span><span class="hljs-function"><span class="hljs-keyword">function</span></span></span> <span>log_stmt_errors</span>(<span class="hljs-params">mysqli_stmt $stmt</span>, $display = false) {
    <span class="hljs-keyword">foreach</span> ($stmt-&gt;error_list as $error) {
        $message = "[Error {$error[&#039;errno&#039;]}] {$error[&#039;error&#039;]} (SQLSTATE: {$error[&#039;sqlstate&#039;]})";
        error_log($message);
        <span class="hljs-keyword">if</span> ($display) {
            echo "&lt;div class=\"sql-error\"&gt;$message&lt;/div&gt;";
        }
    }
}
</span>

6. Conclusion

mysqli_stmt::$error_list is a subtle yet powerful tool for diagnosing issues. When running complex SQL operations, binding multiple parameters, or joining several tables, errors often come in multiples. Parsing and categorizing these errors properly not only helps fix bugs quickly but also improves system robustness.

Stop relying solely on $stmt->error—by fully leveraging error_list, you’ll discover a whole new level of efficiency in debugging SQL.

Related Tags:
mysqli_stmt

mysqli_stmt::$error_list