Prepared statement is a feature used to execute the same SQL statements repeatedly with high efficiency. Prepared statements are very useful against SQL injections.
As we have seen the conventional methods of executing the queries in the following posts:
PDO php data objects introduction
PDO provides the ability to prepare a statement—that is, a query that is parameterized. We can specify parameters for the fields that will change in the query and then assign values to these parameters. Let’s consider first an example, as follows:
$query = 'SELECT * FROM book WHERE author = :author';
$statement = $db->prepare($query);
$statement->bindValue('author', 'George Orwell');
$statement->execute();
$rows = $statement->fetchAll();
var_dump($rows);
The query is a normal one except that it has :author instead of the string of the author that we want to find. This is a parameter, and we will identify them using the prefix :. The prepare method gets the query as an argument and returns a PDOStatement instance.
This class contains several methods to bind values, execute statements, fetch results, and more. In this piece of code, we use only three of them, as follows:
• bindValue: This takes two arguments: the name of the parameter as described in the query and the value to assign. If we provide a parameter name that is not in the query, this will throw an exception.
• execute: This will send the query to MySQL with the replacement of the parameters by the provided values. If there is any parameter that is not assigned to a value, the method will throw an exception.
• fetchAll: This will retrieve the data from MySQL in case it was a SELECT query. As a query, fetchAll will return a list of all rows as arrays.
If we try this code, we can note that the result is very similar to when using a query; however, this time, the code is much more dynamic as you can reuse it for any author that you need.
There is another way to bind values to the parameters of a query than using the bindValue method. We could prepare an array where the key is the name of the parameter and the value is the value we want to assign to it, and then we can send it as the first argument of the execute method.
This way is quite useful as usually you already have this array prepared and do not need to call bindValue several
times with its content. Add this code in order to test it:
$query = <<<SQL INSERT INTO book (isbn, title, author, price) VALUES (:isbn, :title, :author, :price) SQL; $statement = $db->prepare($query); $params = [ 'isbn' => '9781412108614', 'title' => 'Iliad', 'author' => 'Homer', 'price' => 9.25 ]; $statement->execute($params); echo $db->lastInsertId(); // 8
In this last example, we created a new book with almost all the parameters, but we did not specify the ID, which is the desired behavior as we want MySQL to choose a valid one for us.
However, what happens if we want to know the ID of the inserted row? Well, we could query MySQL for the book with the same ISBN and the returned row would contain the ID, but this seems like a lot of work. Instead, PDO
has the lastInsertId method, which returns the last ID inserted by a primary key, saving us from one extra query.