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
DELETEstatement. - 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
DELETEstatement by calling theexecute()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:
$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:
$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.