PHP Prepared Statements and Transactions in a Loop

PHP prepared statements and transactions in a loop

Your loop can be optimized by pulling the prepare and bind_param statements out of the loop.

$value = null;
$mysqli->autocommit(FALSE);
$sql  = "INSERT INTO temp (`fund_id`) VALUES (?)";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('i', $value);
foreach ($pdata as $value) {
    $stmt->execute();
}
$mysqli->commit();

You have turned off autocommit with your autocommit(FALSE) line and therefore don't need to use the START TRANSACTION statement.

MySQLi Prepared Statements and Transactions

Prepared statements and transactions are unrelated techniques and technologies.

You may wish to issue the START TRANSACTION and COMMIT/ROLLBACK commands directly instead of using the dedicated methods. They are functionally equivalent.

For your loop, you'd issue the START TRANSACTION before your prepare, then your COMMIT after the loop exits. You probably should not try to open a transaction after a prepared statement has been started but before it's been executed.

For some reason, they didn't add a "start transaction" command in favor of turning off autocommit. It's one of those weird things about mysqli that makes me always recommend PDO instead. :) Opening a transaction implicitly turns off autocommit for the duration of the transaction.

Prepare multiple statments before executing them in a transaction?

I'll post as answer since comments don't allow enough space.

Yes, you can prepare several prepared statements and then execute them in a loop, there's nothing wrong with that.

The transaction part is wrong. If you want to execute all or no queries, you need to start your transaction outside of the loop (same with commit).
That's where PHP's try/catch comes in handy.

$db = PDO('..connection info...');

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // Set error mode to exceptions

try
{
    $stmt_addcat = $db->prepare('INSERT INTO cats (name,age) VALUES(?,?)');
    $stmt_adddog = $db->prepare('INSERT INTO dogs (name,age) VALUES(?,?)');

    $db->beginTransaction();

    foreach($cat_n_dog as $bunch) { } // Do your foreach binding and executing here 

    $db->commit();
}
catch(PDOException $e)
{
    $db->rollBack();

    echo "Error occurred. Error message: ". $e->getMessage() .". File: ". $e->getFile() .". Line: ". $e->getLine();
}

Mysqli prepared statement: store rows in an array and loop through this array later

This is how you need to do it

 ids=array();
 while($row = $stmt->fetch()){
    ids[]= $row['userID'];
 }

ids is an array and will have all the ids that you need.
But I would say "Dont do it like that", just use a table join to get the username for example may be your case would be like

SELECT   replies.admissionID,users.name,replies.userID,replies.description,
replies.link,replies.postingdate,replies.compensation FROM replies, users 
WHERE 
users.name=replies.userID
and replies.projectID=?

mysqli prepared statements inside or outside if then blocks

Because the number of parameters is different between the two statements, it would probably be more clear to prepare and bind inside the if/then/else blocks.

if ($table == "dhereviewers") {
  $sql = "INSERT into $table (titlesid,peopleid) VALUES (?,?)";
  if ($stmt = $mysqli->prepare($sql)) {
    $stmt->bind_param("ii", $titlesid, $peopleid);
  }
} else {
  $sql = "INSERT into $table (titlesid,peopleid,billing) VALUES (?,?,?)";
  if ($stmt = $mysqli->prepare($sql)) {
    $stmt->bind_param("iii", $titlesid, $peopleid, $billing);
  }
}
$stmt->execute();

@MikeBrant has a good point, that in this example, you may be able to do it more simply. But this may depend on some of the code that you have excluded for this question.

PS: num_rows always reports zero rows until you fetch all the rows. You can do this with fetch() or else use $mysqli->store_result(). See http://www.php.net/manual/en/mysqli-stmt.num-rows.php

Loop through array in bind parameters - prepared statement

You need to pass each variable individually to mysqli_stmt_bind_param, so $parameters needs to be an array, not a string. Change the following lines of code:

$parameters = "";

to:

$parameters = array();

and

$parameters .= "'".$value['value']."'" ;

to:

$parameters[] = $value['value'];

(note there is no need to escape values when you are using prepared statements)

remove this line:

$parameters .= ", ";

and finally, change

mysqli_stmt_bind_param($stmt, $notation, $parameters);

to:

mysqli_stmt_bind_param($stmt, $notation, ...$parameters);

and it should work fine.

---

Related Topics