Database Level CRUD Operations Using PDO
Tags: pdo, php, howto
Published: 2017-05-22 10:58:08

---
title: Database Level CRUD Operations Using PDO
published: true
description: Learn how to perform database level CRUD operations using PDO
tags: PDO,PHP,Howto
cover_image: http://image-store.slidesharecdn.com/ccacf475-37f1-4476-9287-e6250f374d5f-original.jpeg
---
  
No real world PHP application could function without a database.

This is why PHP offers three powerful ways of manipulating and interacting with databases. The list comprises of *MySQL*, *MySQLi* and *PDO*.  

To remove all the problems caused by these different database management approaches, PHP has standardized PHP Data Objects (PDO) as the database layer of choice. PDO provides easy and very streamlined interface for interacting with the databases in the PHP applications. To work with PDO, the database specific driver(s) must be installed. 

# Supported Databases 

PDO supports all popular databases through specific drivers. In effect, the following database drivers are available in PDO. 


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

By default, PHP has a PDO_SQLite driver installed. However, if you wish to work with other databases, [you must first install the relevant driver](http://php.net/manual/en/pdo.installation.php). 
 
In order to check which drivers are installed on your system, create a new PHP file, and add the following code to it:


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

# Performing CRUD Operations Through PDO 

PDO is the best way of performing CRUD and related DBMS operations in PHP because PDO standardizes code that can be reused without major issues. In effect, PDO acts as an abstraction layer that separates database related operations from the rest of the code. 

## Database Connections

Connectivity in PDO is simple. Consider the following code snippet that is used to set up connections with a database. Note that when the underlying database changes, the only change that you need to make in this code is the [database type](https://www.cloudways.com/blog/mysql-data-types/). 


```php
 <?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 database is MySQL. However, if you need to change the database 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.

## Table Creation

To create a table, I will first declare a query string and then *execute* it with exec function. Note that this will not return any data. 

```php
<?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();
}
 
?>
```

## Data Insertion

To insert data into a table using PDO, I will first use prepared statements to protect the query from SQL injections. Next, I will execute this query with the *execute* function.

```php
<?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

In order to select data, first create a query string and then execute it in a *foreach* loop to fetch records from the table:


```php
<?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. 

```php
<?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.

```php
<?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 access layer that greatly streamlines the process of connecting and working with databases. Using PDO, you could easily carry out all database related operations without dealing with the nitty-gritty of database level problems. If you have questions or would like to add to the discussion, do leave a comment below