{"id":981,"date":"2016-05-18T09:15:53","date_gmt":"2016-05-18T02:15:53","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=981"},"modified":"2024-10-28T21:59:57","modified_gmt":"2024-10-28T14:59:57","slug":"delete","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-php\/delete\/","title":{"rendered":"SQLite PHP: Deleting Data"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to delete one or more rows from tables in an SQLite database in PHP using PDO.<\/p>\n\n\n\n

This tutorial begins where the \u201cUpdate data in a table in PHP<\/a>\u201d tutorial left off.<\/p>\n\n\n\n

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

Step 1. Connect to the SQLite database file<\/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
Step 2. Construct a DELETE<\/code> statement. If you want to pass data to the DELETE<\/code> statement, you need to use named placeholders :name<\/code> :<\/p>\n\n\n
$sql = 'DELETE FROM table_name WHERE id = :id'<\/span>;<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, the :id<\/code> is a placeholder whose value will be substituted by an actual value later.<\/p>\n\n\n\n
Step 3. Call the prepare()<\/code> method of the PDO<\/code> object to prepare the DELETE<\/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(':id'<\/span>, $id);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The bindValue<\/code> method substitutes the :id<\/code> placeholder with the value of the $id<\/code> variable.<\/p>\n\n\n\n
If you don’t want to bind values immediately, you can pass them when executing the DELETE<\/code> statement.<\/p>\n\n\n\n
Step 5. Execute the DELETE<\/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 DELETE<\/code> statement 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   ':id'<\/span> => $id\n]);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Delete one row from a table<\/h2>\n\n\n\n
We’ll show you how to delete a row from the tasks<\/code> table in PHP.<\/p>\n\n\n\n
Step 1. Create the delete-task.php<\/code> file in the project directory.<\/p>\n\n\n\n
Step 2. Define the delete_task()<\/code> function to delete a row from the tasks table by id:<\/p>\n\n\n
<?php<\/span>\n\nrequire<\/span> 'config.php'<\/span>;\n\nfunction<\/span> delete_task<\/span>($pdo, $taskId)<\/span> <\/span>{\n    $sql = 'DELETE FROM tasks WHERE task_id = :task_id'<\/span>;\n\n    $stmt = $pdo->prepare($sql);\n    $stmt->bindValue(':task_id'<\/span>, $taskId);\n\n    $stmt->execute();\n\n    return<\/span> $stmt->rowCount();    \n}\n\n\n\n$dsn = \"sqlite:$db\"<\/span>;\n\ntry<\/span> {\n    \/\/ connect to database<\/span>\n    $pdo = new<\/span> \\PDO($dsn);\n\n    \/\/ delete task #2<\/span>\n    $taskId = 1<\/span>;\n    $deleted = delete_task($pdo, $taskId);\n\n    if<\/span>($deleted > 0<\/span>) {\n        echo<\/span> \"Task $taskId deleted.\"<\/span>;\n    }\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 delete.php<\/code> script.<\/p>\n\n\n\n
It’ll show the following output:<\/p>\n\n\n
Task 1<\/span> deleted.<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Deleting related rows automatically<\/h2>\n\n\n\n
Step 1. Create a new file delete-project.php<\/code> within the project directory.<\/p>\n\n\n\n
Step 2. Define a function called delete_project<\/code> that deletes a project by its id:<\/p>\n\n\n
<?php<\/span>\n\nrequire<\/span> 'config.php'<\/span>;\n\nfunction<\/span> delete_project<\/span>($pdo, $projectId)<\/span> <\/span>{\n    $sql = 'DELETE FROM projects WHERE project_id = :project_id'<\/span>;\n\n    $stmt = $pdo->prepare($sql);\n    $stmt->bindValue(':project_id'<\/span>, $projectId);\n\n    $stmt->execute();\n\n    return<\/span> $stmt->rowCount();    \n}\n\n\n\n$dsn = \"sqlite:$db\"<\/span>;\n\ntry<\/span> {\n    \/\/ connect to database<\/span>\n    $pdo = new<\/span> \\PDO($dsn);\n\n    \/\/ delete the project id 1<\/span>\n    $projectId = 1<\/span>;\n    $deleted = delete_project($pdo, $projectId);\n\n    if<\/span>($deleted > 0<\/span>) {\n        echo<\/span> \"Project $projectId deleted.\"<\/span>;\n    }\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 script.<\/p>\n\n\n\n
It’ll show the following output:<\/p>\n\n\n
Project 1<\/span> deleted.<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
Due to the foreign key constraint<\/a> ON DELETE CASCADE<\/code>, the script also deletes all rows associated with the project id 1 from the tasks<\/code> table.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use a prepared statement (PDOStatement<\/code>) to execute an DELETE<\/code> statement to delete data from 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