English
English
When performing database operations using PHP's MySQLi extension, prepared statements provide a safer and more efficient way to execute SQL. However, sometimes even when a statement executes successfully, there may still be underlying warnings that are not displayed by default. To capture these warnings, mysqli::$warning_count has proven to be a useful, albeit underutilized, tool.
This article will explain how to effectively debug potential issues in prepared statements by using mysqli::$warning_count in combination with mysqli_stmt_execute().
mysqli::$warning_count is a read-only property that returns the number of warnings generated by MySQL during the last database operation. This is especially useful for debugging non-fatal issues during SQL execution, such as data truncation or forced default value usage.
The syntax is as follows:
$mysqli = new mysqli("localhost", "user", "password", "database");
echo $mysqli->warning_count;
Here is a typical use case demonstrating how to execute a prepared statement with mysqli_stmt_execute() and check for warnings afterward.
$mysqli = new mysqli("localhost", "user", "password", "database");
$stmt = $mysqli->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);
<p>$name = "Alice";<br>
$email = "<a class="cursor-pointer" rel="noopener">example@example.com</a>";<br>
$stmt->execute();</p>
<p>if ($mysqli->warning_count > 0) {<br>
$result = $mysqli->query("SHOW WARNINGS");<br>
while ($row = $result->fetch_assoc()) {<br>
echo "Level: " . $row['Level'] . " - ";<br>
echo "Code: " . $row['Code'] . " - ";<br>
echo "Message: " . $row['Message'] . "\n";<br>
}<br>
}<br>
If the input data exceeds the column limit, warnings such as the following might appear:
Level: Warning - Code: 1265 - Message: Data truncated for column 'email' at row 1
In real-world projects, for example, when inserting data into a user system like m66.net, even though the code may seem to execute without issues, there may still be discrepancies between the actual data in the database and what was expected, due to issues such as improper database structure or insufficient data cleaning. If the warning_count is not checked, these problems may go unnoticed, potentially leading to data loss or formatting errors.
Enable MySQL's STRICT_ALL_TABLES mode in the development environment to turn certain warnings into errors, helping to catch issues early in development.
Log the output of SHOW WARNINGS for later troubleshooting.
If you're using your own database wrapper class, consider creating a checkWarnings() method to centralize the handling of warnings after each execute() call.
Related Tags:
mysqli
