In the first tutorial in this series we looked at the three different ways you can connect to a mySQL database from PHP. We will next look at the different types of calls you can make to the database once you have established a connection. Just as we discarded the procedural mySQLi connection in the first tutorial as not being an appropriate connection to use, we will disregard all of the procedural calls throughout the rest of this series since the object oriented versions are almost identical to the procedural ones but a few less characters to type. The other thing we will do is to use prepare statements throughout. By preparing all of our database calls prior to executing them we can make our database calls more efficient plus we also get the side effect of being able to keep any data completely separate from the SQL code itself.
We will start with the simplest of the types of call that we can make to the database in terms of the amount of code we need to write and look first at how to code a delete command.
$stmt = $db->prepare("DELETE FROM tablename WHERE id=?");
$affected_rows = $stmt->affected_rows;
$stmt = $db->prepare("DELETE FROM tablename WHERE id=:id");
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$affected_rows = $stmt->rowCount();
As you can see, in this case the code for the two different types of calls is almost identical. Apart from a couple of minor differences in the names of the individual methods there are only really two actual differences between them.
The first difference is that PDO allows named placeholders and so we are able to use :id at the name for the particular placeholder in the code as an alternative to using ? as we have in the mySQLi version. PDO still allows us to use ? as placeholders and then reference them by number in the bindParam call but naming them means you don't have to count them - useful if there are more than a few placeholders in the command.
With mySQLi the first parameter of the bind_param method is a string with one letter per placeholder using 'i' integer, 'd' double, 's' string and 'b' blob. This is followed by a list of the variables to substitute for the placeholders in order. PDO uses one bindParam per placeholder instead and has the position or placeholder name as the first parameter, the value to substitute as the second parameter and a value indicating the type of the variable as the third parameter (with DO::PARAM_STR as the default value if the third parameter is omitted).
Where all of the placeholders are expecting a string value then PDO allows the bindParam lines tobe omitted and for the values to be passed as an array of key/value pairs (with the placeholder names as the keys) directly into the execute call instead. Alternatively if ? placeholders are used then a regular array of just the values can be used.
The last line of each of our code examples shows you how you can retrieve how many rows in the database were actually affected by the execution. Where we are trying to delete a single row this will set $affected_rows to 1 if the delete succeeded and 0 if it failed in both cases. Were what we want to delete can occur more than once in the table the actual number of rows deleted will be returned.
As you can see, when it comes to processing a simple delete command there is very little difference between mySQLi and PDO.
This article written by Stephen Chapman, Felgall Pty Ltd.