PHP: An efficient way to insert a list to database

Question

Merely due to the sake of being efficient I would like to hear some advises from the PHP expers here. (Actually this question goes out all developers that mess with database connections on a regular basis)

Assume you have one personId and this id has multiple numbers. You have a table with columns pID,nums

From the php side you retrieve these numbers in an array. Now my question comes to place. Do you do something like;

for($i=0;$i<count($arr);$i++)
{
   //Call the insert query over and over again
}

Or there is obviously a better solution?

web_bod · Accepted Answer · 2012-05-19 00:16:28Z

2

Essentially you want to do a bulk insert - it's faster to execute one composite statement, than lots of individual inserts, so try:

$data is your array
$sql = array(); 
foreach( $data as $row ) {
    $sql[] = '("'.mysql_real_escape_string($row['field1']).'")';
}
mysql_query('INSERT INTO table (field) VALUES '.implode(',', $sql));

edited May 19, 2012 at 0:16

answered May 19, 2012 at 0:09

web_bod

5,7681 gold badge19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wroniasty Over a year ago

I assumed the tag implied it's MySQL :)

wroniasty · Accepted Answer · 2012-05-19 00:09:03Z

1

If your data set is really large, you can look at dumping your values into a file, and then executing "LOAD DATA INFILE" (will only work if MySQL runs on the same host as your script).

Alternatively, you can use one insert query:

INSERT INTO table(pid,nums) VALUES (1,2), (1,3), (1,4), ... -- etc

with multiple values.

answered May 19, 2012 at 0:09

wroniasty

8,0722 gold badges34 silver badges24 bronze badges

Collectives™ on Stack Overflow

PHP: An efficient way to insert a list to database

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related