PHP MySQLi Transactions 🎯

Welcome to this comprehensive guide on PHP MySQLi Transactions! In this tutorial, we'll dive deep into understanding and working with transactions in PHP and MySQLi. By the end of this lesson, you'll be able to create, manage, and commit/rollback transactions in your PHP projects. 📝 Let's get started!

What are Transactions? 💡

Transactions are a set of database operations that are executed together as a single unit of work. They ensure that all database operations within a transaction are either completed successfully (committed) or none of them are (rolled back). This guarantees data consistency and integrity.

Why Use Transactions? 💡

Transactions are essential when dealing with complex database operations that involve multiple tables and multiple queries. They help prevent inconsistencies and errors, ensuring that your database remains clean and reliable.

Starting a Transaction 💡

To start a transaction in PHP MySQLi, we use the begin_transaction() function.

php

// Connect to the database
$conn = new mysqli("localhost", "username", "password", "database");

// Start the transaction
$conn->begin_transaction();

Committing a Transaction 💡

After executing all the database operations within a transaction, you can commit the transaction using the commit() function.

php

// Execute your SQL queries here
// ...

// Commit the transaction
$conn->commit();

Rolling Back a Transaction 💡

If you encounter an error during the transaction, you can roll back the transaction using the rollback() function.

php

// Execute your SQL queries here
// ...

// If an error occurs, rollback the transaction
if ($error) {
    $conn->rollback();
}

Quiz 💡

Example: Creating a User 📝

Let's create a simple example where we insert a user into a table, then insert their favorite movies, and finally commit the transaction.

php

// Connect to the database
$conn = new mysqli("localhost", "username", "password", "database");

// Start the transaction
$conn->begin_transaction();

// Insert user
$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);
$name = "John Doe";
$email = "john@example.com";
$stmt->execute();

// Insert user's favorite movies
$stmt = $conn->prepare("INSERT INTO movies (user_id, movie) VALUES (?, ?)");
$stmt->bind_param("ii", $user_id, $movie);
$user_id = $conn->insert_id; // Get the inserted user's ID
$movie = "The Shawshank Redemption";
$stmt->execute();

// If an error occurs, rollback the transaction and display the error
if ($conn->errno) {
    $conn->rollback();
    echo "Error: " . $conn->error;
} else {
    // Commit the transaction
    $conn->commit();
    echo "User and movie added successfully!";
}

Remember, this is just a simple example to illustrate the concept of transactions. In real-world projects, you'll likely encounter more complex scenarios that require multiple queries and error handling.

Conclusion ✅

In this tutorial, we've learned what transactions are, why they're important, and how to create, commit, and rollback transactions in PHP MySQLi. You've also seen a practical example of using transactions to insert a user and their favorite movie.

Transactions are a powerful tool for ensuring data consistency and integrity in your PHP projects. Keep practicing, and you'll master this essential concept in no time! 💪

Happy coding! 💻🎨