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:
- We connected to MySQL and instantiated our PDO object.
- We constructed a DELETE SQL statement. Note how we also created a placeholder called :make.
- We prepared our DELETE statement.
- In this example, we are wanting to delete cars with the make “Honda”.
- We bind our $makeToDelete variable (Honda) to our :make placeholder.
- 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:
- Connecting to MySQL.
- Selecting rows with PDO.
- Updating rows with PDO.
- Inserting rows into a MySQL database using the PDO object.
- Multi-inserts with the PDO object.