What are some alternative approaches to inserting arrays into a MySQL database in PHP for better readability and performance?

When inserting arrays into a MySQL database in PHP, a common approach is to loop through the array and execute individual insert queries for each element. However, this can be inefficient and lead to poor performance, especially with large arrays. One alternative approach is to use prepared statements with parameter binding to insert the entire array in a single query. This not only improves readability but also enhances performance by reducing the number of queries sent to the database. 

// Sample array to be inserted into the database
$data = [
    ['name' => 'John Doe', 'age' => 30],
    ['name' => 'Jane Smith', 'age' => 25],
    ['name' => 'Alice Brown', 'age' => 35]
];

// Prepare the insert query
$query = "INSERT INTO users (name, age) VALUES ";
$placeholders = [];
$values = [];

// Build the placeholders and values arrays
foreach ($data as $row) {
    $placeholders[] = "(?, ?)";
    $values = array_merge($values, array_values($row));
}

$query .= implode(", ", $placeholders);

// Prepare and execute the statement
$stmt = $pdo->prepare($query);
$stmt->execute($values);

Keywords

MySQL database PHP arrays performance readability

Related Questions