Recommend approach to insert multiple MySQL records with PHP

[NotionCommotion]

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);  
?>

[Barand]

For a large number of inserts I would definitely choose the latter method, it's faster. For a few, as you have there, I would probably go for the convenience of a prepared query.

[NotionCommotion]

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

Edited June 4, 2015 by NotionCommotion

[Psycho]

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.

[NotionCommotion]

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

 

[Barand]

I have just noticed you are attempting a prepared statement in the second example - how do you propose binding the parameters?

[NotionCommotion]

I have just noticed you are attempting a prepared statement in the second example - how do you propose binding the parameters?

Just as I showed.  Why do you ask?  Thank you

[Barand]

I was thinking of the more general case

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

[NotionCommotion]

 

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)); 

[Barand]

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