How can PHP be used to generate SQL queries for inserting multiple rows of data into a database table?

When inserting multiple rows of data into a database table using PHP, it is efficient to generate a single SQL query that includes all the rows to be inserted. This can be achieved by constructing the SQL query dynamically by looping through the data and formatting it appropriately. The SQL query should be executed only once to insert all the rows at once, reducing the number of database interactions and improving performance. 

// Sample data to be inserted
$data = [
    ['John Doe', 'john@example.com'],
    ['Jane Smith', 'jane@example.com'],
    ['Bob Johnson', 'bob@example.com']
];

// Construct the SQL query dynamically
$query = "INSERT INTO users (name, email) VALUES ";
foreach ($data as $row) {
    $query .= "('" . $row[0] . "', '" . $row[1] . "'),";
}
$query = rtrim($query, ','); // Remove the last comma

// Execute the SQL query to insert multiple rows at once
$result = mysqli_query($connection, $query);

if ($result) {
    echo "Multiple rows inserted successfully.";
} else {
    echo "Error inserting multiple rows: " . mysqli_error($connection);
}

Keywords

PHP SQL queries inserting multiple rows database table

Related Questions