PHP PDO Prepared Statement Tutorial for Beginners with Example – Part 2

In my last tutorial, We have seen PHP PDO with example. But PHP PDO true power lies in prepared statement. I have already covered prepared statement in mysqli procedural and mysqli object oriented . But let’s discuss one more time for PDO.

At this point I am assuming you know what is PHP PDO. If you don’t know then you should read my previous post.

What is Prepared Statement:

It is a feature used to execute same statement repeatedly with high efficiency. It is used almost in every SQL statement (insert, update, select, delete) with certain constant values.

Typically prepared statement works in three steps:

  1. Prepare: At first, application creates a template with placeholders and send to the database. PHP PDO uses 2 type of placeholders. One is positional placeholder and second is name placeholder.
  2. Compile: In this step, databases parses the statement and store the result without executing.
  3. Execute: At this stage, Application bind values for the parameters of the statement template and then database execute the statement. You can execute same statement many times with different placholders.

PHP PDO Prepared Statement Tutorial for Beginners with Example:

Now you know what is prepared statement and how it works. Let’s have a look at prepared statement functions use in PDO. I will only cover PDO functions related to PDO. Other functions are already discussed in previous tutorial.

Functions/Properties Description
prepare($statement, $driver_options) Prepares a statement for execution and returns a statement object. $statement must be a valid sql statement. $driver_options array holds one or more key=>value pairs to set attribute values for the PDOStatement object that this method returns
bindParam($parameter, $variable, $data_type, $length, $driver_options) Binds a parameter to the specified variable name. $parameter is parameter identifier. For a prepared statement using named placeholders, this will be a parameter name of the form :name. For a prepared statement using question mark placeholders, this will be the 1-indexed position of the parameter. $variable is a name of the php variable to bind with SQL parameter. $length is a length of a data type
execute() Executes a prepared statement

Create Database:

Create MySQL Table:

Insert Records in MYSQL Table:

Database Connection:

Fetch Single Row:

Select with Positional Placeholder ‘?’:

Select with Name Place Holder:

Select using LIKE Clause:

Insert Single Record:

Insert Multiple Records with Inserted ID:


Update Record:

Delete Record:

 

Posted in PHP

Author: Ahsan Zameer

I am a web developer and love to search new stuff on web. Trying different approaches and ideas to make web developing more interesting and enjoyable.