CODEWITHSUNDEEP
phpmysql
ian
ian

Reputation: 12335

inserting a array of values

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?

Upvotes: 2

Views: 205

Answers (3)

Crazy Joe Malloy
Crazy Joe Malloy

Reputation: 834

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.

Upvotes: 2

xtofl
xtofl

Reputation: 41519

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.")";
}

Upvotes: 3

Robert DeBoer
Robert DeBoer

Reputation: 1695

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.

Upvotes: 1

Related Questions