PHP - Prepared statements in PDO

  1. HOME
  2. PHP
  3. Database
  4. PHP Data Objects (PDO)

Introduction

PDO provides the ability to prepare a statement.

A query that is parameterized.

You can specify parameters for the fields that will change in the query and then assign values to these parameters. 


$query = 'SELECT * FROM book WHERE author = :author'; 
$statement = $db->prepare($query); 
$statement->bindValue('author', 'C'); 
$statement->execute(); 
$rows = $statement->fetchAll(); 
var_dump($rows);

The query :author instead of the string of the author.

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.

Method
bindValue

 Description
it takes two arguments: the name of the parameter as described in the query and the value to assign.
If you provide a parameter name that is not in the query, this will throw an exception.
execute
 send the query to MySQL with the replacement of the parameters by the provided values.
fetchAll

 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.

Another way

You could prepare an array where the key is the name of the parameter and the value.

Then you can send it as the first argument of the execute method. 


$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

Related Topic