17

The classic transactions in a loop code:

$mysqli->query("START TRANSACTION");
foreach ($pdata as $key => $value) {
    $sql    = "INSERT INTO temp (`fund_id`) VALUES (" . $value . ")";
    $result = $mysqli->query($sql);
}
$mysqli->query("COMMIT");

Then we change to prepared statements:

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

Questions:

1) Are these two codes identical? Am I missing something in the second code with prepared statements?

2) Is $mysqli->commit() the same as $mysqli->query("COMMIT")?

3) Do I need to add $mysqli->query("START TRANSACTION"); for the prepared statements block or the transaction will automatically start when we set autocommit(FALSE)?

BenMorel
  • 34,448
  • 50
  • 182
  • 322
user2723490
  • 2,010
  • 4
  • 27
  • 37
  • 5
    You do not need to `START TRANSACTION` after you have turned autocommit off. You may `$mysqli->prepare()` _before_ your loop, then execute() it inside the `foreach` loop. There's no need to `prepare()` it on each loop iteration. – Michael Berkowski Oct 01 '13 at 02:26
  • 6
    You don't need to `bind_param()` each time, either. It binds a reference to the variable, so each time you execute it will use the variable's current value. – Barmar Oct 01 '13 at 02:28

1 Answers1

35

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.

mickmackusa
  • 43,625
  • 12
  • 83
  • 136
Andy
  • 49,085
  • 60
  • 166
  • 233