How to encapsulate stmt_init as a general database operation class

In PHP development, database operations are an indispensable part, and common operations include addition, deletion, modification, and search. However, traditional database interaction methods often have problems such as code redundancy, SQL injection vulnerabilities, and performance bottlenecks. In order to improve database interaction efficiency and enhance code reusability, we can use the mysqli::stmt_init method to encapsulate a general database operation class. In this way, not only can efficiency be improved, but SQL injection vulnerabilities can also be prevented.

In this article, we will introduce in detail how to use the mysqli::stmt_init function to create a common database operation class, and make database interaction more efficient and secure through reasonable encapsulation.

1. What is the mysqli::stmt_init function?

mysqli::stmt_init is a method in the MySQLi extension to initialize a mysqli_stmt object. The mysqli_stmt object represents a prepared SQL statement that is more efficient than traditional query methods when executing SQL queries, especially when multiple similar SQL queries need to be executed. Preprocessing statements can reduce the time of SQL parsing and improve execution efficiency.

2. Advantages of using mysqli::stmt_init

• Performance improvement : Through preprocessing statements, MySQL compiles and optimizes SQL queries once without parsing them every time.

• Prevent SQL injection : By binding parameters, it avoids directly inserting user input data, thereby effectively preventing SQL injection attacks.

• Code reusability : After encapsulating the database operation class, it can be reused in different places to improve the reusability of the code.

3. Encapsulate common database operation classes

First, we need to create a database connection class and encapsulate SQL operations using mysqli::stmt_init .

3.1 Database connection class

 class Database {
    private $mysqli;
    
    public function __construct($host, $username, $password, $dbname) {
        // Create a database connection
        $this->mysqli = new mysqli($host, $username, $password, $dbname);
        
        // Check the connection
        if ($this->mysqli->connect_error) {
            die("Connection failed: " . $this->mysqli->connect_error);
        }
    }

    // Prepare SQL Statement and execute
    public function executeQuery($sql, $params = []) {
        // Initialization statement
        $stmt = $this->mysqli->stmt_init();
        
        // Prepare SQL Statement
        if (!$stmt->prepare($sql)) {
            die("Error in preparing statement: " . $stmt->error);
        }

        // Bind parameters
        if (!empty($params)) {
            // Get parameter type
            $types = str_repeat('s', count($params));  // Assume that all parameters are string types
            $stmt->bind_param($types, ...$params);
        }

        // Execute a query
        if ($stmt->execute()) {
            // Return result set
            return $stmt->get_result();
        } else {
            die("Error in executing statement: " . $stmt->error);
        }
    }

    // Close the database connection
    public function close() {
        $this->mysqli->close();
    }
}

3.2 Use this class to perform database operations

In the above code, we encapsulate a database operation class Database that can execute any SQL query. When using it, we only need to pass in SQL statements and parameters.

For example, insert a data:

 $db = new Database('localhost', 'root', 'password', 'm66.net');

// Insert data
$sql = "INSERT INTO users (name, email) VALUES (?, ?)";
$params = ['John Doe', 'johndoe@m66.net'];
$db->executeQuery($sql, $params);

Query data:

 $sql = "SELECT * FROM users WHERE email = ?";
$params = ['johndoe@m66.net'];
$result = $db->executeQuery($sql, $params);

// Output result
while ($row = $result->fetch_assoc()) {
    echo "Name: " . $row['name'] . ", Email: " . $row['email'] . "<br>";
}

$db->close();

3.3 Extended functions: batch operation

In addition to executing a single SQL query, the encapsulated database operation class can also support batch operations. By adding a batch operation method to the class, we can perform multiple data operations efficiently.

 public function executeBatchQuery($sql, $paramsList) {
    // Initialization statement
    $stmt = $this->mysqli->stmt_init();
    
    // Prepare SQL Statement
    if (!$stmt->prepare($sql)) {
        die("Error in preparing statement: " . $stmt->error);
    }

    // Get parameter type
    $types = str_repeat('s', count($paramsList[0])); // Assume that all parameters are string types
    
    // Iterate over the parameter list and execute
    foreach ($paramsList as $params) {
        $stmt->bind_param($types, ...$params);
        $stmt->execute();
    }
    
    return true;
}

Insert data in batches: