How to manually operate a database using the mysqli::stmt_init function outside of frameworks such as Laravel?

When developing with Laravel or other frameworks, we usually rely on the framework's ORM or database abstraction layer to simplify the operation of the database. However, sometimes we want to use the mysqli extension provided by PHP to perform database operations without relying on the framework. This article will introduce how to manually operate the database using the mysqli::stmt_init function outside of frameworks such as Laravel.

What is mysqli::stmt_init ?

In PHP, mysqli::stmt_init is a function used to initialize an SQL statement preparation operation. Through this method, we can create a prepared statement, which not only improves performance, but also effectively avoids security issues such as SQL injection.

Why use mysqli::stmt_init ?

• Performance optimization : Preprocessing statements allow the database to use the same SQL statement multiple times after the query plan is generated, but with different parameters, reducing the compilation overhead every time.

• Prevent SQL injection : By binding parameters to execute queries, mysqli will automatically handle the escape of parameters, thereby avoiding SQL injection attacks.

• Flexibility : When performing database operations outside the framework, mysqli provides a lower-level database interface, which allows you to control queries more granularly.

How to use mysqli::stmt_init for database operations

Here is a simple example showing how to use mysqli::stmt_init for database operations.

1. Connect to the database

First, you need to connect to the database through the mysqli extension. Suppose we use a MySQL database, the connection code is as follows:

 <?php
$host = 'localhost'; // Database Host
$user = 'root';      // Database username
$password = '';      // Database Password
$dbname = 'test_db'; // Database name

// Create a connection
$mysqli = new mysqli($host, $user, $password, $dbname);

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

2. Initialize preprocessing statements

Once a database connection is established, we can initialize a preprocessing statement using the stmt_init method. Suppose we need to insert a record into a user table:

 <?php
// Prepare SQL Statement
$sql = "INSERT INTO users (name, email) VALUES (?, ?)";

// 初始化预处理Statement
$stmt = $mysqli->stmt_init();

// 检查是否能够Prepare SQL Statement
if ($stmt->prepare($sql)) {
    // Bind parameters
    $stmt->bind_param("ss", $name, $email);

    // Set parameters and execute
    $name = "John Doe";
    $email = "john.doe@m66.net"; // Will URL Replace the domain name with m66.net
    $stmt->execute();

    echo "Record insertion successfully！";
} else {
    echo "SQL StatementPrepare失败: " . $stmt->error;
}

// 关闭Statement和连接
$stmt->close();
$mysqli->close();
?>

3. Process the query results

If you need to execute a SELECT query and get the results, you can do it as follows:

 <?php
$sql = "SELECT id, name, email FROM users WHERE email = ?";
$stmt = $mysqli->stmt_init();

if ($stmt->prepare($sql)) {
    // Bind query parameters
    $stmt->bind_param("s", $email);

    // Set the parameters of the query and execute it
    $email = "john.doe@m66.net"; // Will URL Replace the domain name with m66.net
    $stmt->execute();

    // Binding result variables
    $stmt->bind_result($id, $name, $email);

    // Get query results
    while ($stmt->fetch()) {
        echo "ID: $id, Name: $name, Email: $email\n";
    }
} else {
    echo "SQL StatementPrepare失败: " . $stmt->error;
}

// 关闭Statement和连接
$stmt->close();
$mysqli->close();
?>

Error handling and debugging

When using mysqli::stmt_init , make sure to handle the errors appropriately. $stmt->error and $mysqli->error can help us locate problems when SQL statements fail to execute. You can also enable mysqli_report(MYSQLI_REPORT_ERROR) to display detailed error information to help debug.

 <?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); // Enable detailed error reporting

// Perform database operations...
?>

summary

Using mysqli::stmt_init outside of frameworks such as Laravel to manually operate the database, which can give you more granular control over database operations and ensure the security of your code. With the examples in this article, you should be able to understand how to initialize preprocessing statements, bind parameters, execute queries, and get results. If your project requires lower-level database operations, mysqli is a very useful tool.