An Introduction To PHP Data Objects (PDO)

An Introduction To PHP Data Objects

D

atabases are everywhere and there is no practical PHP application that could exist without a database. From the very beginning, PHP offers several ways of interfacing with all popular DBMS. For instance, two popular ways of interfacing with MySQL based databases are mysql and mysqli.

Over years, databases have come a long way and now several different vendors offer popular DBMS that power modern PHP apps. To standardize and streamline development practices, PHP introduced PHP Data Objects (PDO) in PHP 5.1

PDO is a database access layer which provides a fast and consistent interface for accessing and managing databases in PHP applications.  Every DBMS has specific PDO driver(s) that must be installed when you are using PDO in PHP applications.

Supported Databases

Driver name	Supported Database
PDO_CUBRID	Cubrid
PDO_DBLIB	FreeTDS / Microsoft SQL Server / Sybase
PDO_FIREBIRD	Firebird
PDO_IBM	IBM DB2
PDO_INFORMIX	IBM Informix Dynamic Server
PDO_MYSQL	MySQL 3.x/4.x/5.x
PDO_OCI	Oracle Call Interface
PDO_ODBC	ODBC v3 (IBM DB2, unixODBC and win32 ODBC)
PDO_PGSQL	PostgreSQL
PDO_SQLITE	SQLite 3 and SQLite 2
PDO_SQLSRV	Microsoft SQL Server / SQL Azure
PDO_4D	4D

By default, PHP has PDO_SQLite driver installed. However, if you wish to work with other databases, you must first install the relevant driver.

in order to check what drivers are installed on your system, create a new PHP file and add the following code snippet to it:

1 2 3 4 5

<?php   print_r(PDO::getAvailableDrivers());   ?>

Working With PDO

PDO replaces all previous database interaction approaches. Using PDO, you could easily perform CRUD and related DBMS operations. In effect, PDO acts as a layer that separates database related operations from the rest of the code.

Connectivity

One of the most important benefits of PDO is the simple and very straightforward database connectivity. Consider the following code snippet that is used to set up connections with the database. Note that when the underlying DBMS changes, the only change that you need to make is the database type.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

<?php   Class Connection {   private  $server = "mysql:host=localhost;dbname=cloudways";   private  $user = "root";   private  $pass = "";   private $options  = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,);   protected $con;            public function openConnection()              {                  try                    {           $this->con = new PDO($this->server, $this->user,$this->pass,$this->options);           return $this->con;                     }                  catch (PDOException $e)                    {                        echo "There is some problem in connection: " . $e->getMessage();                    }              }   public function closeConnection() {      $this->con = null;   }   }   ?>

In the above code snippet, notice that the DBMS is MySQL. However, if the DBMS changes to MS SQL Server, the only change will be the replacement of mysql with mssql.

Note: PDO can handle exceptions. Therefore, always wrap its operation in a try and catch block.

Creating a Table With PDO

In order to create a table, first declare a query string and then execute it with exec function as no data will be returned.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

<?php   include_once 'connection.php';   try   {        $database = new Connection();        $db = $database->openConnection();        // sql to create table        $sql = "CREATE TABLE `Student` ( `ID` INT NOT NULL AUTO_INCREMENT , `name`VARCHAR(40) NOT NULL , `last_ame` VARCHAR(40) NOT NULL , `email` VARCHAR(40)NOT NULL , PRIMARY KEY (`ID`)) ";        // use exec() because no results are returned        $db->exec($sql);        echo "Table Student created successfully";        $database->closeConnection();   }   catch (PDOException $e)   {       echo "There is some problem in connection: " . $e->getMessage();   }   ?>

Inserting Data With PDO

In order to insert data into a table using PDO, first prepare the query using preparestatement. Next, this query is executed with the execute function. Note that this practice prevents SQL injection attacks.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

<?php   include_once 'connection.php';   try   {       $database = new Connection();       $db = $database->openConnection();       // inserting data into create table using prepare statement to prevent from sql injections       $stm = $db->prepare("INSERT INTO student (ID,name,last_name,email) VALUES ( :id, :name, :lastname, :email)") ;       // inserting a record       $stm->execute(array(':id' => 0 , ':name' => 'Saquib' , ':lastname' => 'Rizwan' , ':email' => 'saquib.rizwan@cloudways.com'));       echo "New record created successfully";   }   catch (PDOException $e)   {       echo "There is some problem in connection: " . $e->getMessage();   }   ?>

Select Data With PDO

In order to select data, first create a query string and then execute it in a for each loop to fetch records from the table.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

<?php   include_once 'connection.php';   try   {       $database = new Connection();       $db = $database->openConnection();       $sql = "SELECT * FROM student " ;       foreach ($db->query($sql) as $row) {       echo " ID: ".$row['ID'] . "<br>";       echo " Name: ".$row['name'] . "<br>";       echo " Last Name: ".$row['last_name'] . "<br>";       echo " Email: ".$row['email'] . "<br>";       }   }   catch (PDOException $e)   {       echo "There is some problem in connection: " . $e->getMessage();   }   ?>

Update Data With PDO

In order to update a record in the table, first declare a query string and then execute it with exec function.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

<?php   include_once 'connection.php';   try   {        $database = new Connection();        $db = $database->openConnection();        $sql = "UPDATE `student` SET `name`= 'yourname' , `last_name` = 'your lastname' , `email` = 'your email' WHERE `id` = 8" ;        $affectedrows  = $db->exec($sql);      if(isset($affectedrows))       {          echo "Record has been successfully updated";       }             }   catch (PDOException $e)   {       echo "There is some problem in connection: " . $e->getMessage();   }   ?>

Delete Data With PDO

In order to delete a record from the table, first declare a query string and then execute it with exec function.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

<?php   include_once 'connection.php';   try   {        $database = new Connection();        $db = $database->openConnection();        $sql = "DELETE FROM student WHERE `id` = 8" ;        $affectedrows  = $db->exec($sql);      if(isset($affectedrows))       {          echo "Record has been successfully deleted";       }             }   catch (PDOException $e)   {      echo "There is some problem in connection: " . $e->getMessage();   }   ?>

Conclusion

PDO is the data accessing layer that greatly eases the process of connecting and working with databases. Perhaps, the best thing about PDO is the streamlined process of database migration. In this article, I introduced PDO and highlighted how you could perform CRUD actions using PDO in PHP. If you have questions or would like to add to the discussion, do leave a comment below.