PDO – PHP DATA OBJECTS Fundamentals

Posted 12:27 pm by muni & filed under MYSQL, PHP.

In this tutorial we are going to see about PDO – PHP DATA OBJECTS fundamentals. PDO is uniform database access libary, which provides uniform methods (functions) to work with differnet databases without rewriting scripts while switching the database.

“PDO is uniform database access libary, which provides uniform methods (functions) to work with differnet databases”

To explain PDO concepts I am using following users table

CREATE TABLE IF NOT EXISTS `login`.`users` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(50) NULL DEFAULT NULL,
  `last_name` VARCHAR(50) NULL DEFAULT NULL,
  `email` VARCHAR(60) NOT NULL,
  `password` VARCHAR(60) NOT NULL,
  `oauth_provider` VARCHAR(50) NULL DEFAULT NULL,
  `oauth_uid` VARCHAR(150) NULL DEFAULT NULL,
  `oauth_token` VARCHAR(150) NULL DEFAULT NULL,
  `oauth_secret` VARCHAR(150) NULL DEFAULT NULL,
  `username` VARCHAR(75) NULL DEFAULT NULL,
  `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

If you are using MySQL or MySQLi extension to access MySQL database, then later on if you want to switch to different database like SQLite or PostgreSQL. Then we have to completely rewrite the script the way we are accessing database connection and the data. Where as PDO (PHP DATA OBJECTS) let us write single php script that work with different databases.

$con = mysqli_connect("localhost","root","","login");
if (mysqli_connect_errno()){
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
//sqlite
$dbh =  SQLite3::open($db);
//PostgreSQL
$dbconn = pg_connect("host=localhost dbname=login user=root password=")
    or die('Could not connect: ' . pg_last_error());
/************PDO **********************/
$host = 'localhost';
$dbname = 'login';
$user = 'root';
$pass = '';
// For MySQL:
$conn = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
// For SQLite:
$conn = new PDO("sqlite:$db");
// And for PostgreSQL:
$conn = new PDO("pgsql:host=$host dbname=$db", $user, $pass);

In the above image you can see for different database, we used different functions used to create database connection, where as in PDO, we only have to change connection string.

Advantages :

• 1. Using PDO we can write database neutral script that work across multiple database. This help us switching database painless process and reduces our timings greatly.
• 2. PDO comes with prepared statements, so we don’t need to convert user submitted data into database safe characters using some of the functions (mysqli_real_escape_string(), sqlite_escape_string() or pg_escape_string() ) for different database drivers. But whereas prepared statements itself will take care of escaping user submitted data into database safe characters. That effectively prevents SQL Injection attacks and improves the performance.

  
  $make = 'Ford';
  // MySQL:
  $m = mysqli_real_escape_string($con, $make);
  $q = mysql_query("SELECT sum(price) FROM cars WHERE make='$m'");
  // SQLite:
  $m = sqlite_escape_string($make);
  $q = sqlite_query("SELECT sum(price) FROM cars WHERE make='$m'",
  $dbh);
  // and PostgreSQL:
  $m = pg_escape_string($make);
  $q = pg_query("SELECT sum(price) FROM cars WHERE make='$m'");
  
  //PDO for script for all Databases
  $sql = "SELECT sum(price) FROM cars WHERE make=?";
  $stmt = $conn->prepare($sql);
  $stmt->execute(array($m));
  
  
  

• 3. Prepared statements prepares the SQL query first then it runs the SQL query against database to insert/update array of values into database. So here Prepared statements will act as template for SQL insert/update queries. This will reduces syntax checking time and improves query executing timing with different values

  Where as in conventional database driver (MySQL, SQLlite), every time you insert/update values into database SQL queries prepared and do the insert/update operation. This will take more time on SQL syntax checking.

  
  $users = array(
  	array(
  		'first_name' => 'Muni',
  		'last_name' => 'Ayothi',
  		'email' => 'muni@smartutorials.net',
  		'password' => '234235345'
  	),
  	array(
  		'first_name' => 'Sasi',
  		'last_name' => 'Ayothi',
  		'email' => 'sasi@smartutorials.net',
  		'password' => '234235345'
  	)
  );
  
  

  
  //MySQLi
  foreach($users as $user)
  {
  	$conn->query('INSERT INTO users(first_name, last_name, email, password) VALUES(' .$conn->quote($author['first_name']) .
  		',' . $conn->quote($author['last_name']) .
  		',' . $conn->quote($author['email']) .
  		',' . $conn->quote($author['password'])')' .);
  }
  
  

  You can see in the above SQL query for every insert we are preparing SQL queries and doing insert operation. So for every insert it check syntax of the prepared SQL query.

  Whereas in PDO, you see in the below scripts, SQL query is prepared only one time at first, then we execute same SQL query for array of values.

  
  //PDO
  
  $stmt = $conn->prepare('INSERT INTO authors(first_name, last_name, email, password) VALUES(?, ?, ?, ?)');
  foreach($users as $user)
  {
       $stmt->execute( array($user['first_name'], $user['last_name'], $user['email'], $user['password']));
  }
  
  

• 4. PDO throws catcheable exceptions, so we can handle the errors more effectively.

try
{
	$conn = new PDO('mysql:host=localhost;dbname=login', 'root', '');
	$q = $conn->query('SELECT * FROM users');
	while($r = $q->fetch())
	{
	...
	}
}
catch(PDOException $pdoe)
{
	die('Database error: ' . $pdoe->getMessage());
}
catch(Exception $e)
{
	die('Unexpected error: ' . $e->getMessage());
}

Where as in database specific drivers (MYSQLi or SQLlite), We have to check for an error at every line before proceeding to next lines.

$conn = mysqli_connect("localhost","root","","login");
if (mysqli_connect_errno())
{
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$result = mysqli_query($conn, 'SELECT * FROM users') or die(mysqli_error($conn));
while($row = mysqli_fetch_assoc($result)) {

}

4. PDO is objected oriented.

Connect MySQL Database in PHP via PDO

To create connection to MYSQL database, we have to instantiate PDO class object by supplying connecting strings. Where connection string consists of driver name, database host and database name. Upon successful connection to the MySQL database, it will return instance PDO object to our PHP script.

<?php
try {
    $conn = new PDO('mysql:host=localhost;dbname=test', $user, $pass, array(
      PDO::ATTR_PERSISTENT => true
    ));
    $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
} catch (PDOException $e) {
    print "Error!: " . $e->getMessage() . "<br/>";
    die();
}

If PDO come across any connection error, then it will throws PDOException so we will catch that PDOException and show proper Exception message to user.

While making new connection to database we are supplying PDO::ATTR_PERSISTENT as final parameter. Which caching the database connections, so connections can be reused if the request comes with same credentials from other scripts. This improves application performance greatly.

PDO ERRMODE Attribute and Exceptions Handling :

Default PDO::ATTR_ERRMODE Attribute is PDO::ERRMODE_SILENT. So if any errors happen, then exceptions will not thrown. So we have to manually call the following functions to know more about error details PDO::errorInfo(), PDO::errorCode(), PDOStatement::errorInfo(), or PDOStatement::errorCode().

So if we want write efficient Exception handling application, then we have to enable PDO Exception. So it throws Exception if it encounters any errors. Following script enables PDO exception.

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

PDO has following three ERRMODE Attribute

$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT );
$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );
$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

PDO::ERRMODE_WARNING

If you set as PDO ErrorMode as PDO::ERRMODE_WARNING, then If PDO encounters any error then it throws normal PHP warning without affecting normal flow of PHP execution.

PDO::ERRMODE_EXCEPTION

If you set PDO ErrorMode as PDO::ERRMODE_EXCEPTION, then PDO encounters any error it will throw as an Exception. In catch block we will catch the Exceptions. Where we can either log the error details or mail the error details to admin.

CRUD Operation Using PDO

Now we are going to see how we gonna create, read, update and delete operations using PDO without prepared statements.

$conn->query( 'INSERT INTO users(first_name, last_name, email, password) VALUES(' . $conn->quote($author['first_name']) .
	',' . $conn->quote($author['last_name']) .
	',' . $conn->quote($author['email'])')' .
	',' . $conn->quote($author['password'])')' .
);

PDO Update

$conn->query("UPDATE users SET first_name=" .$conn->quote($_POST['first_name']) .
	', last_name=' . $conn->quote($_POST['last_name']) .
	" WHERE id=$user[id]");

PDO Delete

$conn->query("DELETE FROM users WHERE id=$user[id]");

PDO Select

$conn->query("SELECT users FROM borrowers WHERE id=$user[id]");

PDO Prepared Statements

1. Positional Placeholders
2. Named Placeholders

Positional Placeholders – Prepared Statements

In the below SQL query, the question marks indicates the position of values in the prepared statements, that’s why these question marks are called positional placeholders.
While using Positional Placeholders we need to take extra care on order of values we are passing to the PDOStatement::execute() method. If number of values are more, then it’s very hard to track the values and it’s positions.

$stmt = $conn->prepare('INSERT INTO users(first_name, last_name, email, password) VALUES(?, ?, ?, ?)');
foreach($users as $user)
{
	$stmt->execute( array($user['first_name'], $user['last_name'], $user['email'], $user['password']));
}

Named Placeholders – Prepared Statements

In Named Placeholder instead of question mark, we are giving descriptive name preceded by a colon.

$stmt = $conn->prepare( 'INSERT INTO users(first_name, last_name, email, password) VALUES(:first_name, :last_name, :email, :password)');
foreach($users as $user)
{
	$stmt->execute(
	array(
		':first_name' => $user['firstName'],
		':last_name' => $user['lastName'],
		':email' => $user['email']),
		':password' => $user['password'])
	);
}

In Named Placeholder we can pass values to the PDOStatement::execute() method, in which ever order we like to pass until it matches placeholder names.

In next tutorial I am going to completely rewriting the User registration and Login Using PHP5, MySQLi, jQuery and Bootstrap tutorial using PDO.