PHP mysqli autocommit() Method

The PHP mysqli::autocommit() / mysqli_autocommit() function is used to turn on or off auto-commit mode on queries for the database connection. To determine the current state of autocommit use the SQL command SELECT @@autocommit.

Syntax

//Object-oriented style
public mysqli::autocommit(enable)

//Procedural style
mysqli_autocommit(mysql, enable)

Parameters

`mysql`	`Required.` For procedural style only: Specify a mysqli object returned by mysqli_connect() or mysqli_init().
`enable`	`Required.` Specify whether to turn on auto-commit or not.

Return Value

Returns true on success or false on failure.

Example: Object-oriented style

The example below shows the usage of mysqli::autocommit() method.

<?php
//establishing connection to the database
$mysqli = new mysqli("localhost", "user", "password", "database");
if ($mysqli->connect_errno) {
  echo "Failed to connect to MySQL: ". $mysqli->connect_error;
  exit();
}

//the table engine has to support transactions
$mysqli->query("CREATE TABLE IF NOT EXISTS Employee (
  Name VARCHAR(255) NOT NULL,
  Salary DECIMAL(18,2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

//turn autocommit off
$mysqli->autocommit(false);

$result = $mysqli->query("SELECT @@autocommit");
$row = $result->fetch_row();
printf("Autocommit is %s\n", $row[0]);

try {
  //preparing the insert statement
  $stmt = $mysqli->prepare('INSERT INTO Employee(Name, Salary) VALUES (?,?)');
  $stmt->bind_param('sd', $name, $salary);

  //inserting some values
  $name = 'John';
  $salary = 3000;
  $stmt->execute();
  $name = 'Marry';
  $salary = 2750;
  $stmt->execute();

  //committing the data in the database. This doesn't set autocommit=true
  $mysqli->commit();
  print "Committed 2 rows in the database\n";

  $result = $mysqli->query("SELECT @@autocommit");
  $row = $result->fetch_row();
  printf("Autocommit is %s\n", $row[0]);

  //trying to insert more values
  $name = 'Kim';
  $salary = 3100;
  $stmt->execute();
  $name = 'Ramesh';
  $salary = 3000;
  $stmt->execute();

  //setting autocommit=true will trigger a commit
  $mysqli->autocommit(true);

  print "Committed 2 row in the database\n";

} catch (mysqli_sql_exception $exception) {
  $mysqli->rollback();

  throw $exception;
}
?>

The output of the above code will be:

Autocommit is 0
Committed 2 rows in the database
Autocommit is 0
Committed 2 row in the database

Example: Procedural style

The example below shows the usage of mysqli_autocommit() function.

<?php
//establishing connection to the database
$mysqli = mysqli_connect("localhost", "user", "password", "database");
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: ". mysqli_connect_error();
  exit();
}

//the table engine has to support transactions
mysqli_query($mysqli, "CREATE TABLE IF NOT EXISTS Employee (
  Name VARCHAR(255) NOT NULL,
  Salary DECIMAL(18,2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

//turn autocommit off
mysqli_autocommit($mysqli, false);

$result = mysqli_query($mysqli, "SELECT @@autocommit");
$row = mysqli_fetch_row($result);
printf("Autocommit is %s\n", $row[0]);

try {
  //preparing the insert statement
  $stmt = mysqli_prepare($mysqli, 'INSERT INTO Employee(Name, Salary) VALUES (?,?)');
  mysqli_stmt_bind_param($stmt, 'sd', $name, $salary);

  //inserting some values
  $name = 'John';
  $salary = 3000;
  mysqli_stmt_execute($stmt);
  $name = 'Marry';
  $salary = 2750;
  mysqli_stmt_execute($stmt);

  //committing the data in the database. This doesn't set autocommit=true
  mysqli_commit($mysqli);
  print "Committed 2 rows in the database\n";

  $result = mysqli_query($mysqli, "SELECT @@autocommit");
  $row = mysqli_fetch_row($result);
  printf("Autocommit is %s\n", $row[0]);

  //trying to insert more values
  $name = 'Kim';
  $salary = 3100;
  mysqli_stmt_execute($stmt);
  $name = 'Ramesh';
  $salary = 3000;
  mysqli_stmt_execute($stmt);

  //setting autocommit=true will trigger a commit
  mysqli_autocommit($mysqli, true);

  print "Committed 2 row in the database\n";
} catch (mysqli_sql_exception $exception) {
  mysqli_rollback($mysqli);

  throw $exception;
}
?>

The output of the above code will be:

Autocommit is 0
Committed 2 rows in the database
Autocommit is 0
Committed 2 row in the database

❮ PHP MySQLi Reference