How to delete rows with the PDO object

This PHP tutorial will show you how to delete rows from a MySQL database using the PDO object.

To abide by best security practices and guard against SQL injection, we will be using prepared statements.

Deleting rows with PDO’s positional placeholders.

Let’s say, for example, that we have a table called “cars” and that we want to delete all rows that have “Honda” stored in the “make” column.

The following code uses PDO’s positional (?) placeholders:

//Create the SQL statement
$sql = "DELETE FROM cars WHERE make = ?";

//Prepare the statement
$statement = $pdo->prepare($sql);

//Execute the statement and send the values in an array
$statement->execute(array('Honda'));

This is the quickest and easiest way to delete rows using prepared statements. However, it can get a bit confusing if you are referencing multiple columns in the WHERE clause of your SQL.

Deleting rows with named placeholders.

If the WHERE clause of your DELETE statement references multiple columns, then you may want to use named placeholders.

This will increase the clarity of your PHP code and make it easier to maintain.

In the following example, we will connect to MySQL using the PDO object and then delete all table rows that contain “Honda” as the make and “2012” as the year.

//Your 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);

//Create the DELETE SQL with named placeholders.
$sql = "DELETE FROM `cars` WHERE `make` = :make AND `year` = :year";

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

//Pass our data in and execute the DELETE statement.
$delete = $statement->execute(array(
    'make' => 'Honda',
    'year' => '2012'
));

In the code above, we:

  1. Connected to MySQL and instantiated our PDO object.
  2. Created a DELETE SQL statement using the placeholders :make and :year.
  3. Prepared the SQL statement.
  4. Finally, we passed in the data for our placeholders, executed the statement, and deleted the database rows in question.

Named placeholders are useful because it is easier to see which variable corresponds to each column.

For more PDO-related articles, see: