Jump to content

Recommend approach to insert multiple MySQL records with PHP


Recommended Posts

Normally, I use PDO and make a separate query for each insert.  Started thinking maybe it would be best to insert all records with one query.  Any advise?  Thank you

<?php
$insert=array(4,3,6,9,6,3,6);
$stmt = $conn->prepare('INSERT INTO myTable(a,b,c,d,e,f) VALUES (?,"foo","bar",NOW(),FALSE,TRUE)');
foreach($insert as $id) {
    $stmt->execute(array($id));    
}
?>  

<?php
$insert=array(4,3,6,9,6,3,6);
$sql = 'INSERT INTO myTable(a,b,c,d,e,f) VALUES ';
foreach($insert as $dummy) {
    $sql.='(?,"foo","bar",NOW(),FALSE,TRUE),';
}
$sql=rtrim($sql,',');
$stmt = $conn->prepare($sql);
$stmt->execute($insert);  
?>

Thanks Barand,  When using the later approach, know how big the query could get before MySQL chokes?

 

Impossible to state. There are many variable to consider other than just the number of records being added: CPU, Memory, how big the existing database is, are there any triggers, complexity of the table, the type of data for those records, shared server, etc., etc.

 

The only way to know is to run some tests using the same or similar hardware and configuration as you would expect to see in your production environment. If this is a shared server, you are really at a disadvantage as your results would be based on what is happening with the other sites using that same database server.

So, based on Barand's and Psycho's advice, it is only really advantages from a performance perspective to do so for a large number of inserts, however, I should only do so if I have a small number of inserts if I am on a shared server :(

 

 

I was thinking of the more general case

$sql.='(?, ?, ?, NOW(), ?, ?),';

 

 

Please elaborate.  How would this work?  My approach would just do the following:

$sql='INSERT INTO myTable
   (a,b,c,d,e,f)
VALUES
   (?,"foo","bar",NOW(),FALSE,TRUE),
   (?,"foo","bar",NOW(),FALSE,TRUE),
   (?,"foo","bar",NOW(),FALSE,TRUE),
   (?,"foo","bar",NOW(),FALSE,TRUE),
   (?,"foo","bar",NOW(),FALSE,TRUE),
   (?,"foo","bar",NOW(),FALSE,TRUE)';

$stmt = $conn->prepare($sql);

$stmt->execute(array(4,3,6,9,6,3,6)); 

I am just curious (having never used multiple inserts in conjunction with a  prepared statement) if that method would hold up when inserting a csv file with many columns and a few thousand rows

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.