How to Connect to ANY DB using PHP Data Objects

Tutorial outline

• What is PDO?
• The Tools we shall use in this tutorial
• What we build in this tutorial
• Installing MariaDB, PHP and Apache using XAMPP
• Creating out database
• Connecting to a database using PDO
• Performing Database operations – CRUD operations and database transactions
• Wiring the whole application and setting up Postman
• Changing dialect to PostgreSQL
• Connecting to PostgreSQL database using PDO
• Conclusion

What is PDO?

If you are building data-driven applications, then you have to use a database. PHP provides different ways in which you can interact with MySQL databases such as mysql and mysqli.

MySQL database was acquired by Oracle. MariaDB, which is a fork of MySQL, is available for community development and commercial use free of charge.

However, PHP applications can interface with other databases such as PostgreSQL and Oracle. This makes it cumbersome to connect to different databases using different implementations from one PHP application. To unify the process of connecting to different databases, PHP introduced PDO (PHP Data Objects). Thus if you are going to use more that one database or change your database in the middle of your project, using PDO to interface your application with the database means that you will not re-write your queries.

Then the advantage of using PDO lies in the ability to use an identical interface for 12 different databases and once you are familiar with this PDO API, it is portable from database to database.

However, if you’re sure that the only database you will be using is MySQL or MariaDB, then you can choose between PDO and mysqli.

With PDO, you use a driver to connect to each particular database and the driver must be available in your PHP installation. You do not need to install/enable a driver for MySQL database because PHP ships with it, especially when you use PHP and MySQL bundled in XAMPP software. However, if you are using separately installed databases such as PostgreSQL, then you will need some additional steps to set it up-we will explain the steps in this post.

PDO has drivers that support 12 different databases. The table below shows some of the driver names for the supported databases. See more documentation here.

The Tools we shall use in this tutorial

• MySQL/MariaDB database and Apache webserver both bundled in XAMPP software
• PostgreSQL database
• pg Admin 4 – a GUI tool for accessing PostgreSQL
• Postman – to help us send requests and receives responses
• Windows OS
• Code editor – I am using Visual Studio Code (You can use your favourite).

What we build in this tutorial

Think of a factious application, which stores company details, and employees belonging to that company.

The entities we have are company and employee with a relationship/association of ONE TO MANY i.e. 1 company has many employees (see ERD below)

Entity Relationship Diagram 

The operations we aim to perform include:

• Inserting records
• Reading records
• Inserting Multiple records at once in one table
• Insert records in both tables, both of which must either succeed or fail – Transaction

These operations are executed in both MySQL/MariaDB and PostgreSQL to show that, when PDO is used, your code will remain the same regardless of the database used.

Installing MariaDB, PHP and Apache using XAMPP

You can install MariaDB, PHP and Apache by installing XAMMP. XAMPP for Windows OS can be downloaded from here and should be easy to install.

After installation, use the XAMPP control panel to ensure that Apache and MySQL/MariaDB is running.

XAMPP control panel

When MySQL is running, there is a default database user called root with no password.

If you are using your machine, MySQL can be accessed via your browser using http://localhost/phpmyadmin, where you can create and administer your databases as shown below.

PHPMyAdmin database ministration. All your databases are on the left side and you can run queries using the SQL tab.

 Creating out database

We will create a database with name decode (You can name your database the way you like) by running this query.

CREATE DATABASE decode;

You will see your created database on the left side of the phpMyAdmin window. Click on it and run the following queries to create company and employee tables.

CREATE TABLE company (	
    id int PRIMARY KEY AUTO_INCREMENT,	
    name varchar (32) NOT NULL,	
    website varchar (255)
)

CREATE TABLE employee (    
    id int PRIMARY KEY AUTO_INCREMENT,    
    full_name varchar (32) NOT NULL,    
    age int,    
    companyId int,    
    FOREIGN KEY (companyId) REFERENCES company (id)
);

Connecting to a database using PDO

