inserting a array of values

Question

I have a array with a variable amount of values.

Is there a more efficient or better way to INSERT them into my DB besides a loop with a query inside it?

Answer 1

If the DB you are using allows multiple value insert, then you could create an multi-insert statement and send that to the DB - one connect with one command to do multiple inserts.

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

Answer 2

At this site, there is a nice example of MySQL with a multi-insert query. It is valid SQL to

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

On request: a php snippet:

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

Answer 3

In my experience multi-row inserts are processed MUCH faster than an equivalent number of single row inserts, if you're inserting a large amount of data at a time, that's a good way to go. I've watched a process of entering thousands of rows of data be condensed from 5-10 minutes down to literally seconds using this method.

As far as the code part, I've been a fan of using implode() to join arrays of fields & values together. No reason you can't do the same for rows of data, you just need to be able to identify which fields need to be quoted, escaped, etc.

For the sake of argument assume $rows is an array of properly formatted SQL values...

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

You could apply something similar to assemble the individual fields should you desire.