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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

<?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.