Create your project inside htdocs directory of XAMPP so that your PHP scripts will be interpreted, your HTTP requests processed and responses returned.

The project structure is as shown below

htdocs
  decodeapp
     -util.php
     -db.php
     -index.php
     -operations.php         

The util.php is a PHP class, which contains static database variables and its contents are shown below.

<?php
    class Util {
        //About DB
        static $DB_NAME = "decode";
        static $DB_USER = "root";
        static $DB_USER_PASS = "";
        static $SERVER_NAME = "localhost";
    }
?>

By making the variables static, we will access them without creating an object of the class. The contents in util.php can be changed to match your development environment. The contents of db.php are as shown below. It is a class called DBConnector with a function connectToDB, which returns the database connection handle, $pdo.

From the code, the database connection happens in the class constructor. The data source name (dsn) contains the dialect i.e. target DBMS name, host and the database name. Notice that we have included the util.php file in db.php. Also notice how we get the host and database name values from the Util class using:: (double colon). The PDO database handle is then created by instantiating the PDO class:

$this->pdo=new PDO($dsn,Util::$DB_USER,Util::$DB_USER_PASS, $options);

Database connection options are prepared in form of associative arrays. The connectToDB method returns the PDO connection handle:

public function connectToDB(){
    return $this->pdo;
} 	

Should any error occur when connecting to the database, the catch block will print the error message.

What do the options really mean?

There are many options you can add to the options associative array (see them here). However, I will explain the ones we have used in our connection.

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION: Any errors, which occur when we perform a database transaction will be reported in form of exceptions. You can use PDOException class to catch errors specific to PDO or use Exception class to catch any error.

PDO::ATTR_EMULATE_PREPARES => false: PDO interface uses prepared statements under the hood. If you do not know what prepared statements are and why they are useful, read here.  If a given database driver does not support prepared statements, then PDO will emulate prepared statements. In our case, we’ve turned off prepared statements emulation since we are certain that the PDO_MYSQL driver supports them out of the box. Note that using emulated prepared statements is equally acceptable.

PDO::ATTR_DEFAULT_FETCH_MODE =>PDO::FETCH_ASSOC: PDO gives you the luxury of deciding how you want your data to be presented when you read it from a database table. In our case, we want data presented as an associative array i.e. an array indexed by columns name. If you do not specify, then the default is PDO::FETCH_BOTH, which returns an indexed array (indexed by number) and associative array (indexed by table column name).

Performing Database operations – CRUD operations and database transactions

We write a class called Operations inside the operations.php file. The class has methods, which will perform these operations.

We will not create properties for this class because all operations for both company and employee are performed here.

The skeleton of the class looks as shown below. Notice that each method receives all the data it needs plus a PDO connection handle.

