How Can I Efficiently and Securely Insert Multiple Rows into MySQLi?

Efficient and Secure Insertion of Multiple Rows in MySQLi

When working with large datasets, it is crucial to insert data into your MySQL database efficiently while maintaining security. This can be achieved by utilizing appropriate techniques that prevent SQL injections and optimize performance.

One effective approach for inserting multiple rows is to employ prepared statements within a transaction. This method ensures both data integrity and speed. Consider the following code snippet:

$array = ["array", "with", "about", "2000", "values"];
$query = "INSERT INTO table (link) VALUES (?)";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("s", $one);

$mysqli->query("START TRANSACTION");
foreach ($array as $one) {
    $stmt->execute();
}
$stmt->close();
$mysqli->query("COMMIT");

By utilizing a transaction, your database operations are grouped as a single unit. This means that all insertions will either succeed or fail together, ensuring data consistency. Additionally, the prepared statement prevents SQL injection vulnerabilities by ensuring that all inputs are properly sanitized.

Another advantage of this approach is the significant performance boost it provides. By avoiding multiple round trips to the database server, the transaction approach dramatically reduces the time required for insertion compared to using individual prepared statements for each row.

To illustrate the efficiency of this method, a test was conducted by executing 10,000 insertions using PHP and MySQLi. The results demonstrated a remarkable speed increase when transactions were used:

• Without transactions: 226 seconds
• With transactions: 2 seconds

The use of transactions and prepared statements in combination not only ensures security against data manipulation attacks but also optimizes performance when inserting large quantities of data into your MySQL database.

The above is the detailed content of How Can I Efficiently and Securely Insert Multiple Rows into MySQLi?. For more information, please follow other related articles on the PHP Chinese website!