SQLite PHP: Deleting Data

Summary: in this tutorial, you will learn how to delete one or more rows from tables in an SQLite database in PHP using PDO.

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

To delete data from a table in PHP, you follow these steps:

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

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

Step 2. Construct a DELETE statement. If you want to pass data to the DELETE statement, you need to use named placeholders :name :

$sql = 'DELETE FROM table_name WHERE id = :id';Code language: PHP (php)

In this example, the :id is a placeholder whose value will be substituted by an actual value later.

Step 3. Call the prepare() method of the PDO object to prepare the DELETE 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(':id', $id);Code language: PHP (php)

The bindValue method substitutes the :id placeholder with the value of the $id variable.

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

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

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

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

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

Delete one row from a table

We’ll show you how to delete a row from the tasks table in PHP.

Step 1. Create the delete-task.php file in the project directory.

Step 2. Define the delete_task() function to delete a row from the tasks table by id:

<?php

require 'config.php';

function delete_task($pdo, $taskId) {
    $sql = 'DELETE FROM tasks WHERE task_id = :task_id';

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

    $stmt->execute();

    return $stmt->rowCount();    
}



$dsn = "sqlite:$db";

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

    // delete task #2
    $taskId = 1;
    $deleted = delete_task($pdo, $taskId);

    if($deleted > 0) {
        echo "Task $taskId deleted.";
    }

} catch(\PDOException $e) {
    echo $e->getMessage();
}Code language: PHP (php)

Step 3. Run the delete.php script.

It’ll show the following output:

Task 1 deleted.Code language: PHP (php)

Deleting related rows automatically

Step 1. Create a new file delete-project.php within the project directory.

Step 2. Define a function called delete_project that deletes a project by its id:

<?php

require 'config.php';

function delete_project($pdo, $projectId) {
    $sql = 'DELETE FROM projects WHERE project_id = :project_id';

    $stmt = $pdo->prepare($sql);
    $stmt->bindValue(':project_id', $projectId);

    $stmt->execute();

    return $stmt->rowCount();    
}



$dsn = "sqlite:$db";

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

    // delete the project id 1
    $projectId = 1;
    $deleted = delete_project($pdo, $projectId);

    if($deleted > 0) {
        echo "Project $projectId deleted.";
    }

} catch(\PDOException $e) {
    echo $e->getMessage();
}Code language: PHP (php)

Step 3. Run the script.

It’ll show the following output:

Project 1 deleted.Code language: PHP (php)

Due to the foreign key constraint ON DELETE CASCADE, the script also deletes all rows associated with the project id 1 from the tasks table.

Summary

• Use a prepared statement (PDOStatement) to execute an DELETE statement to delete data from a table.