PHP PDO Delete

Summary: in this tutorial, you will learn how to delete one or more rows in a table from PHP using PDO.

To delete one or more rows from a table, you can use a prepared statement. Here are the steps:

• First, make a connection to the database by creating a new instance of the PDO class.
• Next, construct a DELETE statement.
• Then, create a prepared statement by calling the prepare() method of the PDO instance.
• After that, bind the parameters, if any, using the bindParam() method.
• Finally, execute the DELETE statement by calling the execute() method of the prepared statement.

Delete one row from a table

The following example illustrates how to use a prepared statement to delete the publisher with id 1 from the publishers table:

<?php

$publisher_id = 1;

// connect to the database and select the publisher
$pdo = require 'connect.php';

// construct the delete statement
$sql = 'DELETE FROM publishers
        WHERE publisher_id = :publisher_id';

// prepare the statement for execution
$statement = $pdo->prepare($sql);
$statement->bindParam(':publisher_id', $publisher_id, PDO::PARAM_INT);

// execute the statement
if ($statement->execute()) {
	echo 'publisher id ' . $publisher_id . ' was deleted successfully.';
}
Code language: HTML, XML (xml)

How it works.

First, create a connection to the bookdb database by using the connect.php script.

$pdo = require 'connect.php';Code language: PHP (php)

Next, construct a DELETE statement with a named placeholder :publisher_id:

$sql = 'DELETE FROM publishers
        WHERE publisher_id = :publisher_id';Code language: PHP (php)

Then, create a prepared statement by calling the prepare() method of the PDO instance:

$statement = $pdo->prepare($sql);Code language: PHP (php)

After that, bind the publisher_id parameter to the statement:

$statement->bindParam(':publisher_id', $publisher_id, PDO::PARAM_INT);Code language: PHP (php)

Finally, execute the DELETE statement:

if ($statement->execute()) {
	echo 'publisher id ' . $publisher_id . ' was deleted successfully.';
}Code language: PHP (php)

Delete multiple rows from a table

Deleting multiple rows from the table is the same as the steps for deleting one row from a table.

To find the number of rows deleted, you use the rowCount() method of the PDOStatement object.

The following example shows how to delete publishers with an id greater than 3:

<?php

$publisher_id = 3;

// connect to the database and select the publisher
$pdo = require 'connect.php';

$sql = 'DELETE FROM publishers
        WHERE publisher_id > :publisher_id';

$statement = $pdo->prepare($sql);
$statement->bindParam(':publisher_id', $publisher_id, PDO::PARAM_INT);

if ($statement->execute()) {
	echo $statement->rowCount() . ' row(s) was deleted successfully.';
}
Code language: HTML, XML (xml)

Output:

2 row(s) was deleted successfully.

Summary

• Use a prepared statement to delete a row from a table using PHP PDO.