Deleting rows with the PDO object.

This is a short tutorial on how to delete rows from a MySQL database using the PDO extension. In this example, I will connect to MySQL using the PDO object before deleting a specific row. For the sake of encouraging good practises, I will be using a prepared statement.

Let’s say that we have a table called cars and that we want to delete every car that is made by Honda. All rows have a column called “make”, which contains the name of the company that manufactured the car.

The code is pretty straightforward:

<?php

//MySQL connection details.
$host = 'localhost';
$user = 'root';
$pass = '';
$database = 'test';

//PDO options.
$options = array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false
);

//Connect to MySQL and create our PDO object.
$pdo = new PDO("mysql:host=$host;dbname=$database", $user, $pass, $options);

//Deleting a row using a prepared statement.
$sql = "DELETE FROM `cars` WHERE `make` = :make";

//Prepare our DELETE statement.
$statement = $pdo->prepare($sql);

//The make that we want to delete from our cars table.
$makeToDelete = 'Honda';

//Bind our $makeToDelete variable to the paramater :make.
$statement->bindValue(':make', $makeToDelete);

//Execute our DELETE statement.
$delete = $statement->execute();

A step-by-step guide:

  1. We connected to MySQL and instantiated our PDO object.
  2. We constructed a DELETE SQL statement. Note how we also created a placeholder called :make.
  3. We prepared our DELETE statement.
  4. In this example, we are wanting to delete cars with the make “Honda”.
  5. We bind our $makeToDelete variable (Honda) to our :make placeholder.
  6. Finally, we executed the statement and deleted the database rows in question.

As you can see, it’s actually pretty simple!

For more PDO-related articles, see: