Insert 500 rows to MySQL from PHP array

Question

codes table

id     code
--    ------
1     BB3CA4
2     2788BA
3     E3E3A5
.        .
.        .
.        .

PHP array of 500 semi-unique strings called $codes to be added to the codes table:

$codes = array( 'AC2BE4', 'BC61A2', '34CE5A', ... );

My PDO database connection object:

$dbh = new PDO( "mysql:host=$host;dbname=$db", $user, $pass );

Try 1

Put PDO insert execute within the PHP loop:

foreach( $codes as $code ){
    $stmt = $dbh->prepare("INSERT INTO codes ( code ) VALUES ( :code )");
    $stmt->bindParam( ':code', $code , PDO::PARAM_STR );
    $stmt->execute();
}

I think this is not a good solution!

Try 2

Second try was to construct a long SQL query by gluing 500 SQL queries and run it at once.

If this is necessary only one time, you can use first code.

Richard
– Richard

2015-05-23 15:39:27 +00:00
Commented May 23, 2015 at 15:39 — Richard
– Richard, Commented May 23, 2015 at 15:39

Jite · Accepted Answer · 2015-05-23 15:46:24Z

3

Prepared statements that do the same query but with different parameters don't have to be created multiple times, just once, so create the statement before the loop, then iterate, you bind the parameters and execute the query inside the loop.

$stmt = $dbh->prepare("INSERT INTO codes ( code ) VALUES ( :code )");
foreach( $codes as $code ){
  $stmt->bindParam( ':code', $code , PDO::PARAM_STR );
  $stmt->execute();
}

With PDO you could even slim the code down a little bit by setting the parameter/s in the execute call:

$stmt = $dbh->prepare('INSERT INTO codes ( code ) VALUES ( :code )');
foreach($codes as $code) {
  $stmt->execute(array('code' => $code));
}

edited May 23, 2015 at 15:46

answered May 23, 2015 at 15:41

Jite

5,8772 gold badges28 silver badges40 bronze badges

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

2 Comments

Irvin Lim Over a year ago

are multiple executions more efficient than say, imploding multiple groups of brackets of values together, and running them all as a single query?

Jite Over a year ago

I'm not sure if its faster, but its safer, cause you get the extra protection from injections that prepared statements gives. And when it comes to 500 inserts, it won't really matter, its fast either way and easier to write with like above in my opinion.

Partha Sen · Accepted Answer · 2015-05-23 16:15:34Z

-1

 $sql = "INSERT INTO codes ( code ) VALUES ";
 foreach($codes as $code){        
    $sql = $sql . "($code),"
 }
 $sql = rtrim($sql,",");

I think this is a simple way to insert 500 values at a time into database.

answered May 23, 2015 at 16:15

Partha Sen

14 bronze badges

Collectives™ on Stack Overflow

Insert 500 rows to MySQL from PHP array

Try 1

Try 2

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Try 1

Try 2

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related