What are common pitfalls when using MySQL queries (INSERT) within a FOR loop in PHP?

Common pitfalls when using MySQL queries (INSERT) within a FOR loop in PHP include unnecessary overhead from opening and closing connections multiple times, potential performance issues due to executing multiple queries sequentially, and increased risk of SQL injection if input data is not properly sanitized. To solve this issue, you can batch the INSERT queries and execute them in a single transaction to reduce overhead and improve performance. Additionally, make sure to properly sanitize input data to prevent SQL injection attacks. 

<?php

// Sample code to demonstrate batching INSERT queries within a FOR loop in PHP

// Establish a connection to the database
$connection = new mysqli("localhost", "username", "password", "database");

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

// Initialize an array to store values for batch INSERT
$values = array();

for ($i = 0; $i < 10; $i++) {
    // Generate sample data for INSERT
    $name = "User " . $i;
    $email = "user" . $i . "@example.com";
    
    // Add values to the batch array
    $values[] = "('$name', '$email')";
}

// Batch INSERT query
$query = "INSERT INTO users (name, email) VALUES " . implode(", ", $values);

// Execute the batch INSERT query within a transaction
$connection->begin_transaction();
$connection->query($query);
$connection->commit();

// Close the database connection
$connection->close();

?>

Keywords

MySQL queries INSERT FOR loop PHP performance

Related Questions