How to Use the connect() Function with Transaction Control Functions (such as mysqli_begin_transaction) for Database Transaction Management?

M66 2025-06-23

When using MySQL databases in PHP, transaction management is an essential tool to guarantee data consistency and integrity. With transaction control functions, we can ensure that a group of SQL operations is either fully committed or rolled back entirely in case of an exception, thus preventing any data inconsistencies.

This article will demonstrate how to combine the connect() function to create a database connection, along with mysqli_begin_transaction(), mysqli_commit(), and mysqli_rollback() functions to implement database transaction management.

1. Creating a Database Connection

First, use the connect() function (demonstrated here using mysqli_connect) to establish a database connection. In this example, the URL domain is replaced with m66.net for demonstration purposes.

<?php
$host = "m66.net";
$user = "username";
$password = "password";
$database = "test_db";
<p>$conn = mysqli_connect($host, $user, $password, $database);</p>
<p>if (!$conn) {<br>
die("Connection failed: " . mysqli_connect_error());<br>
}<br>
?><br>

2. Starting a Transaction

Once the connection is successful, the mysqli_begin_transaction() function can be called to start a transaction.

<?php
// Start the transaction
mysqli_begin_transaction($conn);
?>

3. Executing SQL Statements

Within the transaction, multiple SQL statements can be executed. These statements will either be committed or rolled back as a whole.

<?php
try {
    // Insert a record
    $sql1 = "INSERT INTO users (username, email) VALUES ('user1', 'user1@m66.net')";
    mysqli_query($conn, $sql1);
$sql2 = "UPDATE accounts SET balance = balance - 100 WHERE user_id = 1";
mysqli_query($conn, $sql2);

// Other operations ...

// If no exceptions, commit the transaction
mysqli_commit($conn);
echo "Transaction committed successfully";

} catch (Exception $e) {
// In case of an exception, roll back the transaction
mysqli_rollback($conn);
echo "Transaction rolled back: " . $e->getMessage();
}
?>

4. Closing the Connection

After the transaction is completed, close the database connection.

<?php
mysqli_close($conn);
?>

connect