Posted on by

PHP PDO Tutorial for Beginners with Example – Part 1

I got lots of request from php beginners to cover PHP PDO with examples in my tutorial. So here it is guys.

Before jumping into the post I just want to tell you that I have divided PHP PDO tutorial in 2 parts. One is basics part (part 1) and in second part (part 2) I will cover PHP PDO Prepared Statement.

So this is basics part. Let’s start.

Also Read:

What is PDO:

PHP Data Object (PDO) is a light weight, consistent and object oriented interface to use database. It provides a data-access abstraction layer, means that regardless of which database you are using, you just use same function to fetch and insert record in the database.

Right now PDO supports 12 databases including MYSQL, MS SQL SERVER, IBM, ORACLE etc. If you want read more about databases visit PDO Drivers.


Why use PHP PDO:

There are many reasons to use PDO as:

  • Security/ Prepared Statements: PHP PDO supports prepared statements with positional and name based place holder which prevents application from SQL Injection.
  • Multiple databases: Multiple database support is ease when you are going to change database to one another.
  • Excellent error handling: It offers you a choice of 3 different error handling strategies, to fit your style of application development.

Now you know about PHP PDO and why to use it. So now lets take a look at PDO functions and then will see how they work. I will use $db as PDO object in functions list.

Functions/Properties Description
$db = new PDO($dsn,$dbUser,$password) Creates a PDO connection to the database. It will take 3 arguments. $dsn is a Data Source Name that contains the information required to the database. $dbUser is a a user name of DSN. In some PDO drivers this parameter is optional.The password for the DSN. This parameter is also optional in some database.
$db->setAttribute($attribute, $value) Set an attribute on the database. There are many attribute you can set. Visit PDO::setAttribute for further information.
$db->query($qry) Executes an SQL statement, returning a result set as a PDOStatement object.
$db->exec($qry) Execute an SQL statement and return the number of affected rows
$db->lastInsertId() Returns the ID of the last inserted row or sequence value
fetch($fetch_style, $cursor_orientation) Fetches the next row from a result set. $fetch_style control how the next row will be returned to the caller. Some of the common fetch style are PDO::FETCH_ASSOC, PDO::FETCH_OBJ and PDO::FETCH_BOTH
fetchObject($class_name, $ctor_args) Fetches the next row and returns it as an object. $class_name is the name of the created class. $ctor_args is an array which elements are passed to the constructor
fetchAll($fetch_style, $fetch_argument, $ctor_args ) Returns an array containing all of the result set rows. $fetch_style controls the content of the returned array as documented in fetch. $fetch_argument has a different meaning depending on the value of fetch_style. $ctor_args is arguments of custom class constructor when the fetch_style parameter is PDO::FETCH_CLASS

PHP PDO Tutorial for Beginners with Example

I will use demo database in which there is a customers table.

Create Database:

1
2
3
 
create database demo;
 

Create MySQL Table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
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');
 

PDO Connection:

For database connectivity PDO requires 3 parameters that are DSN (Data Source Name), database user and database password. DSN has database driver with database name with host. setAttribute method is used to set attribute on the database handle.

Simple PDO Connection:

1
2
3
4
5
6
7
8
9
10
 
<?php
$dsn = 'mysql:dbname=demo;host=localhost';
$dbUser = 'root';
$password = '';
 
$db = new PDO($dsn,$dbUser,$password);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
?>
 

PDO Connection using try and Catch (Recommended):

Another and recommended way to create database connection is to put database connection in try and catch block.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
<?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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
<?php
 
$qry = "select * from customers";
$rs = $db->query($qry);
 
//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);
?>
 

If you print all three at a same time, you will get result in ascending order.

Fetch an Object:

1
2
3
4
5
6
7
8
9
 
<?php
$qry = "select * from customers";
$rs = $db->query($qry);
 
$fetchObject = $rs->fetchObject();
print_r($fetchObject);
?>
 

Fetch all Records:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
<?php
$qry = "select * from customers";
$rs = $db->query($qry);
 
//Fetch all records as both an associative and index array
$fetchAll = $rs->fetchAll();
print_r($fetchAll);
 
 
//Fetch all records as an associative array
$fetchAll = $rs->fetchAll(PDO::FETCH_ASSOC);
print_r($fetchAll);
 
 
//Fetch All Object
$fetchAll = $rs->fetchAll(PDO::FETCH_OBJ);
print_r($fetchAll);
 
?>
 

Insert Record:

1
2
3
4
5
6
7
8
 
<?php
$insertQry = "insert into customers (first_name, last_name, address, email, phone, country, city) values('Ahsan','Zameer','abc house Karachi','ahsan@wdb24.com','1234567890','Pakistan','Karachi')";
 
$db->query($insertQry);
 
?>
 

Insert Record and get inserted id:

1
2
3
4
5
6
7
8
9
 
<?php
$insertQry = "insert into customers (first_name, last_name, address, email, phone, country, city) values('Ahsan','Zameer','abc house Karachi','ahsan@abc.com','1234567890','Pakistan','Karachi')";
 
$db->query($insertQry);
 
echo $db->lastInsertId();
?>
 


Update Record:

1
2
3
4
5
6
7
8
 
<?php
$updateQry = "update customers set last_name = 'Zameer Khan' where id = 101";
 
$db->query($updateQry);
 
?>
 

Delete Record:

1
2
3
4
5
6
7
8
9
 
<?php
 
$deleteQry = "delete from customers where id = 102";
 
$db->query($deleteQry);
 
?>
 

 

More from WDB24.com

Posted in PHP

Published by 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.

Leave a Reply

Your email address will not be published. Required fields are marked *