How to Insert Multiple Rows into MySQL Tables with PHP: A Better Approach?

Multiple MySQL INSERT Statements in a Single Query Using PHP

In PHP, it is possible to execute multiple INSERT statements in a single query using the following syntax:

<code class="php">$string1 = "INSERT INTO table1 (column1, column2) VALUES (value1, value2);\n";
$string1 .= "INSERT INTO table2 (column1, column2) VALUES (value3, value4);\n";
$string1 .= "INSERT INTO table3 (column1, column2) VALUES (value5, value6);";
mysql_query($string1) or die(mysql_error());</code>

However, this approach is generally not recommended for several reasons:

• Data Integrity: Inserting multiple rows with a single query can increase the risk of data integrity issues, especially if one of the statements fails.
• Transaction Support: MySQL does not support transactions for multiple INSERT statements in a single query. If any of the statements fail, the entire query will fail.
• Efficiency: It is generally more efficient to execute multiple INSERT statements separately, as each statement will be optimized independently by the database.

Optimized Approach

A better approach to inserting multiple rows is to use multiple INSERT statements with the following syntax:

<code class="php">$sql = "INSERT INTO table1 (column1, column2) VALUES ";
for ($i = 0; $i < count($values); $i++) {
    $sql .= "($values[$i][0], $values[$i][1])";
    if ($i < count($values) - 1) {
        $sql .= ", ";
    }
}
mysql_query($sql) or die(mysql_error());</code>

By using this approach, each row is inserted separately, providing better data integrity and transaction support. Additionally, the database can optimize each statement individually, potentially improving efficiency.

The above is the detailed content of How to Insert Multiple Rows into MySQL Tables with PHP: A Better Approach?. For more information, please follow other related articles on the PHP Chinese website!