{"id":960,"date":"2016-05-17T11:20:33","date_gmt":"2016-05-17T04:20:33","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=960"},"modified":"2024-10-28T22:17:43","modified_gmt":"2024-10-28T15:17:43","slug":"transaction","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-php\/transaction\/","title":{"rendered":"SQLite PHP: Transaction"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to manage transactions in SQLite databases from PHP using PDO to ensure data integrity.<\/p>\n\n\n\n

This tutorial begins where the \u201cWorking with BLOB data in PHP<\/a>\u201d tutorial left off.<\/p>\n\n\n\n

Managing transactions<\/h2>\n\n\n\n

When you update data in an SQLite database, PHP automatically commits the changes to the database. <\/p>\n\n\n\n

To control when the PHP commits changes to the database, you can wrap the database operations within a transaction<\/em>.<\/p>\n\n\n\n

Step 1. Call the beginTransaction()<\/code> method of the PDO<\/code> object to start a transaction<\/a>:<\/p>\n\n\n

$pdo->beginTransaction();<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Apply changes permanently to the database by calling the commit()<\/code> method of the PDO object:<\/p>\n\n\n
$pdo->commit();<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 4. If something is wrong or you want to cancel the changes, you can call the rollback()<\/code> method:<\/p>\n\n\n
$pdo->rollback();<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Creating new tables<\/h2>\n\n\n\n
Step 1. Open your terminal and navigate to the database<\/code> directory of the project.<\/p>\n\n\n\n
Step 2. Connect to the my.db<\/code> database using sqlite3<\/code> shell:<\/p>\n\n\n
sqlite3 my.db<\/code><\/span>Code language:<\/span> Shell Session<\/span> (<\/span>shell<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Execute the following CREATE TABLE<\/code> statement to create two new tables calledmembers<\/code> and assignments<\/code>:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> members(\n    member_id INTEGER<\/span> PRIMARY KEY<\/span>,\n    name<\/span> TEXT<\/span> NOT<\/span> NULL<\/span>    \n);\n\nCREATE<\/span> TABLE<\/span> assignments(\n   member_id INT<\/span>,\n   task_id INT<\/span>,\n   FOREIGN<\/span> KEY<\/span>(member_id) REFERENCES<\/span> members(member_id) ON<\/span> DELETE<\/span> CASCADE<\/span>,\n   FOREIGN<\/span> KEY<\/span>(task_id) REFERENCES<\/span> tasks(task_id) ON<\/span> DELETE<\/span> CASCADE<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
A member can have multiple tasks, and a task can belong to multiple members. The assignments<\/code> table creates a many-to-many<\/em> relationship between the tasks<\/code> and members<\/code> tables.<\/p>\n\n\n\n
Suppose we want to create a new task and assign it to a member. Additionally, we want both actions complete as a whole:<\/p>\n\n\n\n
    \n
  • Create a new task.<\/li>\n\n\n\n
  • Assign it to a member.<\/li>\n<\/ul>\n\n\n\n
    To guarantee this, both actions need to occur together in an all-or-nothing manner. We can achieve this using the PDO transaction feature.<\/p>\n\n\n\n
    Step 4. List all the tables:<\/p>\n\n\n
    .tables<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    assignments  documents    members      projects     tasks<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
    Adding a member to the members table<\/h2>\n\n\n\n
    Step 1. Create a new file insert-member.php<\/code> within the project directory.<\/p>\n\n\n\n
    Step 2. Insert a row into the members<\/code> table:<\/p>\n\n\n
    <?php<\/span>\n\nrequire_once<\/span> 'config.php'<\/span>;\n\nfunction<\/span> insert_member<\/span>($pdo, $name)<\/span> <\/span>{\n    $sql = 'INSERT INTO members(name) VALUES(:name)'<\/span>;\n    $stmt = $pdo->prepare($sql);\n    $stmt->execute([\n        ':name'<\/span> => $name,\n    ]);\n    return<\/span> $pdo->lastInsertId();\n}\n\n$dsn = \"sqlite:$db\"<\/span>;\n\ntry<\/span> {\n    $pdo = new<\/span> \\PDO($dsn);\n    insert_member($pdo, 'Bob'<\/span>);\n\n} catch<\/span>(\\PDOException $e){\n    echo<\/span> $e->getMessage();\n}<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
    Step 3. Run the insert-member.php<\/code> script.<\/p>\n\n\n\n
    It’ll create a new member with id 1:<\/p>\n\n\n
    member_id  name\n---------  ----\n1<\/span>          Bob<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
    SQLite PHP transaction example<\/h2>\n\n\n\n
    Step 1. Create a new file transaction.php<\/code> within the project directory:<\/p>\n\n\n
    <?php<\/span>\n\nrequire_once<\/span> 'config.php'<\/span>;\n\n\nfunction<\/span> insert_task<\/span>($pdo, $taskName,  $startDate, $completedDate, $completed, $projectId)<\/span> <\/span>{\n\n    $sql = 'INSERT INTO tasks(task_name,start_date,completed_date,completed,project_id) '<\/span>\n                . 'VALUES(:task_name,:start_date,:completed_date,:completed,:project_id)'<\/span>;\n\n    $stmt = $pdo->prepare($sql);\n\n    $stmt->execute([\n        ':task_name'<\/span> => $taskName,\n        ':start_date'<\/span> => $startDate,\n        ':completed_date'<\/span> => $completedDate,\n        ':completed'<\/span> => $completed,\n        ':project_id'<\/span> => $projectId,\n    ]);\n\n    return<\/span> $pdo->lastInsertId();\n}\n\nfunction<\/span> insert_assignment<\/span>($pdo , $taskId, $memberId)<\/span> <\/span>{\n\n    $sql = 'INSERT INTO task_assignments(task_id,member_id) VALUES(:task_id,:member_id)'<\/span>;\n    $stmt = $pdo->prepare($sql);\n\n    $stmt->execute([\n        ':task_id'<\/span> => $taskId,\n        ':member_id'<\/span> => $memberId,\n    ]);\n\n}\n\n\nfunction<\/span> assign_task<\/span>($pdo, $taskName,  $startDate, $completedDate, $completed, $projectId, $member_id)<\/span> <\/span>{\n\n    try<\/span> {\n        $pdo->beginTransaction();\n\n        $taskId = insert_task($pdo, $taskName,  $startDate, $completedDate, $completed, $projectId);\n\n        if<\/span> ($taskId > 0<\/span>) {\n            insert_assignment($pdo, $taskId, $member_id);\n            $pdo->commit();\n        }  else<\/span> {\n            $pdo->rollBack();\n            throw<\/span> new<\/span> \\Exception<\/span>('Failed to insert task'<\/span>);\n        }\n        \n    } catch<\/span>(\\PDOException $e){\n        echo<\/span> $e->getMessage();\n        $pdo->rollback();\n        throw<\/span> $e;\n    }\n}\n\ntry<\/span> {\n    \/\/ connect to database<\/span>\n    $pdo = new<\/span> \\PDO($dsn);\n\n    \/\/ enable foreign keys<\/span>\n    $pdo->exec('PRAGMA foreign_keys = ON'<\/span>);\n\n    \/\/ assign a task to a member<\/span>\n    $member_id = 1<\/span>;\n    $taskName = 'Create a simple SQLite-based application'<\/span>;\n    $startDate = '2016-06-01'<\/span>;\n\n    assign_task($pdo, $taskName, $startDate, null<\/span>, 0<\/span>, 1<\/span>, $member_id);\n\n} catch<\/span>(\\PDOException $e) {\n    echo<\/span> $e->getMessage();\n}<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
    Step 2. Run the transaction.php<\/code> script.<\/p>\n\n\n\n
    Verify the data<\/h2>\n\n\n\n
    Querying data from the tasks<\/code> table:<\/p>\n\n\n
    SELECT * FROM tasks ORDER BY task_id DESC LIMIT 1<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    task_id  task_name                                 completed  start_date  completed_date  project_id\n-------  ----------------------------------------  ---------  ----------  --------------  ----------\n11<\/span>       Create a simple SQLite-based application  0<\/span>          2016<\/span>-06<\/span>-01<\/span>  null<\/span>            1<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
    Querying data from the assignments<\/code> table:<\/p>\n\n\n
    SELECT * FROM assignments;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    member_id  task_id\n---------  -------\n1<\/span>          11<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Call the $pdo->beginTransaction()<\/code> to start a transaction.<\/li>\n\n\n\n
    • Call the $pdo->commit()<\/code> method to apply the changes permanently to the database.<\/li>\n\n\n\n
    • Call the $pdo->rollback()<\/code> method to cancel the changes.<\/li>\n<\/ul>\n
      \n\t
      \n\t\t
      Was this tutorial helpful ?<\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t
Step 2. Perform database operations such as insert<\/a>, update<\/a>, and delete<\/a>.<\/p>\n\n\n\n