How Can I Efficiently and Securely Insert Multiple Values into MySQL Using PHP?

Inserting Multiple Values Efficiently and Securely with PHP and MySQLi

To insert numerous values securely, avoiding SQL injection and stack overflows, consider the following techniques.

Approach 1: Enhanced Loop with Prepared Statement

Maintain your current approach but make some modifications for efficiency:

$stmt = $mysqli->prepare("INSERT INTO table (link) VALUES (?)");
$stmt->bind_param("s", $one);

foreach ($array as $one) {
    $stmt->execute();
}

This approach initializes the prepared statement outside the loop, improving performance.

Approach 2: Transaction-Based Approach

To further optimize, utilize transactions:

$stmt = $mysqli->prepare("INSERT INTO table (link) VALUES (?)");
$stmt->bind_param("s", $one);

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

By wrapping the insertions in a transaction, MySQL's buffering system is utilized, resulting in significant speed improvements.

Testing and Results

In a performance test with 10,000 iterations, the transaction-based approach was found to be substantially faster:

• Without transaction: 226 seconds
• With transaction: 2 seconds

Therefore, using the transaction-based approach is recommended for large-scale insertion operations.

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