PDO in PHP - Ebhor.com

What is PDO?

Other than procedural methods like MySQLi, PHP also provides an object oriented approach to deal with databases. This framework is widely known as PDO: PHP Data Objects.

Advantages of PDO

PDO is applicable on 12 different databases whereas MySQLi is not, which makes PDO more flexible than typical procedural approach.
PDO also provides an exception class for handling problems during database query execution. The exception-prone code must be written in the try block. When an exception is thrown within the try{ } block, execution stops at that line and jumps directly to the first catch(){ } block.
PDO provides a set of methods on the PDO object for performing queries, which introduces reusability of code.

It’s useful for developing software that may deploy in environments containing different databases available without having explicit support to each database, also for using a database connection with little or no knowledge of the database.
mysqli doesn’t allow you to handle exception. The function die(); will just stop executing the script suddenly and then show the error to the screen to the user which is NOT desirable & allowing hackers to discover your schema.

PDO provides three error handling modes:

PDO::ERRMODE_SILENT acts like mysqli where you have to check each result and then look at $db->getMessage(); to get the error details.
PDO::ERRMODE_WARNING throws PHP Warnings on the output screen.
PDO::ERRMODE_EXCEPTION throws PDOException. This is the best mode use. It acts much like or die(mysql_error()); when it isn’t caught, but unlike die() the PDOException can be caught and handled if you want to do so.

Limitation of PDO

The Limitation is that you lose direct control of your queries, which can make complex queries difficult to write or move a lot of operations off the database and into PHP, requiring larger results thereby reducing code and database performance.

PDO Set Up

For installation procedure, visit: http://php.net/manual/en/pdo.installation.php

Examples

Database Connection Establishment using PDO

Here is an example using PDO:

<?php
$servername = "localhost ";
$username = "username";
$password = "password";
try
	{
	$con = newPDO("mysql:host=$servername;databasename=DB_database", $username, $password);

	// provide an existing database to avoid exception

	$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	echo "Connection Created successfully!";
	catch(PDOException $e)
		{
		echo "Connection failed: " . $e->getMessage();
		}

?>

<?php

$servername = "localhost ";

$username = "username";

$password = "password";

try

{

$con = newPDO("mysql:host=$servername;databasename=DB_database", $username, $password);

// provide an existing database to avoid exception

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

echo "Connection Created successfully!";

catch(PDOException $e)

{

echo "Connection failed: " . $e->getMessage();

}

Run Query Without variables

For simple queries without PHP variable parameters, use the following PDO code:

<?php
$affected_rows = $db->exec("UPDATE table_name SET field_name='value'");
echo $affected_rows . ' are affected';
?>

<?php

$affected_rows = $db->exec("UPDATE table_name SET field_name='value'");

echo $affected_rows . ' are affected';

Just change query for the simple DELETE, and INSERT statements within the above written code.

Run Query With PHP Variables

For statements that take PHP variable parameters, MySQLi uses a long tiring process to perform the task whereas PDO offers you an easier process using method calling. The example for SELECT statement is as follows:

<?php
$qry = $db->prepare("SELECT * FROM table_name_Ebhor WHERE fld_id=? AND fld_name=?");
$qry->execute(array(
	$variable_id,
	$variable_name
));
$rows = $qry->fetchAll(PDO::FETCH_ASSOC);
?>

<?php

$qry = $db->prepare("SELECT * FROM table_name_Ebhor WHERE fld_id=? AND fld_name=?");

$qry->execute(array(

$variable_id,

$variable_name

));

$rows = $qry->fetchAll(PDO::FETCH_ASSOC);

Here we have performed three steps as follows:

Prepare: A valid query template is created and sent to the database where some values are unspecified (labelled as “?”).
Then the database parses, compiles, performs query optimization on the query template, and also stores the query result without executing it.
Execute: Later on, the application binds the values to the parameters, and the database executes the statement in lesser time using execute. The application may execute the prepared query as many times as the user wants with different values as per the requirements. It is a safer solution than concatenating strings together using MySQLi approach.

N.B.: While binding parameters, never put quotes around the ‘?’ placeholders. It will cause SQL syntax errors because quotes aren’t needed to be known while preparing the query.