Confirming Changes with the commit() Method

The commit() method is used to complete a transaction and permanently save all changes made since beginTransaction() was called. When you call this method, the database updates its state, and the changes become visible to all other users. If you do not confirm the transaction using this method, all changes will be automatically undone (rolled back) at the end of the script.

EXAMPLE

<?php
class Database {
    private $server = "localhost";
    private $username = "root";
    private $password = "";
    private $db_name = "pdo_db";
    public $connection;

    public function __construct() {
        try {
            // Added charset=utf8mb4 for character support
            $dsn = "mysql:host=" . $this->server . ";dbname=" . $this->db_name . ";charset=utf8mb4";
            $this->connection = new PDO($dsn, $this->username, $this->password);
            $this->connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch(PDOException $e) {
            die("Connection failed: " . $e->getMessage());
        }
    }

    // Method for starting a transaction
    public function startTransaction() {
        return $this->connection->beginTransaction();
    }

    // Method for committing a transaction
    public function confirm() {
        return $this->connection->commit();
    }
}

// PART 3: Usage
$db = new Database();

try {
    $db->startTransaction();
    
    // Execute a random entry
    $sql = "INSERT INTO users (first_name, last_name, email) VALUES ('Mary', 'Green', 'mary@example.com')";
    $db->connection->exec($sql);
    
    // If everything is fine, we confirm the changes
    if ($db->confirm()) {
        echo "Transaction was successfully committed and data is saved!";
    }
} catch (Exception $e) {
    echo "An error occurred, data was not saved.";
}
?>

RESULT

The result will be displayed here...

Practical Application: We only call the commit method at the very end when we are certain that all SQL commands (e.g., transferring money from one account to another) executed without any errors. Check the process at home by following these steps:

Start the Apache and MySQL modules in the XAMPP control panel.
Ensure via phpMyAdmin that the users table exists within the pdo_db database.
The method returns a boolean value (true/false) depending on the success of the transaction confirmation.
Insert the example into your PHP file and check how the transaction permanently saves the new user.