DEV Community

Cover image for PHP MySQL Delete Query using PDO
Code And Deploy
Code And Deploy

Posted on

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Actual Delete Statement Example:

DELETE FROM posts WHERE id=1;
Enter fullscreen mode Exit fullscreen mode

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!";
}

?>
Enter fullscreen mode Exit fullscreen mode

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!";
}

?>
Enter fullscreen mode Exit fullscreen mode

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!";
}

?>
Enter fullscreen mode Exit fullscreen mode

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 :)

Top comments (0)