Summary: in this tutorial, we will show you how to update data in the SQLite database using PHP PDO.
Steps for updating data in the SQLite database from PHP
The process of updating data is similar to the process of inserting data. To update data in a table, you use these steps:
- Connect to the SQLite database by creating a new PDO object.
- Prepare an UPDATE statement using the
prepare()
method of the PDO object. Theprepare()
method returns aPDOStatement
object. - Bind values to the parameters of the
UPDATE
statement using thebindValue()
method of thePDOStatement
object. - Execute the
UPDATE
statement by calling theexecute()
method of thePDOStatement
object. Theexecute()
method returns true on success or false on failure.
SQLite PHP: update data example
We will use the tasks
table that we created in the creating table tutorial for the demonstration.
See the following SQLiteUpdate
class.
<?php
namespace App;
/**
* PHP SQLite Update Demo
*/
class SQLiteUpdate {
/**
* PDO object
* @var \PDO
*/
private $pdo;
/**
* Initialize the object with a specified PDO object
*/
public function __construct($pdo) {
$this->pdo = $pdo;
}
/**
* Mark a task specified by the task_id completed
* @param type $taskId
* @param type $completedDate
* @return bool true if success and falase on failure
*/
public function completeTask($taskId, $completedDate) {
// SQL statement to update status of a task to completed
$sql = "UPDATE tasks "
. "SET completed = 1, "
. "completed_date = :completed_date "
. "WHERE task_id = :task_id";
$stmt = $this->pdo->prepare($sql);
// passing values to the parameters
$stmt->bindValue(':task_id', $taskId);
$stmt->bindValue(':completed_date', $completedDate);
// execute the update statement
return $stmt->execute();
}
}
Code language: PHP (php)
In the completeTask()
method, we update the completed
and completed_date
columns of the tasks
table using the UPDATE
statement.
Suppose you want to mark the task with id 2 completed, you use the following code in the index.php
file.
<?php
require 'vendor/autoload.php';
use App\SQLiteConnection;
use App\SQLiteUpdate;
$pdo = (new SQLiteConnection())->connect();
$sqlite = new SQLiteUpdate($pdo);
// mark task #2 as completed
$taskId = 2;
$result = $sqlite->completeTask($taskId, '2016-05-02');
if ($result)
echo 'Task #$taskId has been completed';
else
echo 'Whoops, something wrong happened.';
Code language: PHP (php)
Execute the index.php
script, we got the following message:
Task #2 has been completed
Code language: Shell Session (shell)
Let’s verify the update using the following SELECT statement:
SELECT *
FROM tasks
WHERE task_id = 2;
Code language: SQL (Structured Query Language) (sql)
The task with id 2 has been updated as shown in the screenshot above.
To get the number of rows affected by the UPDATE
statement, you use rowCount()
method of the PDOStatement
object.
<?php
$stmt->rowCount();
Code language: PHP (php)
In this tutorial, we have shown you how to update data in the SQLite table using PHP PDO.