Clever Geek Handbook

Inserting an array of values

I have an array with a variable number of values.

Is there a more efficient or better way to INSERT these into my DB other than a loop with a query inside it?

+2


source to share


3 answers


On this site , there is a good MySQL example with a multiuser query. It is valid SQL for

INSERT INTO [table]
VALUES 
(row1),
(row2),
...


As requested: php snippet:

$query="INSERT INTO mytable\nVALUES\n (".$values[0].")";
array_shift( $values );
foreach( $values as $value ) {
    $query .= ",(".$value.")";
}
+3


source


In my experience, multi-line inserts are handled by MUCH faster than the equivalent number of single row inserts if you are inserting a large amount of data at a time, which is a good way to go. I have watched the process of entering thousands of rows of data condense from 5-10 minutes to literally seconds using this method.

As for the piece of code, I was a fan of using implode () to concatenate arrays of fields and values ​​together. There is no reason why you cannot do the same for data strings, you just need to determine which fields to specify, escaped, etc.

For the argument, let's say $ rows is an array of properly formatted SQL values ​​...



$sql = "INSERT INTO `table` VALUES (" . implode("), (", $rows) . ")";

You can apply something similar to collecting individual fields if you like.

+2


source


If the database you are using allows multiple insertion of values, then you can create a multiple insert operator and send it to the database - one connect to one command to perform multiple inserts.

If you cannot do multiple inserts - (as MSSQL does not allow) then I think you are stuck.

+1


source






More articles:


All Articles