PHP Prepared Statement

Summary: in this tutorial, you will learn about the PHP prepared statements in PDO and how to use them effectively.

Introduction to PHP prepared statements

A prepared statement is a template for executing one or more SQL statements with different values. A prepared statement is highly efficient and helps protect the application against SQL injections.

When a database server executes a query, it goes through two main stages: preparation and execution.

• Preparation – the database server checks the syntax of the SQL statement and initializes internal server resources for the execution stage.
• Execution – the application binds the values and sends the SQL statement to the database server. The database server executes the statement with the bound values using the internal server resource allocated in the preparation stage.

Constructing a prepared statement in PDO

To construct a prepared statement in PDO, you follow these steps:

First, create a template SQL statement. For example:

$sql = 'insert into authors(first_name, last_name)
        values(?,?)';Code language: PHP (php)

This INSERT statement has two question marks (?). They are called positional placeholders.

When executing the statement, you need to pass values to the placeholders by their positions. In other words, you need to pass the first name to the first placeholder and the last name to the second placeholder

Second, call the prepare() method of a PDO instance:

$statement = $pdo->prepare($sql);Code language: PHP (php)

The prepare() method returns a new instance of the PDOStatement class.

Third, call the execute() method and pass the values to the placeholders:

$statement->execute(['Sandra', 'Aamodt']);Code language: PHP (php)

The execute() method will substitute the first placeholder by 'Sandra' and the second one by 'Aamodt' in the insert statement.

Put it all together.

The following shows how to use the prepared statement to insert a new row into the authors table:

<?php

$pdo = require 'connect.php';

$sql = 'insert into authors(first_name, last_name)
        values(?,?)';

$statement = $pdo->prepare($sql);

$statement->execute(['Sandra', 'Aamodt']);Code language: HTML, XML (xml)

Note that the script reuses the connect.php file that connects to bookdb database and returns a new instance of the PDO class.

Using named placeholders

When you use the positional placeholders in an SQL statement, you need to pass values that correspond to the positions of the placeholders.

If an SQL statement has many placeholders, it’s quite easy to use the wrong positions. To avoid this, you can use the named placeholders instead. For example:

$sql = 'insert into authors(first_name, last_name)
        values(:first_name,:last_name)';Code language: PHP (php)

In this example, instead of using the question marks (?), you use the parameter name prefixed by a colon (:). The colon is required in the SQL statement.

When executing the statement, you need to pass an associative array to the execute() method like this:

$statement->execute([
	'first_name' => 'Henry',
	'last_name' => 'Aaron'
]);Code language: PHP (php)

Note that the key of the array is important, not the order of elements. Also, you can optionally use the : in the keys of the array:

$statement->execute([
	':first_name' => 'Henry',
	':last_name' => 'Aaron'
]);Code language: PHP (php)

The order of the array element is not important so you can use an array with elements in any order. For example:

$statement->execute([
	'last_name' => 'Aaron',
	'first_name' => 'Henry',
]);Code language: PHP (php)

Put it all together.

<?php

$pdo = require 'connect.php';

$sql = 'insert into authors(first_name, last_name)
        values(:first_name,:last_name)';

$statement = $pdo->prepare($sql);

$statement->execute([
	'last_name' => 'Aaron',
	'first_name' => 'Henry',
]);Code language: HTML, XML (xml)

Bound values

In the above examples, we pass the values to the execute() method to run the query. These statements are called unbound statements.

Besides the unbound statements, PDO also supports bound statements. The bound statements allow you to explicitly bind a value or a variable to a named or positional placeholder.

To bind a value, you use the bindValue() method of the PDOStatement object:

public PDOStatement::bindValue ( mixed $parameter , mixed $value , int $data_type = PDO::PARAM_STR ) : boolCode language: PHP (php)

The bindValue() method has three parameters:

• $parameter specifies the parameter name :parameter if the statement uses the named placeholders or index of the parameter if the statement uses positional placeholders. In case you use the positional placeholder, the first parameter starts with the index 1.
• $value specifies the value to bind to the parameter.
• $data_type specifies the data type for the parameter using the PDO::PARAM_* e.g., PDO::PARAM_INT. By default, the $data_type is PDO::PARAM_STR.

The following example shows how to insert the author Nick Abadzis into the authors table using a bound statement:

<?php

$pdo = require 'connect.php';

$sql = 'insert into authors(first_name, last_name)
        values(?,?)';

$statement = $pdo->prepare($sql);

$statement->bindValue(':first_name', 'Nick');
$statement->bindValue(':last_name', 'Abadzis');

$statement->execute();Code language: HTML, XML (xml)

When you use the bindValue() method, the execute() method executes the statement with the values passed to the bindValue() method, not the values at the time the execute() method runs. For example:

<?php

$pdo = require 'connect.php';

$sql = 'insert into authors(first_name, last_name)
        values(:first_name,:last_name)';

$statement = $pdo->prepare($sql);

$author = [
	'first_name' => 'Chris',
	'last_name' => 'Abani',
];

$statement->bindValue(':first_name', $author['first_name']);
$statement->bindValue(':last_name', $author['last_name']);

// change the author variable
$author['first_name'] = 'Tom';
$author['last_name'] = 'Abate';

// execute the query with value Chris Abani
$statement->execute();Code language: HTML, XML (xml)

In this example:

• First, bind the value 'Chris' and 'Abate' to the first name and last name parameters.
• Second, change the values of the variable $author.
• Third, execute the query. However, the execute() method uses the values passed to the bindValue() method, not the $author value at the time the execute() method runs.

This is why the bindParam() method comes into play.

The bindParam() method

To execute a statement whose values of the parameters are evaluated at the time the execute() method runs, you use the bindParam() method:

public PDOStatement::bindParam ( mixed $parameter , mixed &$variable , int $data_type = PDO::PARAM_STR , int $length = ? , mixed $driver_options = ? ) : boolCode language: PHP (php)

The following example illustrates how to use the bindParam() method to insert a new author into the authors table:

<?php

$pdo = require 'connect.php';

$sql = 'insert into authors(first_name, last_name)
        values(:first_name,:last_name)';

$statement = $pdo->prepare($sql);

$author = [
	'first_name' => 'Chris',
	'last_name' => 'Abani',
];

$statement->bindParam(':first_name', $author['first_name']);
$statement->bindParam(':last_name', $author['last_name']);

// change the author variable
$author['first_name'] = 'Tom';
$author['last_name'] = 'Abate';

// execute the query with value Tom Abate
$statement->execute();Code language: HTML, XML (xml)

In this example, the execute() method evaluates the $author variable at the time of execution so that it uses 'Tom' and 'Abage' values instead.

Summary

• Use a PHP prepared statement to execute a query multiple times with different values.
• Use positional placeholders (?) or named placeholders (:parameter) in the SQL statement before passing it the prepare() method of an PDOStatement object.
• Use the execute() method with values to run an unbound statement.
• Use bindValue() or bindParam() method to bind values to a bound statement.