How to start and end transaction in mysqli?

    |
  • Added:
  • |
  • In: Basic PHP

As far as I understood transaction starts once we call $mysqli->autocommit(FALSE); statement and ends after calling $mysqli->commit(); command like in the example below.

<?php
//Start transaction 
$mysqli->autocommit(FALSE);
$mysqli->query('UPDATE `table` SET `col`=2');
$mysqli->query('UPDATE `table1` SET `col1`=3;');
$mysqli->commit();
//End transaction

//Executing other queries without transaction control
$mysqli->query("Select * from table1");
$mysqli->query("Update table1 set col1=2");
//End of executing other queries without transaction control

//Start transaction 
$mysqli->autocommit(FALSE);
$mysqli->query('UPDATE `table` SET `col`=2');
$mysqli->query('UPDATE `table1` SET `col1`=3;');
$mysqli->commit();
//End transaction
?>

Have I understood correctly? If not could you please correct me, because it is actually my first time using transactions in the real life.

Thank you.

This Question Has 4 Answeres | Orginal Question | Bakhtiyor

You think that the command "commit" automatically switch autocommit back to true? Comment in php doc says NO!

Well according to the php doc, you're right.

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$mysqli->query("CREATE TABLE Language LIKE CountryLanguage");

/* set autocommit to off */
$mysqli->autocommit(FALSE);

/* Insert some values */
$mysqli->query("INSERT INTO Language VALUES ('DEU', 'Bavarian', 'F', 11.2)");
$mysqli->query("INSERT INTO Language VALUES ('DEU', 'Swabian', 'F', 9.4)");

/* commit transaction */
$mysqli->commit();

/* drop table */
$mysqli->query("DROP TABLE Language");

/* close connection */
$mysqli->close();
?>

In the example above:

  • the CREATE TABLE is auto committed because it's the default behavior.
  • the INSERT INTO aren't auto committed because of the autocommit(FALSE)
  • the DROP TABLE is auto committed because the autocommit(FALSE) was resetted by the ->commit();.

Prepare SQL statement ONCE, and then execute it MORE TIMES:

<?php
$Mysqli = new mysqli("host","user","pass","base");

// check connection
if (mysqli_connect_errno())
{
  printf("Connect failed: %s\n", mysqli_connect_error());
  exit();
}

// some data for db insertion
$langs=['Bavarian','Schwabian'];

// explicitly begin db transaction
$Mysqli->begin_transaction();

// prepare statement for multiple inserts once
$stmt=$Mysqli->prepare("INSERT INTO table(column) VALUES(?)");

// bind prepared statement with variable $lang
$stmt->bind_param('s',$lang);

// loop for inserting $langs array values
foreach($langs as $lang)
{
  //execute prep stat more times with new values
  if(!$stmt->execute())
  {
    // rollback if prep stat execution fails
    $Mysqli->rollback();
    // exit or throw an exception
    exit();
  }
}

// close prepared statement
$stmt->close();

// commit transaction
$Mysqli->commit();

// close connection
$Mysqli->close();

?>

j0k is mainly right, except in the drop table.

The auto commit is not turned on with the ->commit()

Instead, the DROP TABLE is a DDL query, and DDL queries are always implicitly commited and will commit all your previously non commited work.

So, if you did not commited the work, the DDL query would force this commit.


Search
Meet with owner

Sajjad Hossain

Hey, I am Sajjad, working in web development sector since 2012. I love to do amazing things. Let's do a project together.
Connect Social With PHPAns
Top