PDO (PHP Data Objects)

PDO (PHP Data Objects) is a PHP extension through which we can access and work with databases. Though PDO is similar in many aspects to mySQLi, it is better to work with for the following reasons:

•  It is better protected against hackers.
• It is consistent across databases, so it can work with MySQL as well as other types of databases (SQLite, Oracle, PostgreSQL, etc.)
•  It is object oriented at its core.

PDO Support 12 diffrent database drivers.

All of these drivers are not necessarily available on your system , you can find list ob available in your system by using this command 

print_r(PDO::getAvailableDrivers()); 

In this PDO  tutorial you will find recipes for 4 basic functions that we perform with the database: insertion, selection, update, and deletion. The recipes are intended to work with MySQL, but we can easily switch it with another database.

How to connect with the database through PDO?

It is considered good practice to wrap the database connection within a try-catch block so that, if anything goes wrong, an exception will be thrown. We can customize the error message but, in order to keep things simple, we’ll settle with the error message that PDO provides.

In order to connect to the database, we’ll need the database name, username, and password.

// DB credentials.
define('DB_HOST','localhost');
define('DB_USER','your user name');
define('DB_PASS','your user password');
define('DB_NAME','your database name');
// Establish database connection.
try
{
$dbh = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME,
DB_USER, DB_PASS,
array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
}
catch (PDOException $e)
{
exit("Error: " . $e->getMessage());
}

How to close the database connection?

PHP automatically closes the database connection but, if the need arises, we can deliberately close the connection with the following line of code:

$dbh = null; 

The SQL code for the users table:

CREATE TABLE IF NOT EXISTS users (id int(11) NOT NULL AUTO_INCREMENT,
name varchar(60) DEFAULT NULL,
phone varchar(12) DEFAULT NULL,
city varchar(60) DEFAULT NULL,
date_added date DEFAULT NULL,
PRIMARY KEY (id)
)

1) Write a regular SQL query but, instead of values, put named placeholders. For example:

$sql = "INSERT INTO `users`(`name`, `phone`, `city`, `date_added`) VALUES(:name,:phone,:city,:date)"; 

The use of placeholders is known as prepared statements. We use prepared statements as templates that we can fill later on with actual values.

2) Prepare the query

$query = $dbh -> prepare($sql); 

3) Bind the placeholders to the variables:

$query->bindParam(':name',$name); 

You can add a third parameter which filters the data before it reaches the database:

$query->bindParam(':name',$name,PDO::PARAM_STR);
$query->bindParam(':phone',$phone,PDO::PARAM_INT);
$query->bindParam(':city',$city,PDO::PARAM_STR);
$query->bindParam(':date',$date,PDO::PARAM_STR);

•  PDO::PARAM_STR is used for strings.
•  PDO::PARAM_INT is used for integers.
•  PDO::PARAM_BOOL allows only boolean (true/false) values.
•  PDO::PARAM_NULL allows only NULL datatype.

4) Assign the values to the variables.

$name = "Anuj kumar";
$phone = "999857868";
$city = "New Delhi";
$date = date('Y-m-d');

5) Execute the query:

$query -> execute(); 

6) Check that the insertion really worked:

$lastInsertId = $dbh->lastInsertId();
if($lastInsertId>0)
{
echo "OK";
}
else
{
echo "not OK";
}

If the last inserted id is greater than zero, the insertion worked.

All code together now:

<?php 
$sql = "INSERT INTO `users` (`name`, `phone`, `city`, `date_added`) VALUES(:name,:phone,:city,:date)";
$query = $dbh -> prepare($sql);
$query->bindParam(':name',$name,PDO::PARAM_STR);
$query->bindParam(':phone',$phone,PDO::PARAM_INT);
$query->bindParam(':city',$city,PDO::PARAM_STR);
$query->bindParam(':date',$date);
// Insert the first row
$name = "Anuj";
$phone = "1231234567";
$city = "New Delhi";
$date = date('Y-m-d');
$query -> execute();
$lastInsertId = $dbh->lastInsertId();
if($lastInsertId>0)
{
echo "OK";
 }
