Click here to Skip to main content
11,636,598 members (75,602 online)
Click here to Skip to main content

Tagged as

PHP's Database Operation- Go OO

, 8 Oct 2014 CPOL 9.3K 10
Rate this:
Please Sign up or sign in to vote.
The OO way to writing your database operation code in PHP

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.

License

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

Share

About the Author

Peter Leow
Instructor / Trainer
Singapore Singapore
子曰:"三人行,必有我师焉;择其善者而从之,其不善者而改之

"There is always something we can learn from another person. Choose to follow his strengths while use his shortcomings to reflect upon ourselves."
― Confucius

“Live as if you were to die tomorrow. Learn as if you were to live forever.”
― Mahatma Gandhi

You may also be interested in...

Comments and Discussions

 
SuggestionTo go real OOP using PHP, I suggest you take a look at Doctrine Pin
Fadi Chamieh9-Oct-14 8:04
memberFadi Chamieh9-Oct-14 8:04 
QuestionPHP Pin
Anatoli K7-Oct-14 22:27
professionalAnatoli K7-Oct-14 22:27 
AnswerRe: PHP Pin
Peter Leow8-Oct-14 15:38
professionalPeter Leow8-Oct-14 15:38 
QuestionPHP DB operation Pin
Frans_5512919-Aug-14 2:31
memberFrans_5512919-Aug-14 2:31 
AnswerRe: PHP DB operation Pin
Peter Leow25-Aug-14 22:54
professionalPeter Leow25-Aug-14 22:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150728.1 | Last Updated 8 Oct 2014
Article Copyright 2014 by Peter Leow
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid