Insert multiple rows via a PHP array into MYSQL

Sometime you want to pass a large dataset into a mysql table using PHP.

Assembling one INSERT statement with multiple rows is much faster in MySQL than one INSERT statement per row. This sounds like you might be running into string-handling problems in PHP, if fact it is an algorithm problem. Basically, when working with large strings, you want to minimize unnecessary copying. In order to insert hundreds of rows at once, the fastest and most memory efficient way to build a large string is to take advantage of the implode() function and array assignment.

Example of an array with products $product:

Array
(
    [0] => Array
        (
            [ID] => 100
            [NAME] => Apple
            [PRICE] => 0.50
        )

    [1] => Array
        (
            [ID] => 101
            [NAME] => Orange
            [PRICE] => 1.00
        )

    [2] => Array
        (
            [ID] => 102
            [NAME] => Pear
            [PRICE] => 0.60
        )
)

Now the function to insert into MYSQL:

 $sql = array(); 
    foreach( $product as $row ) {
        $sql[] = '('.$row['ID'].', "'.mysql_real_escape_string($row['NAME']).'",
 "'.$row['PRICE'].'")';
    }
    mysql_query('INSERT INTO table (ID, NAME,PRICE) VALUES '.implode(',', $sql));

The advantage of this approach is that you don’t copy and re-copy the SQL statement you’ve so far assembled with each concatenation; instead, PHP does this once in the implode() statement.