SQLite PHP: Updating Data

Summary: in this tutorial, you will learn how to update data in the SQLite database using PHP PDO.

This tutorial begins where the “Insert data into a table in PHP” tutorial left off.

To update data in a SQLite table in PHP, you follow these steps:

Step 1. Connect to the SQLite database by creating a new PDO instance:

$pdo = new \PDO($dsn);Code language: PHP (php)

Step 2. Construct an UPDATE statement.

To pass values to the UPDATE statement, you need to use named placeholders :parameter . For example:

$sql = 'UPDATE table_name SET c1 = :c1 WHERE id = :id';Code language: PHP (php)

Step 3. Call the prepare() method of the PDO object to prepare the UPDATE statement for execution:

$stmt = pdo->prepare($sql);Code language: PHP (php)

The prepare() returns a PDOStatement object.

Step 4. Bind values to parameters by calling the bindValue() method of the PDOStatement object:

$stmt->bindValue(':c1', $value1);
$stmt->bindValue(':id', $id);Code language: PHP (php)

If you don’t want to bind values immediately, you can pass them when executing the UPDATE statement.

Step 5. Execute the UPDATE statement by calling the execute() method of the PDOStatement object:

$stmt->execute();Code language: PHP (php)

To execute the UPDATE statement with parameters but without calling the bindValue() method, you can pass the values directly to the execute() method like this:

$stmt->execute([
   ':c1' => $value1,
   ':id' => $id
]);Code language: PHP (php)

Updating data in PHP

We’ll show you how to update the data in the tasks table.

Step 1. Create the update.php file in the project directory.

Step 2. Define the set_completed_date() function to set the completed date for a task specified by task id and mark the task id 2 completed:

<?php

require_once 'config.php';

$dsn = "sqlite:$db";

function set_completed_date($pdo, $taskId, $completedDate) {
    $sql = 'UPDATE 
                tasks 
            SET 
                completed = 1,  
                completed_date = :completed_date  
            WHERE 
                task_id = :task_id';

    $stmt = $pdo->prepare($sql);
    
    $stmt->bindValue(':task_id', $taskId);
    $stmt->bindValue(':completed_date', $completedDate);

    return $stmt->execute();
}

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

    // mark task #2 as completed
    $taskId = 2;
    $completedDate = '2016-05-02';
    set_completed_date($pdo, $taskId, $completedDate);
} catch (\PDOException $e) {
    echo $e->getMessage();
}Code language: HTML, XML (xml)

Step 3. Run the update.php script.

Verifying data

The following steps show you how to query data from the tasks table to verify the update using the sqlite3 shell:

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

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

sqlite3 my.dbCode language: PHP (php)

Step 3. Format the query output:

.header on
.mode column
.nullvalue nullCode language: PHP (php)

Step 4. Query data from the projects table:

select * from tasks
where task_id = 2;Code language: PHP (php)

Output:

task_id  task_name           completed  start_date  completed_date  project_id
-------  ------------------  ---------  ----------  --------------  ----------
2        Create new tables   1          2016-05-01  2016-05-02      1Code language: PHP (php)

The output indicates that the completed_date was updated to 2016-05-02 and the completed column was updated to 1.

Summary

• Use the PDOStatement to execute an UPDATE statement to update data in a table.