Can you use mysqli prepared statements and transactions together?

Question

All I want to know is if you can use mysqli's prepare, execute, and rollback together?

$m = new mysqli($dbhost,$dbuser,$dbpassword,$dbname);

$m->autocommit(FALSE);
$stmt = $m->prepare("INSERT `table` (`name`,`gender`,`age`) VALUES (?,?,?)");
$stmt->bind_param("ssi", $name, $gender, $age);
$query_ok = $stmt->execute();

$stmt = $m->prepare("INSERT `table` (`name`,`gender`,`age`) VALUES (?,?,?)");
$stmt->bind_param("ssi", $name, $gender, $age);
if ($query_ok) {$query_ok = $stmt->execute();}

if (!$query_ok) {$m->rollback();} else {$m->commit();}

Can you do this? Let's assume that the above code has a loop and or the variables get new data in them.

I have tried it and it's unclear on the results, which is why I am asking. The PHP documentation doesn't say anything either way if prepare, execute, and rollback work together. Any one ever try and or get it to work? — Joseph Pahl, Oct 09 '12 at 20:08
I too am trying to figure this out. It is very hard to find an example with this combination. — Adam, Jan 12 '13 at 23:14
It does work. I had made a mistake in my selection of the mysql engine. You need to make sure that the innodb engine is selected for what ever table you wish to use rollback on. — Joseph Pahl, Mar 04 '13 at 17:26
Refer this answer on stackoverflow, you will get quick solution. [link](https://stackoverflow.com/questions/45270154/how-to-use-prepare-statement-to-insert-data-to-three-different-tables-in-mysqli) — rohit, Oct 11 '20 at 15:02

Fleshgrinder · Answer 1 · 2021-04-17T16:20:19.127

Best way to handle this is with exceptions (as always, darn PHP error/warning stuff). Simply because our commit() call may fail too. Note that finally is only available in newer PHP versions.

<?php

// Transform all errors to exceptions!
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
  $connection = new \mysqli($dbhost, $dbuser, $dbpassword, $dbname);
  $connection->autocommit(false);

  $stmt = $connection->prepare("INSERT `table` (`name`, `gender`, `age`) VALUES (?, ?, ?)");
  $stmt->bind_param("ssi", $name, $gender, $age);
  $stmt->execute();

  // We can simply reuse the prepared statement if it's the same query.
  //$stmt = $connection->prepare("INSERT `table` (`name`, `gender`, `age`) VALUES (?, ?, ?)");

  // We can even reuse the bound parameters.
  //$stmt->bind_param("ssi", $name, $gender, $age);

  // Yet it would be better to write it like this:
  /*
  $stmt = $connection->prepare("INSERT `table` (`name`, `gender`, `age`) VALUES (?, ?, ?), (?, ?, ?)");
  $stmt->bind_param("ssissi", $name, $gender, $age, $name, $gender, $age);
   */

  $stmt->execute();
  $connection->commit();
}
catch (\mysqli_sql_exception $exception) {
  $connection->rollback();
  throw $exception;
}
finally {
  isset($stmt) && $stmt->close();
  $connection->autocommit(true);
}

Can you use mysqli prepared statements and transactions together?

1 Answers1