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:

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.
Compile: In this step, databases parses the statement and store the result without executing.
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 database;

1	create database;

Create MySQL Table:

CREATE TABLE `customers` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`first_name` VARCHAR(255) NULL DEFAULT NULL,
	`last_name` VARCHAR(255) NULL DEFAULT NULL,
	`address` VARCHAR(255) NULL DEFAULT NULL,
	`email` VARCHAR(255) NULL DEFAULT NULL,
	`phone` VARCHAR(255) NULL DEFAULT NULL,
	`country` VARCHAR(255) NULL DEFAULT NULL,
	`city` VARCHAR(255) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

CREATE TABLE `customers` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`first_name` VARCHAR(255) NULL DEFAULT NULL,

`last_name` VARCHAR(255) NULL DEFAULT NULL,

`address` VARCHAR(255) NULL DEFAULT NULL,

`email` VARCHAR(255) NULL DEFAULT NULL,

`phone` VARCHAR(255) NULL DEFAULT NULL,

`country` VARCHAR(255) NULL DEFAULT NULL,

`city` VARCHAR(255) NULL DEFAULT NULL,

PRIMARY KEY (`id`)

)

COLLATE='latin1_swedish_ci'

ENGINE=InnoDB

AUTO_INCREMENT=1

;

Insert Records in MYSQL Table:

INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (108, 'Mikey', 'Ginnelly', '49222 Farwell Way', 'mginnellyd@ucsd.edu', '557-640-0626', 'Brazil', 'Cupira');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (107, 'Easter', 'Prebble', '35 Sherman Alley', 'eprebble0@go.com', '718-202-0367', 'Indonesia', 'Sukasetia');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (106, 'Fionna', 'Danilyuk', '35 Sherman Alley', 'eprebble0@go.com', '718-202-0367', 'Indonesia', 'Sukasetia');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (103, 'Tony', 'Stark', 'zyx house', 'tony.stark@ironman.com', '1-8541-8597-12', 'USA', 'New York City');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (102, 'Aggi', 'Sandhill', '94896 Mallard Parkway', 'asandhillj@shutterfly.com', '110-327-1151', 'Latvia', 'Ape');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (101, 'Juditha', 'Francecione', '6301 Barby Court', 'jfrancecione3@ebay.com', '947-224-5435', 'Colombia', '	Caicedonia');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (99, 'Edward', 'Jecks', '53 Main Street', 'pjecks1@blogger.com', '585-624-7083', 'Russia', 'Tëploye');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (98, 'Brok', 'Mc Curlye', '3824 Blackbird Lane', 'bmccurlye0@is.gd', '826-863-5136', 'Sweden', 'Katrineholm');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (97, 'John', 'Gillbey', '338 Sunfield Center', 'ygillbey2@npr.org', '869-198-7138', 'Poland', 'Kamionka Wielka');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (96, 'Will', 'Smith', 'Abc house New York', 'afuge1@rediff.com', '494-246-3738', 'Russia', 'Dzhubga');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (94, 'Ahsan', 'Zameer', 'Abc house', 'ahsan@example.com', '123456789', 'Pakistan', 'Karachi');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (93, 'Conner', 'Krajcik', '91179 Jast Lane\nDesmondfort, KS 22709-7059', 'dbartoletti@example.net', '876.918.8132 x7459', 'Kenya', 'Guidoborough');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (92, 'Edwardo', 'Kling', '688 Araceli Roads\nNorth Charleyville, OR 91371-7211', 'tfarrell@example.org', '(445) 230-9112', 'Solomon Islands', 'Port Garnetville');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (91, 'Rosalinda', 'Lubowitz', '2124 Kemmer Drives\nWest Vernaland, MI 64378', 'fhettinger@example.org', '240.601.4749', 'Antigua and Barbuda', 'Lake Stewarttown');
INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (90, 'Albertha', 'Ritchie', '7611 Gleason Gateway\nEast Willie, MT 59038', 'mcglynn.flavie@example.org', '287-241-7595', 'Ethiopia', 'East Sophia');

INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (108, 'Mikey', 'Ginnelly', '49222 Farwell Way', 'mginnellyd@ucsd.edu', '557-640-0626', 'Brazil', 'Cupira');

INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (107, 'Easter', 'Prebble', '35 Sherman Alley', 'eprebble0@go.com', '718-202-0367', 'Indonesia', 'Sukasetia');

INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (106, 'Fionna', 'Danilyuk', '35 Sherman Alley', 'eprebble0@go.com', '718-202-0367', 'Indonesia', 'Sukasetia');

INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (103, 'Tony', 'Stark', 'zyx house', 'tony.stark@ironman.com', '1-8541-8597-12', 'USA', 'New York City');

INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (102, 'Aggi', 'Sandhill', '94896 Mallard Parkway', 'asandhillj@shutterfly.com', '110-327-1151', 'Latvia', 'Ape');

INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (101, 'Juditha', 'Francecione', '6301 Barby Court', 'jfrancecione3@ebay.com', '947-224-5435', 'Colombia', ' Caicedonia');

INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (99, 'Edward', 'Jecks', '53 Main Street', 'pjecks1@blogger.com', '585-624-7083', 'Russia', 'Tëploye');

INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (98, 'Brok', 'Mc Curlye', '3824 Blackbird Lane', 'bmccurlye0@is.gd', '826-863-5136', 'Sweden', 'Katrineholm');

INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (97, 'John', 'Gillbey', '338 Sunfield Center', 'ygillbey2@npr.org', '869-198-7138', 'Poland', 'Kamionka Wielka');

INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (96, 'Will', 'Smith', 'Abc house New York', 'afuge1@rediff.com', '494-246-3738', 'Russia', 'Dzhubga');

INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (94, 'Ahsan', 'Zameer', 'Abc house', 'ahsan@example.com', '123456789', 'Pakistan', 'Karachi');

INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (93, 'Conner', 'Krajcik', '91179 Jast Lane\nDesmondfort, KS 22709-7059', 'dbartoletti@example.net', '876.918.8132 x7459', 'Kenya', 'Guidoborough');

INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (92, 'Edwardo', 'Kling', '688 Araceli Roads\nNorth Charleyville, OR 91371-7211', 'tfarrell@example.org', '(445) 230-9112', 'Solomon Islands', 'Port Garnetville');

INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (91, 'Rosalinda', 'Lubowitz', '2124 Kemmer Drives\nWest Vernaland, MI 64378', 'fhettinger@example.org', '240.601.4749', 'Antigua and Barbuda', 'Lake Stewarttown');

INSERT INTO `customers` (`id`, `first_name`, `last_name`, `address`, `email`, `phone`, `country`, `city`) VALUES (90, 'Albertha', 'Ritchie', '7611 Gleason Gateway\nEast Willie, MT 59038', 'mcglynn.flavie@example.org', '287-241-7595', 'Ethiopia', 'East Sophia');

Database Connection:

<?php
try
{
	
	$dsn = 'mysql:dbname=demo;host=localhost';
	$dbUser = 'root';
	$password = '';

	$db = new PDO($dsn,$dbUser,$password);
	$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	
}
catch(PDOException $e)
{
	echo "PDO Error".$e->getMessage();
	die();
}
?>

<?php

try

{

$dsn = 'mysql:dbname=demo;host=localhost';

$dbUser = 'root';

$password = '';

$db = new PDO($dsn,$dbUser,$password);

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

}

catch(PDOException $e)

{

echo "PDO Error".$e->getMessage();

die();

}

Fetch Single Row:

<?php
$qry = "select * from customers";
$rs = $db->prepare($qry);
$rs->execute();

//fetch associative and index both
$rowsBoth 	= $rs->fetch();
print_r($rowsBoth);

//fetch as associative array
$rowsAssoc 	= $rs->fetch(PDO::FETCH_ASSOC);
print_r($rowsAssoc);

//fetch as object
$rowsObj 	= $rs->fetch(PDO::FETCH_OBJ);
print_r($rowsObj);

?>

<?php

$qry = "select * from customers";

$rs = $db->prepare($qry);

