Use $errno and mysqli_stmt to determine whether the preprocessing statement fails

Preprocessing statements provide a safe way to prevent SQL injection attacks when using PHP for database operations. Using the mysqli extension, you can execute preprocessing statements through the mysqli_stmt object. When executing preprocessing statements, it is sometimes necessary to determine whether the statement is successfully executed. mysqli::$errno and mysqli_stmt are powerful tools for us to judge execution failures.

This article will introduce how to use these two to determine whether a preprocessing statement has failed and provide corresponding code examples.

1. What are mysqli::$errno and mysqli_stmt ?

• mysqli::$errno : This is a property of mysqli class that returns the error code that occurred in the last MySQL operation. If no error occurs, the value will be 0 .

• mysqli_stmt : This is a class provided by the mysqli extension, representing a preprocessing statement. You can execute SQL queries through this class and pass parameters.

Through these two tools, you can check whether an error has occurred after executing a SQL query, thereby handling related exceptions.

2. Use mysqli::$errno to determine whether there is any error

First, let's take a look at how to use mysqli::$errno to determine whether the database operation fails.

 <?php
// Connect to the database
$mysqli = new mysqli('localhost', 'username', 'password', 'database_name');

// Check if the connection is successful
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Write preprocessing statements
$stmt = $mysqli->prepare("SELECT * FROM users WHERE email = ?");

// Check whether the preprocessing statement is successful
if ($stmt === false) {
    die("Prepare failed: " . $mysqli->errno . " - " . $mysqli->error);
}

// Bind parameters
$email = 'test@example.com';
$stmt->bind_param('s', $email);

// Execution statement
$stmt->execute();

// Check whether the execution is successful
if ($stmt->errno) {
    echo "Error occurred: " . $stmt->errno . " - " . $stmt->error;
} else {
    echo "Query executed successfully!";
}

// Close statements and connections
$stmt->close();
$mysqli->close();
?>

3. Explain the code

• We first connect to the MySQL database. If the connection fails, check the error via $mysqli->connect_error .

• Then, we prepare an SQL query statement and check whether prepare() succeeds. If it fails, we use $mysqli->errno to get the error code and output the error message.

• After binding the parameters, we execute the statement and use $stmt->errno to determine whether there are any errors during the execution process. If the value of errno is greater than 0, it means that the execution has failed. You can get the error details through $stmt->error .

4. Error handling using mysqli_stmt

In addition to $mysqli::$errno , the mysqli_stmt object itself also provides errno and error attributes to judge the execution status of the preprocessing statement. We can use mysqli_stmt::errno to capture the error code during execution.

 <?php
// Create a connection
$mysqli = new mysqli('localhost', 'username', 'password', 'database_name');

// Check if the connection is successful
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Preparation statement
$stmt = $mysqli->prepare("INSERT INTO users (name, email) VALUES (?, ?)");

// Bind parameters
$name = 'John Doe';
$email = 'john@example.com';
$stmt->bind_param('ss', $name, $email);

// Execution statement
if (!$stmt->execute()) {
    echo "Execute failed: " . $stmt->errno . " - " . $stmt->error;
} else {
    echo "Insert successful!";
}

// Close the resource
$stmt->close();
$mysqli->close();
?>

In the above example, $stmt->execute() returns a boolean value. If the execution fails, the error message is output through $stmt->errno and $stmt->error .

5. Summary

• Use mysqli::$errno and mysqli_stmt to effectively capture errors during MySQL execution.

• Through mysqli_stmt::errno and mysqli_stmt::error , the cause of failure can be clearly obtained and the corresponding processing can be performed.

• These tools can help you better debug and handle errors in database operations, ensuring the robustness and security of your code.

I hope this article will be helpful to you using mysqli extension for database operations!