else {
echo "not OK"; 
}
?>

Reading data from the database is not so different than inserting data, with steps 1 to 5 being almost identical while the sixth step is different.

1) Write the regular select statement and again, instead of values, put named placeholders. For example:

$sql = "SELECT * FROM users"; 

2) Prepare the query:

$query = $dbh -> prepare($sql); 

3) Execute the query:

$query -> execute(); 

4) Assign the data which you pulled from the database (in the preceding step) to a variable.

$results = $query -> fetchAll(PDO::FETCH_OBJ); 

Here I used the parameterPDO::FETCH_OBJ  that returns the fetched data as an object. If you’d like to fetch the data in the form of an array, use: PDO::FETCH_ASSOC . 5) Make sure that you were able to retrieve the data from the database, by counting the number of records.

if($query -> rowCount() > 0){} 

6) In case that the query returned at least one record, we can echo the records within a foreach loop:

if($query -> rowCount() > 0)
{
foreach($results as $result)
{
echo $result -> name . ", ";
echo $result -> city . ", ";
echo $result -> date_added;
}
}

All code together now :

<?php
$sql = "SELECT * FROM users WHERE city = :city";
$query = $dbh -> prepare($sql);
$query -> bindParam(':city', $city, PDO::PARAM_STR);
$city = "New York";
$query -> execute();
$results = $query -> fetchAll(PDO::FETCH_OBJ);
if($query -> rowCount() > 0)
{
foreach($results as $result)
{
echo $result -> name . ", ";
echo $result -> city . ", ";
echo $result -> date_added;
}
}
?>

1) Write the regular update statement and again, instead of values, assign the named placeholders. For example:

$sql = "UPDATE `users` SET `city`= :city, `phone` = :tel WHERE `id` = :id";

2) Prepare the query:

$query = $dbh->prepare($sql); 

3) Bind the parameters:

$query -> bindParam(':city', $city, PDO::PARAM_STR);
$query -> bindParam(':tel' , $tel , PDO::PARAM_INT);
$query -> bindParam(':id' , $id , PDO::PARAM_INT);

4) Define the bound values:

$tel = '06901234567';
$city = 'New Delhi';
$id = 1;

5) Execute the query:

$query -> execute(); 

6) Check that the query has been performed and that the database has been successfully updated.

<?php 
if($query -> rowCount() > 0)
{
$count = $query -> rowCount();
echo $count . " rows were affected.";
}
else
{
echo "No affected rows.";
}
All together now:
$sql = "UPDATE users
SET `city`= :city, `phone` = :tel
WHERE `id` = :id";
$query = $dbh->prepare($sql);
$query -> bindParam(':city', $city, PDO::PARAM_STR);
$query -> bindParam(':tel' , $tel , PDO::PARAM_INT);
$query -> bindParam(':id' , $id , PDO::PARAM_INT);
$tel = '02012345678';
$city = 'London';
$id = 1;
$query -> execute();
if($query -> rowCount() > 0)
{
$count = $query -> rowCount();
echo $count . " rows were affected.";
}
else
{
echo "No affected rows.";
} ?>

1) Write the delete statement:

$sql = "DELETE FROM `users` WHERE `id`=:id"; 

2) Prepare the query:

$query = $dbh -> prepare($sql); 

3) Bind the parameters:

$query -> bindParam(':id', $id, PDO::PARAM_INT); 

4) Define the bound values:

$id = 1; 

5) Execute the query:

$query -> execute(); 

6) Check that the query has been performed and that the records have been successfully deleted from the database.

if($query -> rowCount() > 0)
{
$count = $query -> rowCount();
echo $count . " rows were affected.";
}
else
{
echo "No affected rows.";
}

All code together now:

<?php $sql = "DELETE FROM `users` WHERE `id`=:id";
$query = $dbh -> prepare($sql);
$query -> bindParam(':id', $id, PDO::PARAM_INT);
$id = 1;
$query -> execute();
if($query -> rowCount() > 0)
{
$count = $query -> rowCount();
echo $count . " rows were affected.";
}
else
{
echo "No affected rows.";
}
?>