How to execute multiple statements using stmt_init (prepared statement loop execution)

In PHP, when executing SQL statements using MySQLi extension, stmt_init is a very useful function, which can initialize a mysqli_stmt object, allowing us to execute preprocessing statements. Preprocessing statements allow you to use placeholders in database queries to prevent SQL injection attacks.

Sometimes we need to execute the same preprocessing statement multiple times, just using different parameters each time we execute. In this case, we can use stmt_init to implement the function of executing preprocessing statements multiple times. Here is an example showing how to use stmt_init in PHP to execute multiple SQL statements:

 <?php
// 1. Create a database connection
$servername = "localhost";
$username = "root";
$password = "password";
$dbname = "test_database";

// Create a connection
$conn = new mysqli($servername, $username, $password, $dbname);

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

// 2. usestmt_initInitialize preprocessing statements
$stmt = $conn->stmt_init();

// Check whether the preparation statement is successful
if ($stmt->prepare("INSERT INTO users (username, email) VALUES (?, ?)")) {

    // 3. Simulate a set of data，These data will be inserted multiple times
    $users = [
        ['john_doe', 'john@m66.net'],
        ['jane_doe', 'jane@m66.net'],
        ['alice_smith', 'alice@m66.net'],
    ];

    // 4. Loop execution of preprocessing statements，Insert user data one by one
    foreach ($users as $user) {
        $username = $user[0];
        $email = $user[1];

        // Bind parameters to preprocessing statements
        $stmt->bind_param("ss", $username, $email);

        // Execution statement
        if ($stmt->execute()) {
            echo "Successfully inserted user: $username, $email\n";
        } else {
            echo "Insert failed: " . $stmt->error . "\n";
        }
    }

    // 5. Close statement
    $stmt->close();
} else {
    echo "Preprocessing statement preparation failed: " . $conn->error;
}

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

Code description:

1. Establish a database connection : First, we need to connect to the MySQL database. In this example, localhost is used as the server, and you need to adjust the connection parameters according to the actual situation.

2. Initialization statement : Use $conn->stmt_init() to initialize a statement object ready for execution. Then, set up preprocessed SQL statements through the prepare method. In this example, we are preparing an INSERT INTO statement that inserts the username and email fields in the users table.

3. Bind parameters : We bind PHP variables to placeholders in preprocessing statements through the bind_param method. The "ss" parameter means that we are bound to two string types parameters.

4. Loop execution : In the foreach loop, we pass in an array containing multiple sets of user data. Each loop, execute the execute method to insert a row of data.

5. Close statement and connection : After the operation is completed, we close the stmt and database connection through the close() method.

advantage:

1. Prevent SQL injection : Since we use preprocessing statements and parameter binding, this process can effectively prevent SQL injection attacks.

2. High efficiency : For the same query statement, different parameters are executed by looping, avoiding the repeated creation of new SQL statements and improving efficiency.

Notes:

1. Each time you execute a loop, make sure that the bind_param and execute methods can correctly process different input data.

2. If you need to process large amounts of data, make sure that the connection configuration of the database can handle concurrent requests and avoid performance bottlenecks.

In this way, you can effectively execute SQL statements using stmt_init in PHP and be able to dynamically pass different parameters. Hopefully this example helps you better understand how to execute multiple preprocessing statements in PHP!