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:
//MySQL connection details.
$host = 'localhost';
$user = 'root';
$pass = '';
$database = 'test';
$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.
//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.