PHP's Database Operation- Go OO

Introduction

This tip is a guide to writing database operation code in PHP - the OO way.

Background

I have come across people asking me the proper way of writing database operation code in PHP on a number of occasions. Not wanting to answer the same question over and over again, I have decided to publish the answer here.

The Approach

Step 1: Create a PHP page called "config.php" that defines the database related parameters.

<?php   // config.php     
      define('DB_USER', "username");
      define('DB_PASSWORD', "password");  
      define('DB_DATABASE', "database name"); 
      define('DB_SERVER', "ip address of database server"); 
?>

Step 2: Create a PHP page that contains a class that takes care of the database connection chore, call it "db_connect.php".

<?php  // db_connnect.php
class DB_Connect {
     private $con;
 
    // constructor
    function __construct() {
        // connecting to database
            $this->con = $this->connect();
    }
 
    /**
     * Function to connect with database
     */
    private function connect() {
        // import database connection variables
        require_once __DIR__.'/config.php';
        try {
            $conn = new PDO('mysql:host='.DB_SERVER .';dbname='.DB_DATABASE, DB_USER, DB_PASSWORD);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch(PDOException $e) {
            echo 'ERROR: ' . $e->getMessage();
        }
        return $conn;
    }
    
    public function getDbConnection(){
        return $this->con;
    }
}
?>

Step 3: Create a PHP page that contains a class that contains all the functions for all your SQL queries, say "db_functions.php". You will include every SQL query as a function in this file. That will promote reusability and maintainability.

<?php    // db_functions.php
class DB_Functions {
    private $con;
    // constructor
    function __construct() {
        require_once __DIR__.'/db_connect.php';
        // connecting to database
        $db = new DB_Connect();
        $this->con = $db->getDbConnection();
    }
 
    public function selectUser($id) {
        try {
            $stmt = $this->con->prepare('SELECT * FROM user WHERE id = :id');
            $params = array(':id' => $id);
            $stmt->execute($params);
            return $stmt;
        } catch(PDOException $e) {
            echo 'ERROR: ' . $e->getMessage();
        }
    }
 
    public function otherSQLfunction($parameter) {
        // other sql code
    }
}

Step 4: Finally, your other PHP files will simply need to call the appropriate functions in the "db_functions.php" whenever they need to query the database.

<?php
     require_once __DIR__.'/db_functions.php';
 
     $db = new DB_Functions();
 
     $result = $db->selectUser($id);
 
     //  other code
?>

Points of Interest

It seems to be a hassle to split code into separate files, but that extra work when it is done correctly is only required once at the initial stage. With proper planning and design, your subsequent coding and maintenance will be a breeze.