{"id":932,"date":"2016-05-16T09:56:33","date_gmt":"2016-05-16T02:56:33","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=932"},"modified":"2024-10-28T21:41:05","modified_gmt":"2024-10-28T14:41:05","slug":"update","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-php\/update\/","title":{"rendered":"SQLite PHP: Updating Data"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to update data in the SQLite database using PHP PDO.<\/p>\n\n\n\n

This tutorial begins where the “Insert data into a table in PHP<\/a>” tutorial left off.<\/p>\n\n\n\n

To update data in a SQLite table in PHP, you follow these steps:<\/p>\n\n\n\n

Step 1. Connect to the SQLite database<\/a> by creating a new PDO<\/code> instance:<\/p>\n\n\n

$pdo = new<\/span> \\PDO($dsn);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
To pass values to the UPDATE <\/a>statement, you need to use named placeholders :parameter<\/code> . For example:<\/p>\n\n\n
$sql = 'UPDATE table_name SET c1 = :c1 WHERE id = :id'<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Call the prepare()<\/code> method of the PDO<\/code> object to prepare the UPDATE<\/code> statement for execution:<\/p>\n\n\n
$stmt = pdo->prepare($sql);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The prepare()<\/code> returns a PDOStatement<\/code> object.<\/p>\n\n\n\n
Step 4. Bind values to parameters by calling the bindValue()<\/code> method of the PDOStatement<\/code> object:<\/p>\n\n\n
$stmt->bindValue(':c1'<\/span>, $value1);\n$stmt->bindValue(':id'<\/span>, $id);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
If you don’t want to bind values immediately, you can pass them when executing the UPDATE<\/code> statement.<\/p>\n\n\n\n
Step 5. Execute the UPDATE<\/code> statement by calling the execute()<\/code> method of the PDOStatement<\/code> object:<\/p>\n\n\n
$stmt->execute();<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
To execute the UPDATE<\/code> statement with parameters but without calling the bindValue()<\/code> method, you can pass the values directly to the execute()<\/code> method like this:<\/p>\n\n\n
$stmt->execute([\n   ':c1'<\/span> => $value1,\n   ':id'<\/span> => $id\n]);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Updating data in PHP<\/h2>\n\n\n\n
We’ll show you how to update the data in the tasks<\/code> table.<\/p>\n\n\n\n
Step 1. Create the update.php<\/code> file in the project directory.<\/p>\n\n\n\n
Step 2. Define the set_completed_date()<\/code> function to set the completed date for a task specified by task id and mark the task id 2 completed:<\/p>\n\n\n
<?php<\/span>\n\nrequire_once<\/span> 'config.php'<\/span>;\n\n$dsn = \"sqlite:$db\"<\/span>;\n\nfunction<\/span> set_completed_date<\/span>($pdo, $taskId, $completedDate)<\/span> <\/span>{\n    $sql = 'UPDATE \n                tasks \n            SET \n                completed = 1,  \n                completed_date = :completed_date  \n            WHERE \n                task_id = :task_id'<\/span>;\n\n    $stmt = $pdo->prepare($sql);\n    \n    $stmt->bindValue(':task_id'<\/span>, $taskId);\n    $stmt->bindValue(':completed_date'<\/span>, $completedDate);\n\n    return<\/span> $stmt->execute();\n}\n\ntry<\/span> {\n    \/\/ connect to database<\/span>\n    $pdo = new<\/span> \\PDO($dsn);\n\n    \/\/ mark task #2 as completed<\/span>\n    $taskId = 2<\/span>;\n    $completedDate = '2016-05-02'<\/span>;\n    set_completed_date($pdo, $taskId, $completedDate);\n} catch<\/span> (\\PDOException $e) {\n    echo<\/span> $e->getMessage();\n}<\/span><\/code><\/span>Code language:<\/span> HTML, XML<\/span> (<\/span>xml<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Run the update.php<\/code> script.<\/p>\n\n\n\n
Verifying data<\/h2>\n\n\n\n
The following steps show you how to query data from the tasks<\/code> table to verify the update using the sqlite3<\/code> shell:<\/p>\n\n\n\n
Step 1. Open your terminal and navigate to the database<\/code> directory in the project directory.<\/p>\n\n\n\n
Step 2.  Connect to the my.db<\/code> database using the sqlite3<\/code> shell:<\/p>\n\n\n
sqlite3 my.db<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Format the query output:<\/p>\n\n\n
.header on\n.mode column\n.nullvalue null<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 4. Query data from the projects<\/code> table:<\/p>\n\n\n
select * from tasks\nwhere task_id = 2<\/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-------  ------------------  ---------  ----------  --------------  ----------\n2<\/span>        Create new<\/span> tables   1<\/span>          2016<\/span>-05<\/span>-01<\/span>  2016<\/span>-05<\/span>-02<\/span>      1<\/span><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that the completed_date<\/code> was updated to 2016-05-02<\/code> and the completed<\/code> column was updated to 1<\/code>.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the PDOStatement<\/code> to execute an UPDATE<\/code> statement to update data in a table.<\/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. Construct an UPDATE<\/a> statement.<\/p>\n\n\n\n