Database Level CRUD Operations Using PDO

#pdo #php #howto

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.

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.

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

Top comments (6)

Mārtiņš Briedis • May 23 '17

It's nice to know the basics, but you would be mad not to use a convenient ORM or Active Record library, even without a framework. Long time ago, I found RedBean, it was pretty new (8 years ago?), but I was blown away how simple CRUD operations became with it. Still, appreciate the article, good job!

Saquib Rizwan • May 25 '17

You are absolutely right, This article is beginners, As it is important to learn the basics first. I have written several articles on many frameworks in which I have demonstrated Active records as well. I actively contribute articles on Cloudways official blog.