10

Is there a way to do transactions with prepared statements?

I mean can I use the following example with $mysqli->autocommit(FALSE); and $mysqli->commit( ); and $mysqli->rollback( );

//Preparing the statment

$insert_stmt=$mysqli->prepare("INSERT INTO x VALUES(?,?)")
or die($mysqli->error);

//associate variables with the input parameters

$insert_stmt->bind_param("is", $my_number,$my_string); //i=integer

//Execute the statement multiple times....

for ($my_number = 1; $my_number <= 10; $my_number++) 
{
    $my_string="row ".$my_number;
    $insert_stmt->execute() or die ($insert_stmt->error);
}
$insert_stmt->close();

I have looked around but can't find any example of using prepared statements (not PDO): http://php.net/manual/en/mysqli.prepare.php with transactions, I only find examples like these mentioned here: http://book.opensourceproject.org.cn/lamp/mysql/mysqlstored/opensource/0596100892/mysqlspp-chp-13-sect-2.html where transactions and prepared statements are never inter-mixed.

Would it be wrong to use them together?

Yerel
  • 103
  • 1
  • 5

1 Answers1

8

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.

Charles
  • 50,943
  • 13
  • 104
  • 142
  • Thanks alot :) shouldn't I check for an error after the loop, and rollback on error (and commit if there's no error) – Yerel Mar 21 '11 at 00:25
  • That's one of the other big advantages of PDO over mysqli -- you can set it to throw exceptions and thus don't have to check error codes over and over and over, meaning the decision to rollback or commit can be greatly simplified. – Charles Mar 21 '11 at 00:30
  • 3
    OMG, this helps me so much. I made a script that `INSERT`ed ~5000 rows, it was taking hours to finish. Adding `$mysqli->autocommit(FALSE);` and `$mysqli->commit();` made it run within minutes! :-) – gen_Eric Apr 12 '12 at 18:31
  • @Charles: when you say ___they did't add a "start transaction"___, are you speaking of [begin_transaction](http://www.php.net/manual/en/mysqli.begin-transaction.php)? – Justin C Sep 22 '13 at 04:22
  • @JustinC, that was added in 5.5, which is just a few months old now. Take a peek at the date of the question and answer. :) – Charles Sep 22 '13 at 04:27