$rs->execute();

//fetch associative and index both

$rowsBoth = $rs->fetch();

print_r($rowsBoth);

//fetch as associative array

$rowsAssoc = $rs->fetch(PDO::FETCH_ASSOC);

print_r($rowsAssoc);

//fetch as object

$rowsObj = $rs->fetch(PDO::FETCH_OBJ);

print_r($rowsObj);

Select with Positional Placeholder ‘?’:

<?php
$qry = "select * from customers where first_name = ? and last_name= ?";
$statment = $db->prepare($qry);

$statment->bindParam(1,$firstName);
$statment->bindParam(2,$lastName);

$firstName = 'Mikey';
$lastName = 'Ginnelly';
$statment->execute();

$rowsAssoc 	= $statment->fetch(PDO::FETCH_ASSOC);

print_r($rowsAssoc);
?>

<?php

$qry = "select * from customers where first_name = ? and last_name= ?";

$statment = $db->prepare($qry);

$statment->bindParam(1,$firstName);

$statment->bindParam(2,$lastName);

$firstName = 'Mikey';

$lastName = 'Ginnelly';

$statment->execute();

$rowsAssoc = $statment->fetch(PDO::FETCH_ASSOC);

print_r($rowsAssoc);

Select with Name Place Holder:

<?php
$qry = "select * from customers where first_name = :fname and last_name=:lname";
$statement = $db->prepare($qry);

$statement->bindParam(':fname',$firstName);
$statement->bindParam(':lname',$lastName);

$firstName = 'Mikey';
$lastName = 'Ginnelly';
$statement->execute();

$rowsAssoc 	= $statement->fetch(PDO::FETCH_ASSOC);

print_r($rowsAssoc);
?>

<?php

$qry = "select * from customers where first_name = :fname and last_name=:lname";

$statement = $db->prepare($qry);

$statement->bindParam(':fname',$firstName);

$statement->bindParam(':lname',$lastName);

$firstName = 'Mikey';

$lastName = 'Ginnelly';

$statement->execute();

$rowsAssoc = $statement->fetch(PDO::FETCH_ASSOC);

print_r($rowsAssoc);

Select using LIKE Clause:

<?php
$qry = "select * from customers where first_name like :fname";
$statement = $db->prepare($qry);

$statement->bindParam(':fname',$firstName);


$firstName = 'A%';

$statement->execute();

$rowsAssoc 	= $statement->fetchAll(PDO::FETCH_ASSOC);

print_r($rowsAssoc);
?>

<?php

$qry = "select * from customers where first_name like :fname";

$statement = $db->prepare($qry);

$statement->bindParam(':fname',$firstName);

$firstName = 'A%';

$statement->execute();

$rowsAssoc = $statement->fetchAll(PDO::FETCH_ASSOC);

print_r($rowsAssoc);

Insert Single Record:

<?php 
$insertQry = "insert into customers (first_name, last_name, address, email, phone, country, city) values(:fname,:lname,:address,:email,:phoneno,:country,:city)";

$statement = $db->prepare($insertQry);

$statement->bindParam(':fname', $firstName);
$statement->bindParam(':lname', $lastName);
$statement->bindParam(':address', $address);
$statement->bindParam(':email', $email);
$statement->bindParam(':phoneno', $phone);
$statement->bindParam(':country', $country);
$statement->bindParam(':city', $city);


$firstName = 'Tony';
$lastName = 'Stark';
$address = 'zyx house';
$email = 'tony.stark@ironman.com';
$phone = '1-8541-8597-12';
$country = 'USA';
$city = 'New York City';

$statement->execute();

?>

<?php

$insertQry = "insert into customers (first_name, last_name, address, email, phone, country, city) values(:fname,:lname,:address,:email,:phoneno,:country,:city)";

$statement = $db->prepare($insertQry);

$statement->bindParam(':fname', $firstName);

$statement->bindParam(':lname', $lastName);

$statement->bindParam(':address', $address);

$statement->bindParam(':email', $email);

$statement->bindParam(':phoneno', $phone);

$statement->bindParam(':country', $country);

$statement->bindParam(':city', $city);

