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:

• PHP Encode and Decode Mysql Data into JSON
• PHP MYSQLi Object Oriented Tutorial for Beginners
• PHP CURL tutorial with Examples

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:

Create MySQL Table:

Insert Records in MYSQL Table:

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:

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.

Fetch Single Row:

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

Fetch an Object:

Fetch all Records:

Insert Record:

Insert Record and get inserted id:

Update Record:

Delete Record: