Creating a PHP website using MVC – 3 Creating a Database Wrapper

In the previous parts of this tutorial series, we learned how to create a working example of an MVC framework, but without a Model. Now we will create a Database wrapper singleton class which can be used everywhere.

Before we create a Database wrapper, let us create an install file which installs required database, tables etc. Create a new folder in the public folder called ‘install’ and create an index.php inside. Copy this code into the file.

<?php
require_once "../../system/config/config.php";

$db = new PDO('mysql:host='.DB_HOST.';charset=utf8', DB_USER, DB_PASS);
$db->setAttribute(PDO::ERRMODE_EXCEPTION,TRUE);

try{
    $db->exec("CREATE DATABASE IF NOT EXISTS ".DB_NAME);
    $db->__construct('mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset=utf8',DB_USER,DB_PASS);
}catch(PDOException $e){
    die($e->getMessage());
}
try{
    $db->exec("CREATE TABLE ".TABLE_SETTINGS."(
        param VARCHAR(20) NOT NULL,
        value VARCHAR(250) NOT NULL
    );");
    //Add another tables..
    $db->exec("CREATE TABLE ".TABLE_USERS."(
        id BIGINT AUTO_INCREMENT NOT NULL,
        username VARCHAR(30) NOT NULL,
        password VARCHAR(100) NOT NULL,
        email VARCHAR(100) NOT NULL,
        fullname VARCHAR(100) NOT NULL,
        privilege INT(2) NOT NULL,
        PRIMARY KEY (id)
    );");
    $default_username = 'admin';
    $default_fullname = 'Administrator';
    $default_email = '';
    $default_password = md5($default_username.'admin'.$default_email.'s4lt$t61N9');
    $statement = $db->prepare("INSERT INTO ".TABLE_USERS."(username,password,email,fullname,privilege)VALUES(:u,:p,:e,:f,1)");
    $statement->bindValue(':u',$default_username,PDO::PARAM_STR);
    $statement->bindValue(':p',$default_password,PDO::PARAM_STR);
    $statement->bindValue(':e',$default_email,PDO::PARAM_STR);
    $statement->bindValue(':f',$default_fullname,PDO::PARAM_STR);
    $statement->execute();
}catch(PDOException $e){
    die($e->getMessage());
}

echo "All done";
?>

There are some constants used like TABLE_SETTINGS, TABLE_USERS which are defined inside our config file.

<?php
define('DEVELOPMENT_ENVIRONMENT', TRUE);
define('DEFAULT_CONROLLER', 'index');
define('DEFAULT_ACTION', 'index');

define('DB_HOST','localhost');
define('DB_NAME','mvc_example');
define('DB_USER','root');
define('DB_PASS','');

define('TABLE_SETTINGS','settings');
define('TABLE_USERS','users');
?>

Call the url by calling http://localhost/mvc_example/install/

The server will redirect all calls into the public folder and runs the installer. The database, settings table and users table are created. An entry for an admin user is also created. You can modify this code as you need to include some other tables or entries. After this is done, we can delete the install.php file because it is not needed anymore.

Also Read:   7 Lerping tricks you need to know as a Game Developer

Now into the MySQL database wrapper. This is a singleton object which instance is created and made available as a static member of the class Database. The Database.class.php file inside lib folder looks like,

<?php
/**
 * Database Singleton
 */
class Database extends PDO {
    protected static $instance;
    
    //A cache to hold prepared statements
    protected $cache;
    
    /**
     * Get instance of the PDO
     * @return PDO
     */
    static function getInstance($dsn=NULL,$dbname=NULL,$dbpass=NULL){
        if(!self::$instance){
            self::$instance = new Database($dsn,$dbname,$dbpass);
        }
        return self::$instance;
    }
 
 function __construct($dsn,$dbname,$dbpass) {
  parent::__construct($dsn,$dbname,$dbpass);
        $this->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
        $this->cache = array();
 }
    
    /**
     * If the statement is not cached, cache it and return PDOStatement
     * If the statement is already cached, return the cached statement
     * @return PDOStatement
     */
    function getPreparedStatment($query){
        $hash = md5($query);
        if(!isset($this->cache[$hash])){
            $this->cache[$hash] = $this->prepare($query);
        }
        return $this->cache[$hash];
    }
    
    function __destruct(){
        $this->cache = NULL;
    }
}

?>

There is a getInstance() static method which accepts database host, username and password which is used to create a PDO instance and is assigned to the static variable $instance. So whenever we call Database::getInstance() it will check for any previous instance and if found the same is returned or if it is not found, one is created and returned.

There is another method called getPreparedStatement() which caches each unique statement into an array. If later we call the same query again, the old statement is executed.

[Total: 0    Average: 0/5]
  • This comment has been removed by the author.

  • Yes, why?

  • is that blogger? m getting confused

  • In the index.php page we need to call the Database like this

    Database::getInstance('mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset=utf8', DB_USER, DB_PASS);
    before loading the controller.

    Check the full source

  • Hi,

    Thanks for nice tutorial however I am getting :Fatal error: Uncaught exception 'PDOException' with message 'invalid data source name' in C:wampwwwmvcmvc_blueprintlibDatabase.class.php on line 23.

    It seems like the $dsn has not been initialized anywhere. Or Where have I gone wrong?

    CODE:
    setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    $this->cache = array();
    }

    /**
    * If the statement is not cached, cache it and return PDOStatement
    * If the statement is already cached, return the cached statement
    * @return PDOStatement
    */
    function getPreparedStatment($query){
    $hash = md5($query);
    if(!isset($this->cache[$hash])){
    $this->cache[$hash] = $this->prepare($query);
    }
    return $this->cache[$hash];
    }

    function __destruct(){
    $this->cache = NULL;
    }
    }

    ?>