In this tutorial, we will be looking at deleting rows from a MySQL database. This is the last post in the series of standard operations using the PDO extension; just follow the links if you want to find out how to connect to the database, insert a record, or update a record using PDO.
If you are familiar with the PDO extension, then that’s great as this tutorial will be assuming you are. If not, then before starting this tutorial, you may want to have a look here for an introduction to PDO.
Deleting a row with PDO
As always, we need to make sure we are connected to our database:
<?php $user = 'your_user'; $pass = 'your_pass'; $db = new PDO( 'mysql:host=localhost;dbname=your_database', $user, $pass );
I have a table set up with two rows, as follows:
As you can see, a pretty straight forward table, only two rows, one of which we are going to delete. Let’s get rid of Tails Cat.
When deleting a row, we need to let MySQL know which record to delete. Usually, we would chose the id, as if you are setting your table up properly, you will make sure your id column is unique. Without using a unique field we could find ourselves getting into trouble – I imagine in the world of Cats, ‘Cat’ is a common surname, and ‘Tails’ is a common first name – but our id is unique, there will only be one cat with the id of ‘2’. This way, we ensure that there are no accidental row deletes.
Bearing all this in mind:
$id_to_delete = 2;
We then set up the MySQL query, remembering to always use a placeholder, signified by the colon, as we will be using prepared statements:
$sql = "DELETE FROM `users` WHERE `id` = :id_to_delete";
Prepare the statement:
$query = $db->prepare( $sql );
$query->execute( array( ":id_to_delete" => $id_to_delete ) );
If we now run this code, we will see that Mr Cat has been deleted from our users table and we are left only left with Claws McPaws:
This has been a very short and to the point post where you have learnt the syntax for a simple delete operation using PDO. I have assumed that you have remembered everything we have gone through in the previous posts on PDO and MySQL ( listed at the top of the post ), and so by now you should be finding yourself comfortable with using PDO, phpmyadmin, MySQL, and PHP. What we have learnt so far is very simple; there are different and more complex – and powerful – ways of doing the above that I will be covering in later posts. One thing we haven’t touched on is actually using our database interaction within our web page or app. So very soon I shall talk us through setting up a simple web app where we can add, update, and delete users, all from a user interface.
If we are going to start building some small apps, we will probably want them to look half-decent. So in the next tutorial, before we start on building our app, I will be introducing a simple and quick way to get our sites looking good very quickly, and it will be from using something called the Twitter Bootstrap. Essentially, the Twitter Bootstrap is a number of pre-prepared CSS styles that we can plug in to our site to get things looking great right from the start. And, yes it was put together by the guys at Twitter. Have a look a the site and get yourself familiar with it before our next tutorial.
So, there is plenty to look forward to :) In the meantime, if you would like to be kept up to date with new posts here at the web developing cat, then be sure to add your name to our mailing list below, cheers!