What exactly is the fact : when placed before execution, PDO prepared statements make multiple sql calls or not

Question

I asked a question about the best way, performace-wise, to make multiple inserts( hundreds or even thousands) in prepared statements. See here for it How to perform multiple MySQL inserts in PHP.

In the course of answering the question, this old issue came up again : Do multiple SQL calls really take place in the following or not? :

$stmt = $dbLink->prepare( "INSERT INTO table SET id = :ID, 
                                             name = :name, 
                                             email = :email, 
                                             mobile = :mobile");

$stmt->bindParam( ':ID', $person->getID(), PDO::PARAM_STR );
$stmt->bindParam( ':name', $person->getName(), PDO::PARAM_STR );
$stmt->bindParam( ':email', $person->getEmail(), PDO::PARAM_STR );
$stmt->bindParam( ':mobile', $person->getMobile(), PDO::PARAM_STR );

foreach( $persons as $person ){

   $stmt->execute(); 
}

Please, I think something like this cannot be right and wrong at the same time. There has to be a fact about it; and I just don't know that fact.

Answer 1

First, you should re-order your code a bit:

• the $person is not defined outside the loop (or is not what you think), so you will get warnings, and the bindParam will set empty values
• you will be inserting the same (empty) values in your for loop, since you never update the query parameters.

Do it like this, as was explained in this answer:

$stmt = $dbLink->prepare( "INSERT INTO table SET id = :ID, 
                                             name = :name, 
                                             email = :email, 
                                             mobile = :mobile");

foreach ( $persons as $person ) {

    $stmt->bindParam( ':ID', $person->getID(), PDO::PARAM_STR );
    $stmt->bindParam( ':name', $person->getName(), PDO::PARAM_STR );
    $stmt->bindParam( ':email', $person->getEmail(), PDO::PARAM_STR );
    $stmt->bindParam( ':mobile', $person->getMobile(), PDO::PARAM_STR );

    $stmt->execute(); 
}

Second, every time you run $stmt->execute() you are executing a query. Therefore, you will run count( $persons ) queries. The prepared statement is a little bit faster, because the SQL query string only has to be parsed once.

This will run pretty fast, even for thousands of records. However, you may get a delay every time the database writes to disk; this is especially noticable with software RAID and slow disks. You can eliminate all but one of those delays by using transactions:

$dbLink->beginTransaction();

$stmt = $dbLink->prepare( ..... );
foreach ( $persons as $person ) {
  ...bindParam();
  ...execute();
}

$dbLink->commit();