PHP MySQL Delete Query using PDO

Originally posted @ https://codeanddeploy.com visit and download the sample code: https://codeanddeploy.com/blog/php/php-mysql-delete-query-using-pdo

In this post, I'm sharing how to Delete Query in PHP using PDO. I Will share several examples that easier to you to understand how to perform delete queries in PDO.

SQL Statement Table

To work with Delete query in PHP using PDO we need to set up first our database and table. In this example, we are working with simple posts table. See below sample SQL statement sample:

CREATE TABLE `posts` (
  `id` int(11) NOT NULL,
  `title` varchar(150) NOT NULL,
  `content` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `posts`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `posts`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
COMMIT;

MySQL Delete Query Statement

Usually, when deleting a record on MySQL we use the following SQL statement below:

Syntax:

DELETE FROM table_name WHERE condition;

Actual Delete Statement Example:

DELETE FROM posts WHERE id=1;

Now we have a basic idea of how to delete with MySQL.

Okay, let's implement it with PHP PDO Delete.

PDO Delete Query with Positional Placeholders Example

Positional placeholder brief and easier to use. See below example code:

<?php

$host     = 'localhost';
$db       = 'demos';
$user     = 'root';
$password = '';

$dsn = "mysql:host=$host;dbname=$db;charset=UTF8";

try {
     $conn = new PDO($dsn, $user, $password, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

} catch (PDOException $e) {
     echo $e->getMessage();
}

$data = [1];

$sql = "DELETE FROM posts WHERE id=?";

$statement = $conn->prepare($sql);

if($statement->execute($data)) {
  echo "Post deleted successfully!";
}

?>

PDO Delete Query with Named Placeholders

If you want a clearer predefined array with values this example is for you. See below code:

<?php

$host     = 'localhost';
$db       = 'demos';
$user     = 'root';
$password = '';

$dsn = "mysql:host=$host;dbname=$db;charset=UTF8";

try {
     $conn = new PDO($dsn, $user, $password, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

} catch (PDOException $e) {
     echo $e->getMessage();
}

$data = [
     'id' => 3
];

$sql = "DELETE FROM posts WHERE id=:id";

$statement = $conn->prepare($sql);

if($statement->execute($data)) {
  echo "Post deleted successfully!";
}

?>

PDO Delete Query with Named Placeholders using bindParam() Method

Using bindParam() is used to bind a parameter to the specified variable in a SQL Statement.

<?php

$host     = 'localhost';
$db       = 'demos';
$user     = 'root';
$password = '';

$dsn = "mysql:host=$host;dbname=$db;charset=UTF8";

try {
     $conn = new PDO($dsn, $user, $password, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);

} catch (PDOException $e) {
     echo $e->getMessage();
}

$data = [
     'id' => 4
];

$sql = "DELETE FROM posts WHERE id=:id";

$statement = $conn->prepare($sql);

$statement->bindParam(':id', $data['id'], PDO::PARAM_INT);

if($statement->execute()) {
     echo "Post deleted successfully!";
}

?>

I hope this tutorial can help you. Kindly visit here https://codeanddeploy.com/blog/php/php-mysql-delete-query-using-pdo if you want to download this code.

Happy coding :)