For those of us who find it hard to comprehend OOP PHP especially PDO. These few texts of mine would be great for you.

First, let us know What is PDO
PDO is a PHP Extension which basically stands for PHP Data Objects (PDO). With PDO developers can create applications that can connect across multiple database with little modification to their PHP code. When connecting to MySQL database PDO uses PDO MySQL driver for it's connections.

To know all the database that your PHP PDO server supports. simple run the following PHP code:

<?php
foreach(PDO::getAvailableDrivers() as $driver){
    echo $driver.'<br />';
}
?>

If you have been using using mysql_connect for connection to your database I advice you stop doing so and switch to this awesome driver called PDO which have been tested in terms of performance with other PHP drivers like MySQLi and MySQL and has been proven worthy.

Let us Look at some of the ways you can connect to your database using PDO

When I ran the code above in my localhost using WAMPSERVER 2.5 that has PHP 5.5.12 installed, only MySQL and SQLite were supported, so I will be doing some database connection using only this two database. But according to PHP, PDO supports several database ranging from

DBLIB: FreeTDS / Microsoft SQL Server / Sybase
Firebird (http://firebird.sourceforge.net/): Firebird/Interbase 6
IBM (IBM DB2)
INFORMIX - IBM Informix Dynamic Server
MYSQL (http://www.mysql.com/): MySQL 3.x/4.0
OCI (http://www.oracle.com): Oracle Call Interface
ODBC: ODBC v3 (IBM DB2 and unixODBC)
PGSQL (http://www.postgresql.org/): PostgreSQL
SQLITE (http://sqlite.org/): SQLite 3.x

So let's see the code for database connection to MySQL and SQLite on PDO

MySQL

<?php

/*** mysql hostname ***/
$hostname = 'localhost';
$username = 'username';
$password = 'password';
$dbname = 'myDB';

try {
    $conn= new PDO("mysql:host=$this->hostname;dbname=$this->dbname", $username, $password);
    /*** echo a message saying we have connected ***/
    echo 'Connected to database';
    }
catch(PDOException $e){
    echo $e->getMessage();
}
?>

You can see how Exception is being handled in case we encounter some unknown error while trying to connect to your database (Java and Android guys will understand this wella ). Exception might be handled when the server is quite overwhelmed.

$e->getMessage()

will display the error to your browser as a String.

Connection to SQLite DB

<?php
try {
    /*** connect to SQLite database ***/
    $dbh = new PDO("sqlite:/path/to/database.sdb";
    }
catch(PDOException $e){
    echo $e->getMessage();
}
?>

This simply connects to the path where your SQLite DB file is located in your local machine.

By doing all those up there, let's look at some of the PDO Queries

INSERT

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

try {
    $conn = new PDO("mysql:host=$this->servername;dbname=$this->dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "INSERT INTO myTableName (firstname, lastname, email)
    VALUES ('Joel', 'Eze', 'jl_eze@yahoo.co.uk')";
    // use exec() because no results are returned
    $conn->exec($sql);
    echo "New record created successfully";
    }
catch(PDOException $e){
    echo $sql . "<br>" . $e->getMessage();
}

$conn = null;
?>

From the code you can see it requires less code to perform an operation. In PDO to close a database connection is as simple as

$conn = null

on your connection variable

SELECT

SELECT statement in PDO is quite easy. Let say we have a table called "nl_programming_gurus"

CREATE TABLE nl_programming_gurus ( id MEDIUMINT( NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(25) NOT NULL,
description VARCHAR(255) NOT NULL 
) ENGINE = MYISAM ;

INSERT INTO `nl_programming_gurus` (`id`, `name`, `description`) VALUES
(1, 'dhtml18', 'Someone you run to for all your problems'),
(2, 'spikeC', 'Web Development Consultant'),
(3, 'ChinenyeN', 'The man the explain every nitty gritty in programming'),
(4, 'Javanian', 'This one na mods'),
(5, 'asamlipo', 'Always here to critize'),
(6, 'seunthomas', 'The man beefing dhtml18');

you can do your database SELECTING in PDO like this

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

try {
    $conn = new PDO("mysql:host=$this->servername;dbname=$this->dbname", $username, $password);
    
/*** echo a message saying we have connected ***/
    echo 'Connected to database<br />';

    /*** The SQL SELECT statement ***/
    $sql = "SELECT * FROM nl_programming_gurus";
    foreach ($conn->query($sql) as $row){
        print $row['name'] .' - '. $row['description'] . '<br />';
   }

    /*** close the database connection ***/
    $conn = null;
}
catch(PDOException $e){
    echo $e->getMessage();
}
?>

The following SELECT statement will give a result below

Connected to Database
dhtml18 - Someone you run to for all your problems
spikeC - Web Development Consultant
ChinenyeN - The man the explain every nitty gritty in programming
Javanian - This one na mods
asamlipo - Always here to critize
seunthomas - The man beefing dhtml18

You will have noticed that I can iterate over the result set directly with foreach. This is possible with PDO

So let's do the UPDATE STATEMENT

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

try {
    $conn = new PDO("mysql:host=$this->servername;dbname=$this->dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "UPDATE nl_programming_guru SET name='FrankLampard' WHERE id=2";

    // Prepare statement
    $stmt = $conn->prepare($sql);

    // execute the query
    $stmt->execute();

    // echo a message to say the UPDATE succeeded
    echo $stmt->rowCount() . " records UPDATED successfully";
    }
catch(PDOException $e){
    echo $sql . "<br>" . $e->getMessage();
}

$conn = null;
?>

The above code will UPDATE the second record with an id of 2 with FrankLampard

DELETE STATEMENT

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
    $conn = new PDO("mysql:host=$this->servername;dbname=$this->dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // sql to delete a record
    $sql = "DELETE FROM nl_programming_gurus WHERE id=2";

    // use exec() because no results are returned
    $conn->exec($sql);
    echo "Record deleted successfully";
    }
catch(PDOException $e){
    echo $sql . "<br>" . $e->getMessage();
}

$conn = null;
?>

The above code will delete the record in the database with an id of 2.

FETCH Modes
The section above showed how using PDO::query we can fetch information from the database. The PDO::query method returns a PDOStatement object that can be utilized in much the same was as mysql_fetch_object(). Of course there are times when an numerical index is needed or an associative index. PDO::query provides for this also by allowing the coder to set the fetch mode for via the PDOStatement object or via PDOStatement::setFetchMode().

I will list some of the FETCH Modes used in PDO.

FETCH ASSOC
To fetch an associative array from our results the constant PDO::FETCH_ASSOC is used and returns the column names as indexes or keys of the resulting array.
FETCH NUM
Like PDO::FETCH_ASSOC, the PDO::FETCH_NUM produces a numerical index of the result set rather than the field names.
FETCH BOTH
There may be times you need to fetch both numerical and associative indexes. PDO::FETCH_BOTH produces a numerical and associative index of the result set so you can use either, or both.

I know this texts is boring to some, but let just talk about one of the MOST IMPORTANCE aspect of PDO which is Prepared statements

Prepared statements basically compiles your SQL statements and performs query optimization on the SQL statement, and stores the result without executing it. It helps in preventing SQL Injection.

PDO accepts two kinds of parameter markers.
named - :name
question mark - ?
You must choose one or the other, they cannot be mixed.

Lets dive in and have a look at how PDO::prepare and PDOStatement::execute work together.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

try {
    $conn = new PDO("mysql:host=$this->servername;dbname=$this->dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // prepare sql and bind parameters
    $stmt = $conn->prepare("INSERT INTO nl_programming_gurus (name, description) 
    VALUES (:name, :description)";
    $stmt->bindParam(':name', $name);
    $stmt->bindParam(':description', $description);

    // insert a row
    $name= "Joel";
    $description= "Gentle Guy";
    $stmt->execute();

    $name= "dhtml18";
    $description= "Guru";
    $stmt->execute();

    echo "New records created successfully";
    }
catch(PDOException $e){
    echo "Error: " . $e->getMessage();
    }
$conn = null;
?>

PDO gives you some level of security, but you need to still sanitize your input and escape your output.

Lastly and finally, let's see how we can get our lastId when performing transactions
Getting Last Insert Id

This is a common task required when you need to get the id of the last INSERT. This is done with PDO::lastInserId() method as shown below.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
    $conn = new PDO("mysql:host=$this->servername;dbname=$this->dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "INSERT INTO nl_programming_gurus (name, description)
    VALUES ('FrankLampard, 'Gentle Guy, humble and jovial')";
    // use exec() because no results are returned
    $conn->exec($sql);
    $last_id = $conn->lastInsertId();
    echo "New record created successfully. Last inserted ID is: " . $last_id;
    }
catch(PDOException $e){
    echo $sql . "<br>" . $e->getMessage();
    }

$conn = null;
?>

The above code spinet will out the last Id transaction on your database.

That is it. PDO is a very nice wrapper/driver, for me I see it as a Framework on its own. Hope this series of texts would be helpful. Thank you.

If you feel I made any mistake feel free to correct me.

Thanks for taking out time to do this for all who want to transit from function-oriented (procedural) PHP programming to object-oriented. I have become very much accustomed to mysqli procedural. But please I need to know why switching to PDO for database operations is advantageous, apart from PDO's power to connect to other databases? Thank you.

ivcraig:
Thanks for taking out time to do this for all who want to transit from function-oriented (procedural) PHP programming to object-oriented. I have become very much accustomed to mysqli procedural. But please I need to know why switching to PDO for database operations is advantageous, apart from PDO's power to connect to other databases? Thank you.

Just like I said in the tutorial. PDO has been tested in terms of performance and has proved worthy.

Check this URL. This guys explained it all.

http://wooptoo.com/blog/pdo-vs-mysqli-performance-comparison/

PDO has prepare statement. which gives your app some level of security.

Or just read php's PDO manual at http://php.net/manual/en/book.pdo.php

This benchmark is flawed, as PDO does escaping/preparing entirely on the client-side, while MySQLi uses the mysql protocol for that (thus network IO).
The writer even agreed to it himself. Read the end of the article.

ivcraig:
This benchmark is flawed, as PDO does escaping/preparing entirely on the client-side, while MySQLi uses the mysql protocol for that (thus network IO).
The writer even agreed to it himself. Read the end of the article.

Where are you driving at?

FrankLampard:


Where are you driving at?

My point is PDO seems to have only one advantage, which is being able to connect to other databases.

Oga, FrankLampard, how about MS-MQL na?
And again, can you put me through how I can connect PHP to MS Access DB? XAMPP'S my development server.

ivcraig:

My point is PDO seems to have only one advantage, which is being able to connect to other databases.

No, No, No, Did you read that URL I posted earlier. PDO outwit MySQLi in term of performance. We can't even talk of MySQL because that one is depreciated.

PDO::PreparedStatement gives your application some level of security.

There was a small App I built with PDO recently, I tested for SQL injection and it passed the test. Without even writing any SQL escape functions.

WARNING: YOU HAVE TO STOP USING PROCEDURAL PHP, BECAUSE PHP 7 DOES NOT HAVE SUPPORT FOR IT SHA SHA

paranorman:
Oga, FrankLampard, how about MS-MQL na?
And again, can you put me through how I can connect PHP to MS Access DB? XAMPP'S my development server.

Have you ran the first code spinet in my article to check if your current XAMP server have support for MS Access DB, because when I did it in my my WAMP server only MySQL and SQLite was supported, that was why I didn't show you guys the connection code spinet for database connection, but according to PHP, he is the code spinet for connection to MS Access Database.

NB: I didn't test this code whatsoever.

<? php
$ dbName = \path\to\dbname.mdb" ;
if ( ! file _ exists( $ dbName) ) {
die ( " Could not find database file . " ) ;
}
$ db = new PDO ( "odbc : DRIVER ={ Microsoft Access Driver (* . mdb )}; DBQ = $ dbName; Uid =; Pwd =;" ) ;
?>

FrankLampard:


Have you ran the first code spinet in my article to check if your current XAMP server have support for MS Access DB, because when I did it in my my WAMP server only MySQL and SQLite was supported, that was why I didn't show you guys the connection code spinet for database connection, but according to PHP, he is the code spinet for connection to MS Access Database.

NB: I didn't test this code whatsoever.

thanks. What if it does not support it, how can I go about it? Isn't this a driver thing?

paranorman:

thanks. What if it does not support it, how can I go about it? Isn't this a driver thing?

You have to go to your php.ini file to enable it, if it is not supported by default.

FrankLampard:


You have to go to your php.ini file to enable it, if it is not supported by default.

thanks, I already did that.

paranorman:
thanks, I already did that.

Looking forward to seeing how you used PDO in developing Enterprise Solutions.

FrankLampard:


Looking forward to seeing how you used PDO in developing Enterprise Solutions.

will get there, someday. So, what lang(s) are you a pro in?

paranorman:

will get there, someday. So, what lang(s) are you a pro in?

My first love is Java, recently started doing PHP.

FrankLampard:


No, No, No, Did you read that URL I posted earlier. PDO outwit MySQLi in term of performance. We can't even talk of MySQL because that one is depreciated.

PDO::PreparedStatement gives your application some level of security.

There was a small App I built with PDO recently, I tested for SQL injection and it passed the test. Without even writing any SQL escape functions.

WARNING: YOU HAVE TO STOP USING PROCEDURAL PHP, BECAUSE PHP 7 DOES NOT HAVE SUPPORT FOR IT SHA SHA

The author in the article you sent me said his test result is wrong. I suggest u also read it again.

ivcraig:

The author in the article you sent me said his test result is wrong. I suggest u also read it again.

Digest the first paragraph first. What he meant was that MySQLi was supposed to be better than PDO, but using his benchmark test PDO surprisingly outwit MySQLi.

thanks bro. really appreciate the tutorials. God bless

nnamdiosu:
thanks bro. really appreciate the tutorials. God bless

Thanks

The conclusion of the writer after the test.

^^^

It seems you have made up your mind on the procedural route. Even tho I have explain this countless number of times.