In this article, I will show you about the PHP prepared statements in PDO, their avantages and how to use them effectively.

What is PHP prepared statements in PHP?

Prepared statements for executing a plurality of the same SQL statement, when a database server executes a query, it goes through two main phases: 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.

What are the advantages of prepared statements?

First, the Prepared Statement is a slightly more powerful version of a Statement, and should always be at least as quick and easy to handle as a Statement.

Precompilation and DB-side caching of the SQL statement leads to overall faster execution and the ability to reuse the same SQL statement in batches.

The Prepared Statement may be parametrized, that the reason why it is highly efficient and helps protect the application against SQL injections.

Finally, it’s general idea of using placeholders is really great, while prepared statements is just a subset of placeholders with limited functionality.

How can we do to construct a prepared statement in PDO?

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

First, create a template SQL statement. For example:

This DELETE statement has a value « :id ». It is called positional placeholder.
When executing the statement, you need to pass this value to the placeholders by its position. In other words, you need to pass the id to the placeholder
Second, call the prepare() method of a PDO instance:

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

Third, call the function blindParam to set the value of « :id »:

Fourth, call the execute() method and pass the value to the placeholder:

Put it all together, the following shows how to use the prepared statement to delete a user in the table by its ID:

Notice : We have two types of placeholders: named placeholders (:parameter) and nonamed / positional placeholders (?)

Example with named placeholders:

Example with positional placeholders:

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.

---