PHPs Database Operation Go OOP

pomosda Peter Leow

This tip is a guide to writing database operation code in PHP - the Object Oriented way. 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.


// 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".


// 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.


// 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.

     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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Source from : Code Project

  • 08 Oct 2014
  • By Peter Leow
  • PHP-5
  • 1228 Read