<?php
    class Operations {
       function __construct(){}
       public function saveCompany ($pdo, $name, $website){
       }
       public function saveEmployee ($pdo, $full_name,
       $age, $companyId){
       }
       public function readCompany ($pdo){
       }
       public function searchCompany ($pdo, $companyId){
       }
       public function saveManyCompany ($pdo, $companyList
       {			
            //For inserting multiple records into the
            company table
       }
       public function saveCompanyAndEmployee ($pdo,
       $company, $employee){
           //For inserting a record both in company and
           employee in which //case both are expected to be
           successfull or fail - all or nothing
           //i.e a transaction.
       }    
    }
?>

 Inserting a record  company table

The saveCompany function would look like this.

public function saveCompany ($pdo, $name, $website){
    try {
        $stmt=$pdo->prepare("INSERT INTO company
        (name,website)VALUES (?,?)");
        $stmt->execute([$name,$website]);
        $stmt = null;
        return "Company has been saved";
    } catch (PDOException $e) {
        return $e->getMessage();
    }
}

A SQL query is prepared using the prepare() method in $pdo object and then executed using the execute() method in $stmt. The execute() accepts an array with the same number of values/elements as the placeholders.

We set the error mode to be exceptions, thus the code above will return a message explaining the error in case it happens.

There are two ways in which you can prepare queries using prepared statements. The one used above uses placeholders (?) but one can also use named variable. If we rewrite the function above to use named variable, it would change as shown below:

public function saveCompany ($pdo, $name, $website){
    try {
       $stmt=$pdo->prepare("INSERT INTO company
       (name,website)VALUES (:name,:website)");
       $stmt->execute([':name'=>$name,':website'=>
       $website]);
       $stmt = null;
       return "Company has been saved";
    } catch (PDOException $e) {
       return $e->getMessage();
    }
}

Insert a record in the employee table

Because we must show the company to which an employee belongs, we need to have a valid id in the company table. Thus, the function saveEmployee() would look like as shown below:

public function saveEmployee ($pdo, $full_name, $age, $companyId){    
     try {
        $stmt = $pdo->prepare ("INSERT INTO employee
        (full_name,age,companyId) VALUES (?,?,?)");
        $stmt->execute([$full_name,$age,$companyId]);
        $stmt = null;
        return "Employee has been saved";
     } catch (PDOException $e) {
        return $e->getMessage();
     }
}

If you try to insert an employee record with a companyId that is not already in the company table, you will see this error:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails(`decode`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`companyId`) REFERENCES `company` (`id`))

Reading all records from the company tables

Reading all records from the company table will yield an associative array whose keys are names of table columns. The readCompany() function would be as shown below.

public function readCompany ($pdo){
    try {
       $stmt=$pdo->prepare("SELECT * FROM company");
       $stmt->execute();
       $result = $stmt->fetchAll();
       $stmt=null;
       return json_encode($result);
    } catch (PDOException $e) {
       return $e->getMessage();
    }
}

Notice the use fetchAll() method on $stmt to read all the records returned from the table. We then use json_encode() to convert the associative array to JSON format. Thus, the output from the function would be similar to as below. The output is described as ‘an array of objects’.

[
    {
       "id": 2,
       "name": "fourth profile",
       "website": "fourthprofile.com"
    },
    { 
       "id": 3,
       "name": "Skillsday",
       "website": "Skillsday.co"
    },
    { 
       "id": 4,
       "name": "Bobolink Ltd",
       "website": "Bobolink.co.ke"
    }
]

Searching for a company using an id (primary key)

For one to search the company table, a unique column value would be needed such as the company id. Thus the function searchCompany() would look like this.

public function searchCompany ($pdo, $companyId){
    try {
        $stmt=$pdo->prepare("SELECT * FROM company WHERE
        id=?");
        $stmt->execute([$companyId]);
        $result = $stmt->fetch();
        return json_encode($result);
    } catch (PDOException $e) {
        return $e->getMessage();
    }
}

Notice that because we were expecting one record, we have used fetch() on $stmt instead of fetchAll().

In this case, if companyId value is 3, the output from the function is a JSON object as shown below.

{
    "id": 3,
    "name": "Skillsday",
    "website": "Skillsday.co"
}

Inserting multiple records into the company table

The beauty of PDO is that you prepare a query/statement once and execute it many times. Suppose you have multiple company records in JSON format as shown below, and you want to insert all the records into the companies table.

[        
    {
       "name": "Safaricom",
       "website": "Safaricom.co.ke"
    },
    {
       "name": "Centum",
       "website": "Centum.co.ke"
    },
    { 
       "name": "Microsoft",
       "website": "microsoft.com"
    }
]

The data above is described as an array of objects. Thus, you need to loop through the array and retrieve name and website as properties of the objects in the array. The saveManyCompany() function would then look like as shown below.

public function saveManyCompany ($pdo, $companyList){
    try {
       $stmt = $pdo->prepare ("INSERT INTO company (name,
       website) VALUES (?,?)");
       foreach ($companyList as $company){
          $stmt->execute([$company->name,
          $company->website]);
       }
       return "Companies has been saved";
    } catch (PDOException $e) {
       return $e->getMessage();
    }
}

Running transactions with PDO object

One of the properties of database transactions is Atomicity  It means that a transaction should be treated as a unit, which means that all the operations in transactions are all executed or none i.e. ‘all or nothing‘.

For instance, if we were to insert two records, one in company table and the other in the employee table, if the two queries belong to a transaction, then if one query fails, for some reason, all must fail.

By default, a query effect is auto-committed in a table but this behaviour can be changed. If you turn off auto-commit behaviour, then you have the opportunity to commit or rollback any query effects manually. Let’s see how we can run transactions using PDO in the saveCompanyAndEmployee() function.

public function saveCompanyAndEmployee ($pdo, $company, $employee){    
     /*$company and $employee are arrays, which contain
     data belonging to company table and employee table
     respectively*/    
     $pdo->setAttribute(PDO::ATTR_AUTOCOMMIT,FALSE);
     try {
         //begin a transaction
         $pdo->beginTransaction();
         //prepare the two queries
         $stmtCompany = $pdo->prepare ("INSERT INTO company
         (name, website) VALUES (?,?)");
         $stmtEmployee = $pdo->prepare ("INSERT INTO
         employee (full_name,age,companyId) 
         VALUES (?,?,?)");        
         //execute both queries
         $stmtCompany->execute([$company[0],$company[1]]);
         $stmtEmployee->execute([$employee[0],$employee[1],
         $employee[2]]);
         //commit here
         $pdo->commit();
         return 'Transaction was successiful';
     } catch (Exception $e) {
         //rollback to cancel if anything goes wrong
         $pdo->rollBack();
         return $e->getMessage();
     }
} 

From the code above, notice that $pdo -> setAttribute (PDO :: ATTR_AUTOCOMMIT, FALSE) removes auto-commit. On the other hand $pdo->beginTransaction() starts the transaction to which the 2 queries belong. You will have to commit changes manually ($pdo->commit()) if all goes well or  roll back any changes, which may have occurred ($pdo->rollBack()).

The complete Operations Class

<?php
class Operations {
    //class constructor
    function __construct(){}
    public function saveCompany ($pdo, $name, $website){
       try {
          $stmt=$pdo->prepare("INSERT INTO company
          (name,website)VALUES (?,?)");
          $stmt->execute([$name,$website]);
          $stmt = null;
          return "Company has been saved";
       } catch (PDOException $e) {
          return $e->getMessage();
       }
    }
    public function saveEmployee ($pdo, $full_name, $age,
    $companyId){
       try{
          $stmt = $pdo->prepare ("INSERT INTO employee
          (full_name,age,companyId) VALUES (?,?,?)");
          $stmt->execute([$full_name,$age,$companyId]);
          $stmt = null;
          return "Employee has been saved";
       } catch (PDOException $e) {
          return $e->getMessage();
       }
    }
    public function readCompany ($pdo){
       try{
          $stmt=$pdo->prepare("SELECT * FROM company");
          $stmt->execute();
          $result = $stmt->fetchAll();
          $stmt=null;
          return json_encode($result);
       } catch (PDOException $e) {
          return $e->getMessage();
       }
    }
    public function searchCompany ($pdo, $companyId){
       try{
          $stmt=$pdo->prepare("SELECT * FROM company WHERE
          id=?");
          $stmt->execute([$companyId]);
          $result = $stmt->fetch();
          return json_encode($result);
       } catch (PDOException $e) {
          return $e->getMessage();
       }
     }
     public function saveManyCompany ($pdo, $companyList){
       try {
          $stmt = $pdo->prepare ("INSERT INTO company
          (name, website) VALUES (?,?)");            
          foreach ($companyList as $company){
             $stmt->execute([$company->name,
             $company->website]);
          }
          return "Companies has been saved";
       } catch (PDOException $e) {
          return $e->getMessage();
       }
     }
     public function saveCompanyAndEmployee ($pdo,
       $company, $employee){
       /*$company and $employee are arrays, which contain
       data belonging to company table and employee table
       respectively*/
       $pdo->setAttribute(PDO::ATTR_AUTOCOMMIT,FALSE);
       try {
           //begin a transaction
           $pdo->beginTransaction();
           //prepare the two queries
           $stmtCompany = $pdo->prepare ("INSERT INTO
           company (name, website) VALUES (?,?)");
           $stmtEmployee = $pdo->prepare ("INSERT INTO
           employee (full_name,age,companyId) 
           VALUES(?,?,?)");
           //execute both queries
           $stmtCompany->execute([$company[0],
           $company[1]]);
           $stmtEmployee->execute([$employee[0],
           $employee[1],$employee[2]]);
           //commit here
           $pdo->commit();
           return 'Transaction was successiful';
        } catch (Exception $e) {
           //rollback to cancel if anything goes wrong
           $pdo->rollBack();
           return $e->getMessage();
        }
     } 
}

?>

Wiring the whole application

Now that we know all the contents of util.php, db.php and util.php, it is time to understand how index.php will help us wire the application. index.php is the entry point to our application. It receives HTTP requests and as well as return HTTP responses.

To insert a record in the company table using Postman, follow these steps:

1. Add the following code in the index.php file.

<?php
    include_once './db.php';
    include_once './operations.php';

    //Database handle $pdo
    $con = new DBConnector();
    $pdo = $con->connectToDB();

    //creating object of Operations class.
    $operations = new Operations();

    //Inserting one record into the company table.
    $name = $_POST['name'];
    $website = $_POST['website'];
    echo $operations->saveCompany($pdo,$name,$website);
?>

In index.php, we expect that data will be sent using POST method and there are two values sent, name and website. The only thing happening in this file is to connect to the database, then use Operations class to perform all the transactions. Do not forget to include db.php and operations.php files inside index.php.

2. Setup postman:

Postman is a software used by API designers but it can also be used to create and send HTTP requests and receive responses.

You can download Postman for here and it should be easier to install it in Windows OS.  The postman’s free plan already has many features you can use hence you do not need to pay anything. When you open it, it will appear as shown below.

The Postman interface 

You click on ‘New’ to create a new request, then the interface you see after creating the request will allow you to construct a new HTTP request by specifying all the information needed i.e URL, Selecting method, adding data in the body or as URL parameters and any custom headers.

3. Create a postman request

URL: http://localhost/decodedapp/index.php

Method: POST

Body: Key pair values for company name and website

headers: No modification needed.

Click send

The figure below shows the structure of the request. For the data format, you can select form-data or x-www-form-urlencoded.

Inserting a record in the company table using Postman

To read all the records in the company table using Postman, follow these steps.

1. Modify index.php page as follows

<?php
    include_once './db.php';
    include_once './operations.php';

    //Database handle $pdo
    $con = new DBConnector();
    $pdo = $con->connectToDB();

    //creating object of Operations class.
    $operations = new Operations();
    echo $operations->readCompany($pdo);
?> 

2. Create a request on postman as shown below and click Send.

Read all the records in the company table using Postman

Creating postman requests for inserting a record into the employee table, searching a record in the company table and running a transaction should now be easier.

In the case of inserting multiple records in the company table, you can send JSON formatted data (having multiple records) from postman by following these steps.

1. Create a postman request, click on body tab, then click on raw on body data format options and in the last option, select JSON.
2. Construct your request as shown below

Postman request body with JSON data

3. Before you click Send, add this code in your index.php file.

<?php
    include_once './db.php';
    include_once './operations.php';

    //Database handle $pdo
    $con = new DBConnector();
    $pdo = $con->connectToDB();

    //creating object of Operations class.
    $operations = new Operations();

    //Read the body of your request and store it in 
    $jsondata variable.    
    $jsondata = file_get_contents('php://input');
    //Call the saveManyCompany() but you have to use json_decode()
    to convert your raw body data into a JSON data variable.
    echo $operations->saveManyCompany($pdo, json_decode($jsondata));
?>

If everything goes well, then you should see “Companies has been saved” in your postman as a response.

Changing dialect to PostgreSQL

All along, we have been using MySQL/MariaDB but we had mentioned that the benefit of using PDO is the ability to hop from one database to another without touching your queries. So now we need to set up a new database connection using PostgreSQL instead.

Installing PostgreSQL and pgAdmin 4

Download PostgreSQL from here and it should easy to install in Windows OS. When installing PostgreSQL, it will ask you to set a password for a default user Postgres. This is the password you will use to make a connection.

Download pgAdmin 4 from here and it should be easier to install it in Windows OS. pgAdmin 4 is a management tool for PostgreSQL just like phpMyAdmin for MySQL.

Open pgAdmin 4 and it should open your browser ready to access your PostgreSQL. You must log in using the password you had set when installing PostgreSQL. See diagram below. If you have difficulties setting up pgAdmin 4 seek help here.

Creating the company and employee table

Use the pgAdmin 4 query tool to run your queries shown below

CREATE TABLE company (
    id serial PRIMARY KEY,
    name varchar (32) NOT NULL,
    website varchar (255)
)

CREATE TABLE employee (
    id serial PRIMARY KEY,
    full_name varchar (32) NOT NULL,
    age int,
    companyId int,
    FOREIGN KEY (companyId) REFERENCES company (id)
);

Notice that these queries are exactly the same as the ones we used to create the tables in MySQL/MariaDB, except in the id column, where we have used serial. Serial in PostgreSQL adds the auto-increment on that column, makes the column unique and adds NOT NULL constraint.

Connecting to PostgreSQL database using PDO

Change your util.php file as shown below

<?php
    class Util{

       //About DB
       static $DB_NAME = "decode";
       static $DB_USER = "postgres";
       //Use password you set to postgres user
       static $DB_USER_PASS = "your password here";
       static $SERVER_NAME = "localhost";
    }
?>

Notice that we changed the $DB_USER value to Postgres and of course, $DB_USER_PASS value need to have the right value.

Next, change the contents of the db.php as shown below:

<?php
    include_once './util.php';
    class DBConnector {
       protected $pdo;
       function __construct(){
          $dsn="pgsql:host=".Util::$SERVER_NAME.";dbname="
          .Util::$DB_NAME ."";
          $options = [
              PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
              PDO::ATTR_EMULATE_PREPARES => false,
              PDO::ATTR_DEFAULT_FETCH_MODE =>PDO::FETCH_ASSOC
          ];
          try{
              $this->pdo=new
              PDO($dsn,Util::$DB_USER,Util::$DB_USER_PASS,
              $options);
          }catch (PDOException $e){
              echo $e->getMessage();
          }
       }
       public function connectToDB(){
              return $this->pdo;
       }
       public function closeDB(){
              $this->pdo = null;
       } 
   }
?>

Notice that the only thing that we have changed is changing the database dialect from MySQL to pgsql in $dsn.

At this point, if you try to connect to the PostgreSQL database, you will this message ‘could not find driver’. This is because we have not activated the PDO_PGSQL driver. This means that the methods such as prepare() and execute() will not be available.

To enable PDO_PGSQL driver you will need to comment it in php.ini file in XAMPP’s php directory.

//Location of php.ini file
xampp
   php
      -php.ini        


   //Inside the php.ini file,
   ;extension=pdo_pgsql //Remove the ; at the beginning of this line

Restart your Apache server for the changes to take effect.

Now if you repeat everything we did with MySQL/MariaDB, it will work without changing anything else in the code.

Lastly, there are many aspects of PDO, which we did not touch in this tutorial. For instance, getting a count of rows affected by the last DELETE, INSERT, or UPDATE statement, getting the last inserted index and many more. You can find all these in PDO’s official documentation.

Conclusion

This was a long tutorial but I hope you had fun exploring PDO. Thank you for stopping.