SQLite PHP Transaction with Examples

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();

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();

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

$pdo->rollback();

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.db

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
);

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:

.tables

Output:

assignments  documents    members      projects     tasks

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();
}

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

It’ll create a new member with id 1:

member_id  name
---------  ----
1          Bob

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();
}

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;

Output:

task_id  task_name                                 completed  start_date  completed_date  project_id
-------  ----------------------------------------  ---------  ----------  --------------  ----------
11       Create a simple SQLite-based application  0          2016-06-01  null            1

Querying data from the assignments table:

SELECT * FROM assignments;

Output:

member_id  task_id
---------  -------
1          11

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.

Was this tutorial helpful?