autocommit

In this article, we will focus on the mysqli_autocommit() function in PHP, which is used to toggle the autocommit feature in MySQLi transactions. We will provide you with an overview of the function, how it works, and examples of its use.

Introduction to the mysqli_autocommit() function

The mysqli_autocommit() function is a built-in function in PHP that is used to toggle the autocommit feature in MySQLi transactions. When autocommit is enabled, each SQL statement is treated as a separate transaction and is automatically committed. When autocommit is disabled, you need to explicitly commit or rollback transactions using the mysqli_commit() and mysqli_rollback() functions.

How to use the mysqli_autocommit() function

Using the mysqli_autocommit() function is very simple. You just need to call the function and pass in a valid MySQLi connection and a boolean value that represents the autocommit state.

Parameters:

• connection (procedural) / $mysqli (OOP): The MySQLi connection object.
• mode (bool): TRUE to enable autocommit, FALSE to disable it.

Return value: Returns TRUE on success, FALSE on failure.

Note: MySQLi supports both object-oriented and procedural syntax. The examples below use the OOP method $mysqli->autocommit(). The procedural equivalent is mysqli_autocommit($link, $mode).

Here is an example:

How to use the mysqli_autocommit() function

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

$mysqli->autocommit(FALSE);

$mysqli->query("INSERT INTO users (name, email) VALUES ('John', '[email protected]')");
$mysqli->query("UPDATE users SET name='John Doe' WHERE id=1");

$mysqli->commit();

$mysqli->autocommit(TRUE);

$mysqli->close();
?>

In this example, we create a new MySQLi object and disable autocommit by calling the autocommit() function with an argument of FALSE. We then execute two queries to insert and update data in a users table. We commit the transaction by calling the commit() function of the MySQLi object.

We then re-enable autocommit by calling the autocommit() function with an argument of TRUE. Finally, we close the MySQLi connection using the close() method of the MySQLi object.

Advanced usage

The mysqli_autocommit() function operates at the connection level. When you switch autocommit from FALSE to TRUE, MySQLi automatically commits any pending transaction. This behavior is useful when managing multiple independent transactions sequentially within the same script. Here is an example:

Advanced usage of PHP autocommit()

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

// Start and commit the first transaction
$mysqli->autocommit(FALSE);
$mysqli->query("INSERT INTO users (name, email) VALUES ('John', '[email protected]')");
$mysqli->commit();

// Start and commit the second transaction
$mysqli->autocommit(FALSE);
$mysqli->query("UPDATE users SET name='John Doe' WHERE id=1");
$mysqli->commit();

// Re-enable autocommit for subsequent queries
$mysqli->autocommit(TRUE);

$mysqli->close();
?>

In this example, we disable autocommit and execute an INSERT query. We then explicitly commit the first transaction. Next, we disable autocommit again to start a second transaction, execute an UPDATE query, and commit it. Finally, we re-enable autocommit and close the MySQLi connection.

Conclusion

In conclusion, the mysqli_autocommit() function is a powerful tool for enabling or disabling the autocommit feature in MySQLi transactions in PHP. By understanding how to use the function and its advanced usage scenarios, you can take advantage of this feature to create powerful and flexible MySQL transactions in your PHP scripts. Remember to always commit or rollback transactions when autocommit is disabled to ensure data integrity and consistency in your database.

Practice