$firstName = 'Tony';

$lastName = 'Stark';

$address = 'zyx house';

$email = 'tony.stark@ironman.com';

$phone = '1-8541-8597-12';

$country = 'USA';

$city = 'New York City';

$statement->execute();

Insert Multiple Records with Inserted ID:

<?php 
$insertQry = "insert into customers (first_name, last_name, address, email, phone, country, city) values(:fname,:lname,:address,:email,:phoneno,:country,:city)";

$statement = $db->prepare($insertQry);

$statement->bindParam(':fname', $firstName);
$statement->bindParam(':lname', $lastName);
$statement->bindParam(':address', $address);
$statement->bindParam(':email', $email);
$statement->bindParam(':phoneno', $phone);
$statement->bindParam(':country', $country);
$statement->bindParam(':city', $city);


//Insert First
$firstName = 'Easter';
$lastName = 'Prebble';
$address = '35 Sherman Alley';
$email = 'eprebble0@go.com';
$phone = '718-202-0367';
$country = 'Indonesia';
$city = 'Sukasetia';

$statement->execute();

echo $db->lastInsertId();

//Insert Second
$firstName = 'Mikey';
$lastName = 'Ginnelly';
$address = '49222 Farwell Way';
$email = 'mginnellyd@ucsd.edu';
$phone = '557-640-0626';
$country = 'Brazil';
$city = 'Cupira';

$statement->execute();
echo $db->lastInsertId();


?>

<?php

$insertQry = "insert into customers (first_name, last_name, address, email, phone, country, city) values(:fname,:lname,:address,:email,:phoneno,:country,:city)";

$statement = $db->prepare($insertQry);

$statement->bindParam(':fname', $firstName);

$statement->bindParam(':lname', $lastName);

$statement->bindParam(':address', $address);

$statement->bindParam(':email', $email);

$statement->bindParam(':phoneno', $phone);

$statement->bindParam(':country', $country);

$statement->bindParam(':city', $city);

//Insert First

$firstName = 'Easter';

$lastName = 'Prebble';

$address = '35 Sherman Alley';

$email = 'eprebble0@go.com';

$phone = '718-202-0367';

$country = 'Indonesia';

$city = 'Sukasetia';

$statement->execute();

echo $db->lastInsertId();

//Insert Second

$firstName = 'Mikey';

$lastName = 'Ginnelly';

$address = '49222 Farwell Way';

$email = 'mginnellyd@ucsd.edu';

$phone = '557-640-0626';

$country = 'Brazil';

$city = 'Cupira';

$statement->execute();

echo $db->lastInsertId();

Update Record:

<?php
$updateQry = "update customers set first_name = :fname ,  last_name = :lname  where id = :id";
$updatestmt = $db->prepare($updateQry);

$updatestmt->bindParam(':fname',$firstName);
$updatestmt->bindParam(':lname',$lastName);
$updatestmt->bindParam(':id',$id);


$firstName = 'Fionna';
$lastName = 'Danilyuk';
$id = '99';

$updatestmt->execute();
?>

<?php

$updateQry = "update customers set first_name = :fname , last_name = :lname where id = :id";

$updatestmt = $db->prepare($updateQry);

$updatestmt->bindParam(':fname',$firstName);

$updatestmt->bindParam(':lname',$lastName);

$updatestmt->bindParam(':id',$id);

$firstName = 'Fionna';

$lastName = 'Danilyuk';

$id = '99';

$updatestmt->execute();

Delete Record:

<?php 
$deleteQry = "delete from customers where id = :id";

$deleteStmt = $db->prepare($deleteQry);

$deleteStmt->bindParam(':id',$id);


$id = '101';

$deleteStmt->execute();
?>

<?php

$deleteQry = "delete from customers where id = :id";

$deleteStmt = $db->prepare($deleteQry);

$deleteStmt->bindParam(':id',$id);

$id = '101';

$deleteStmt->execute();

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

What is Prepared Statement:

PHP PDO Prepared Statement Tutorial for Beginners with Example:

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:

Published by Ahsan Zameer

Leave a Reply Cancel reply