Skip to main content

SQLite PHP:删除数据

从PHP PDO中删除数据的步骤

要从PHP应用程序中删除SQLite数据库表中的数据,请使用以下步骤:

SQLite PHP:删除数据示例

我们将使用上一个教程中创建的项目和任务进行演示。

首先,我们在app文件夹中创建SQLiteDelete类。

下面的deleteTask()方法按任务id删除任务。该方法通过调用PDOStatement对象的rowCount()方法返回删除的行数。

我们使用bindValue()方法将任务id值传递给DELETE语句。

    /**
* Delete a task by task id
* @param int $taskId
* @return int the number of rows deleted
*/
public function deleteTask($taskId) {
$sql = 'DELETE FROM tasks '
. 'WHERE task_id = :task_id';

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

$stmt->execute();

return $stmt->rowCount();
}

deleteTaskByProject()方法删除与项目id指定的项目关联的所有任务。

在这个方法中,我们不是使用bindValue()方法将值传递给DELETE语句,而是将输入值作为数组传递给execute()方法。

    /**
* Delete all tasks associated with a project
* @param int $projectId
* @return int the number of rows deleted
*/
public function deleteTaskByProject($projectId) {
$sql = 'DELETE FROM tasks '
. 'WHERE project_id = :project_id';

$stmt = $this->pdo->prepare($sql);

$stmt->execute([':project_id' => $projectId]);

return $stmt->rowCount();
}

以下方法删除由项目id指定的项目。它还返回项目表中已删除的行数。

此外,由于外键约束,与项目关联的所有任务也将被删除。ROONCOUNT()函数不考虑任务表中已删除的行。

    /**
* Delete the project by project id
* @param int $projectId
* @return int the number of rows deleted
*/
public function deleteProject($projectId) {
$sql = 'DELETE FROM projects '
. 'WHERE project_id = :project_id';

$stmt = $this->pdo->prepare($sql);

$stmt->execute([':project_id' => $projectId]);

return $stmt->rowCount();
}

其次,我们创建索引。测试SQLiteDelete类的php脚本。

<?php

require 'vendor/autoload.php';

use App\SQLiteConnection as SQLiteConnection;
use App\SQLiteDelete as SQLiteDelete;

$pdo = (new SQLiteConnection())->connect();
$sqlite = new SQLiteDelete($pdo);

// delete task id 1
$taskId = 1;
$rowDeleted = $sqlite->deleteTask($taskId);

echo 'The number of rows deleted: ' . $rowDeleted . '<br>';

// delete task associated with a project id 1
$projectId = 1;
$sqlite->deleteTaskByProject($projectId);

echo 'The number of task in the project #' . $projectId . ' deleted: ' . $rowDeleted . '<br>';

// delete project with id 1 and also its associated tasks
$projectId = 2;
$sqlite->deleteProject(2);
echo 'The number of project deleted: ' . $rowDeleted . '<br>';

以下是脚本的输出:

The number of rows deleted: 1
The number of task in the project #1 deleted: 1
The number of project deleted: 1

在本教程中,我们向您展示了如何使用PHP PDO删除SQLite数据库中一个或多个表中的数据。