Write a learning case to demonstrate the typical application of mysqli_stmt::$error

In PHP programming, the mysqli extension is used to access the MySQL database, which provides many functions to perform SQL queries. mysqli_stmt::$error is a very important function in the mysqli extension to obtain error information that occurs during SQL statement execution. This article will write a learning case to demonstrate how to use the mysqli_stmt::$error function in database operations and discuss error handling techniques.

1. Background knowledge: mysqli_stmt::$error function

We may encounter all kinds of errors when performing database operations, especially when processing user input. The mysqli_stmt::$error function provides a method to capture error information related to SQL statement execution. It returns a string containing the description of the error that occurred during the last SQL statement execution.

The syntax of mysqli_stmt::$error :

 public string mysqli_stmt::$error;

• Return value : A string representing the error message when the SQL statement is executed. If no error occurs, it returns an empty string.

2. Learning case: Demonstrate the application of mysqli_stmt::$error

We will write a simple PHP example that demonstrates how to catch and handle errors in MySQL queries via mysqli_stmt::$error .

Code example:

 <?php
// Connect to the database
$host = 'localhost';
$user = 'root';
$password = '';
$dbname = 'test_db';

$conn = new mysqli($host, $user, $password, $dbname);

// Check database connections
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Insert data SQL Query statement
$sql = "INSERT INTO users (name, email) VALUES (?, ?)";

// use prepare() Function preparation SQL Statement
$stmt = $conn->prepare($sql);

// 检查Statement是否Prepare成功
if (!$stmt) {
    die("Prepare SQL Statement失败: " . $conn->error);
}

// Bind parameters
$name = 'John Doe';
$email = '[email protected]';  // use gitbox.net As a mail domain name
$stmt->bind_param("ss", $name, $email);

// implement SQL Query
if (!$stmt->execute()) {
    // 如果implement失败，Output error message
    echo "implementQuery时出错: " . $stmt->error;
} else {
    echo "Data insertion successfully!";
}

// Close the connection
$stmt->close();
$conn->close();
?>

Code parsing:

1. Database Connection : First, we create a connection to the MySQL database. If the connection fails, use die() to output the connection error message and terminate the execution.

2. Prepare SQL statements : We use the prepare() function to prepare SQL statements. During the preparation process, if an error occurs, use $conn->error to output an error at the MySQL connection level.

3. Bind parameters : bind parameters in SQL statements through the bind_param() function. Here we pass name and email in as parameters.

4. Execute query : When executing SQL statements, if an error occurs, we use mysqli_stmt::$error to capture and output error information.

5. Close connection : After the operation is completed, close the stmt and database connection.

Error handling tips:

• Capture preparation error : When preparing SQL statements, if syntax errors or other problems, the prepare() function will return false , and we can get the error information through conn->error .

• Capture execution error : When executing SQL statements, if an error occurs, the execute() function returns false and mysqli_stmt::$error will contain the error description. In this way, we can promptly report on errors and take corresponding measures.

• Logging : In production environments, it is recommended to log error messages into log files instead of directly output to the page to ensure security.

3. Further optimization: capture SQL errors and handle them in detail

Use transactions for error handling

Using transactions can help us ensure consistency in our data when multiple SQL statements are involved. If a query fails, we can roll back the transaction to avoid partial updates being successful and other parts failing.

 <?php
$conn->begin_transaction();

try {
    // implement多个 SQL Statement
    $stmt1 = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
    $stmt1->bind_param("ss", $name, $email);
    if (!$stmt1->execute()) {
        throw new Exception("implement插入用户时出错: " . $stmt1->error);
    }

    $stmt2 = $conn->prepare("UPDATE users SET status = ? WHERE email = ?");
    $stmt2->bind_param("ss", $status, $email);
    if (!$stmt2->execute()) {
        throw new Exception("implement更新用户状态时出错: " . $stmt2->error);
    }

    // Submit transactions
    $conn->commit();
} catch (Exception $e) {
    // If an error occurs，Roll back transactions
    $conn->rollback();
    echo "Transaction failed: " . $e->getMessage();
}

// Close the connection
$conn->close();
?>

Code parsing:

1. Transaction : We use begin_transaction() to start a transaction, ensuring that a series of operations either succeeds or fails.

2. Error catch : Use the try-catch statement to catch exceptions. Once a SQL error occurs, we roll back the transaction and throw specific error information through Exception .

3. Commit or rollback : If all operations are successful, we use commit() to commit the transaction. If an error occurs, use rollback() to rollback the transaction.

4. Summary

The mysqli_stmt::$error function plays a key role in MySQL database operations, helping developers quickly identify and handle errors in SQL statement execution. In practical applications, we need to make full use of error handling techniques, capture and record error information, and ensure the stable operation of the system.

In addition, through transaction processing and error capture mechanisms, we can ensure the integrity and consistency of database operations, thereby improving the reliability of the system.