SQLite PHP: Transaction

Summary: in this tutorial, you will learn how to manage transactions in SQLite databases from PHP using PDO to ensure data integrity.

This tutorial begins where the “Working with BLOB data in PHP” tutorial left off.

Managing transactions

When you update data in an SQLite database, PHP automatically commits the changes to the database.

To control when the PHP commits changes to the database, you can wrap the database operations within a transaction.

Step 1. Call the beginTransaction() method of the PDO object to start a transaction:

$pdo->beginTransaction();Code language: PHP (php)

Step 2. Perform database operations such as insert, update, and delete.

Step 3. Apply changes permanently to the database by calling the commit() method of the PDO object:

$pdo->commit();Code language: PHP (php)

Step 4. If something is wrong or you want to cancel the changes, you can call the rollback() method:

$pdo->rollback();Code language: PHP (php)

Creating new tables

Step 1. Open your terminal and navigate to the database directory of the project.

Step 2. Connect to the my.db database using sqlite3 shell:

sqlite3 my.dbCode language: Shell Session (shell)

Step 3. Execute the following CREATE TABLE statement to create two new tables calledmembers and assignments:

CREATE TABLE members(
    member_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL    
);

CREATE TABLE assignments(
   member_id INT,
   task_id INT,
   FOREIGN KEY(member_id) REFERENCES members(member_id) ON DELETE CASCADE,
   FOREIGN KEY(task_id) REFERENCES tasks(task_id) ON DELETE CASCADE
);Code language: SQL (Structured Query Language) (sql)

A member can have multiple tasks, and a task can belong to multiple members. The assignments table creates a many-to-many relationship between the tasks and members tables.

Suppose we want to create a new task and assign it to a member. Additionally, we want both actions complete as a whole:

• Create a new task.
• Assign it to a member.

To guarantee this, both actions need to occur together in an all-or-nothing manner. We can achieve this using the PDO transaction feature.

Step 4. List all the tables:

.tablesCode language: PHP (php)

Output:

assignments  documents    members      projects     tasksCode language: PHP (php)

Adding a member to the members table

Step 1. Create a new file insert-member.php within the project directory.

Step 2. Insert a row into the members table:

<?php

require_once 'config.php';

function insert_member($pdo, $name) {
    $sql = 'INSERT INTO members(name) VALUES(:name)';
    $stmt = $pdo->prepare($sql);
    $stmt->execute([
        ':name' => $name,
    ]);
    return $pdo->lastInsertId();
}

$dsn = "sqlite:$db";

try {
    $pdo = new \PDO($dsn);
    insert_member($pdo, 'Bob');

} catch(\PDOException $e){
    echo $e->getMessage();
}Code language: PHP (php)

Step 3. Run the insert-member.php script.

It’ll create a new member with id 1:

member_id  name
---------  ----
1          BobCode language: PHP (php)

SQLite PHP transaction example

Step 1. Create a new file transaction.php within the project directory:

<?php

require_once 'config.php';


function insert_task($pdo, $taskName,  $startDate, $completedDate, $completed, $projectId) {

    $sql = 'INSERT INTO tasks(task_name,start_date,completed_date,completed,project_id) '
                . 'VALUES(:task_name,:start_date,:completed_date,:completed,:project_id)';

    $stmt = $pdo->prepare($sql);

    $stmt->execute([
        ':task_name' => $taskName,
        ':start_date' => $startDate,
        ':completed_date' => $completedDate,
        ':completed' => $completed,
        ':project_id' => $projectId,
    ]);

    return $pdo->lastInsertId();
}

function insert_assignment($pdo , $taskId, $memberId) {

    $sql = 'INSERT INTO task_assignments(task_id,member_id) VALUES(:task_id,:member_id)';
    $stmt = $pdo->prepare($sql);

    $stmt->execute([
        ':task_id' => $taskId,
        ':member_id' => $memberId,
    ]);

}


function assign_task($pdo, $taskName,  $startDate, $completedDate, $completed, $projectId, $member_id) {

    try {
        $pdo->beginTransaction();

        $taskId = insert_task($pdo, $taskName,  $startDate, $completedDate, $completed, $projectId);

        if ($taskId > 0) {
            insert_assignment($pdo, $taskId, $member_id);
            $pdo->commit();
        }  else {
            $pdo->rollBack();
            throw new \Exception('Failed to insert task');
        }
        
    } catch(\PDOException $e){
        echo $e->getMessage();
        $pdo->rollback();
        throw $e;
    }
}

try {
    // connect to database
    $pdo = new \PDO($dsn);

    // enable foreign keys
    $pdo->exec('PRAGMA foreign_keys = ON');

    // assign a task to a member
    $member_id = 1;
    $taskName = 'Create a simple SQLite-based application';
    $startDate = '2016-06-01';

    assign_task($pdo, $taskName, $startDate, null, 0, 1, $member_id);

} catch(\PDOException $e) {
    echo $e->getMessage();
}Code language: PHP (php)

Step 2. Run the transaction.php script.

Verify the data

Querying data from the tasks table:

SELECT * FROM tasks ORDER BY task_id DESC LIMIT 1;Code language: PHP (php)

Output:

task_id  task_name                                 completed  start_date  completed_date  project_id
-------  ----------------------------------------  ---------  ----------  --------------  ----------
11       Create a simple SQLite-based application  0          2016-06-01  null            1Code language: PHP (php)

Querying data from the assignments table:

SELECT * FROM assignments;Code language: PHP (php)

Output:

member_id  task_id
---------  -------
1          11Code language: PHP (php)

Summary

• Call the $pdo->beginTransaction() to start a transaction.
• Call the $pdo->commit() method to apply the changes permanently to the database.
• Call the $pdo->rollback() method to cancel the changes.