A simple Data Layer for OOP PHP Applications

9 Years Ago jkon 4 2K Views

Hello, first of all to understand this tutorial would be best if you have some knowledges about OOP ( https://www.daniweb.com/programming/web-development/tutorials/437592/introduction-to-phps-object-orientation by priteas ) and DB usage (MySQL in this case) with PHP https://www.daniweb.com/programming/web-development/threads/499320/common-issues-with-mysql-and-php by diafol ).
What we will try to achieve is a consistent usage of the DB (we will use MySQL but you can use what ever DB you like). We will not map query results in objects / lists of objects in this tutorial (there will be another one that will contain that) , but just retrieve the results as array.

The Db Object

**
What we will do is to simplify the usage of PDO , we will have one method for almost all needed interactions with the DB the request. More over when you prepare an SQL statement with PDO it creates (and returns) a PDOStatement object , if you want to use that in another part of your app and you prepare again this SQL statement it will create a whole new PDOStatement object and return it. This is not only problem of recreating the same PDOStatement object but also that if you choose PDO::ATTR_EMULATE_PREPARES => false in PDO options , the statement will be prepared again in DB side and if it is true (that is by default) you will force PHP to build again the bind validation scenario. So what we are going to do is make that PDOStatement objects instantiate only once per that request life.

/**
 * A very simple PDO child , in order to make the use of PDO even
 * easier and prepare the statements only once.
 */
class _Data_Db extends PDO
{
    /**
     * The PDO statements that have been used.
     * Keys of the array are the text of the statement.
     * That way PDO statements instantiates only once.
     * @var array of PDOStatement
     */
    private $statements = array();

    /**
     * It takes a sql statement and replacement values for question marks placeholders (if there are any) executes the query by a prepared statement and
     * returns the result (if any) as an multidimentional array with the first key the index num in the row of results, the second the name of result column.
     * @param $statement The query statement
     * @param none,string,array $values The replacement values as array for question marks placeholders if there are any. If there is only one then this can be a string
     * @throws Exception
     * @return null,array Returns the result (if any) as an multidimentional array with the first key the index num in the row of results, the second the name of result column and the value.
     */
    public function request($statement,$values = null)
    {
        $result=array();
        $statement = trim($statement);

        if(is_null($values))
        {
            $values = array();
        }
        else if(!is_array($values))
        {
            $values = array($values);
        }

        if(substr_count($statement,"?") !== count($values))
        {
            throw new Exception(
            "PREPARED STATEMENT QUESTION MARKS MISSMATCH : ".$statement
            , 4561);
        }

        if(!isset($this->statements[$statement]))
        {
            $this->statements[$statement] = $this->prepare($statement);
        }

        $this->statements[$statement]->execute($values);
        if($this->statements[$statement]->errorCode()!="00000")
        {
            $error = $this->statements[$statement]->errorInfo();
            throw new Exception($error[2],(int)$error[0]);
        }

        $result = $this->statements[$statement]->fetchAll();
        return $result;
    }
}

Here is a simple usage of it ,

error_reporting(E_ALL);
ini_set("display_errors", "1");

$database = "test";
$username = "testuser";
$password = "testpassword";

// Nowdays MySQL hase natural prepared statements and
// we gain from using them and not the PDO emulation of them
$pdoOptions = array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);

// You could use also try catch when you do with Db
$db = new _Data_Db("mysql:host=localhost;dbname=".$database
, $username, $password,$pdoOptions);

// an Insert Sql statement (e.g. here there is an autoincriment id)
$db->request("INSERT INTO directories (name,url) VALUES (?,?)"
    , array("This is a test directory","test") );

$lastInsertedId = $db->lastInsertId(); // if we want to keep that id

// a Select with a value (notice that here we have only one 
// question mark placeholder so we don't have to send it as array
$results = $db->request("SELECT * FROM directories WHERE id = ?" 
    , $lastInsertedId);
// if there are results 
if(count($results) > 0)
{
    // here the row will be the first row because we know that there 
    // can be only one result 
    var_export($results[0]);
    // will be an array ["name"] = "This is a test directory" 
    // , "url" = "test"
}

// A select all example 
$results = $db->request("SELECT * FROM directories ORDER BY id ASC");
// if there are results 
if(count($results) > 0)
{
    foreach($results as $row)
    {
        echo "<br/>";
        echo "id=".$row["id"]." name=".$row["name"]." url=".$row["url"];
    }
}

The Db Factory Object

**
What have we done so far is generalizing and simplify the usage of PDO with the request method. But an application may not have to do only with one database. So we will create a Db Factory object , the Data_Db objects in in will be instantiated only once by an array of configuration that we will pass to it.

/**
 * A Db factory object that produces _Data_Db objects
 */
class _Data_DbFactory
{

    /**
     * Array having key a db connection identifier and values or an array of
     * connection information or a string of dsn connection
     * @var array
     */
    private $dbPropertiesArray;

    /**
     * The _Data_Db objects that have been instantiated using the dbPropertiesArray
     * @var array of _Data_Db
     */
    private $dbObjects = array();

    /**
     * Options of the PDO constructor. Can be modified in each DB object as needed
     * @var array
     */
    private $defaultOptions = array(PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_STRINGIFY_FETCHES => false,
    PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,PDO::ATTR_CASE => PDO::CASE_NATURAL,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);

    /**
     * Creates a new instance from databases properties array
     * @param array $dbPropertiesArray
     */
    public final function __construct($dbPropertiesArray)
    {
        $this->dbPropertiesArray = $dbPropertiesArray == null ? array() : $dbPropertiesArray;
    }

    /**
     * Returns a _Data_Db object (PDO inhereted).
     * The parameters for the initialization comes from the
     * $dbPropertiesArray in the construction
     * (e.g. for db() will use the parameters from $dbPropertiesArray "" key
     * , for db("USERS") will use the parameters from $dbPropertiesArray "USERS" key
     * @param string $identifier
     * @return _Data_Db
     */
    public final function db($identifier = "")
    {
        if(!isset($this->dbObjects[$identifier]))
        {
            $this->initDb($identifier);
        }
        return $this->dbObjects[$identifier];
    }

    /**
     * Initialize the _Data_Db object according to the
     * $identifier provided
     * @param string $identifier
     * @throws _Exception_Data
     */
    private function initDb($identifier)
    {
        if(!isset($this->dbPropertiesArray[$identifier]))
        {
            throw new Exception("Db ".$identifier." has no properties for initialization ", 456554);
        }
        else
        {
            $dbConstructors = $this->dbPropertiesArray[$identifier];
            $charset = isset($dbConstructors["charset"]) ? ";charset=".$dbConstructors["charset"] : "";
            $options = $this->defaultOptions;
            if(isset($dbConstructors["options"]))
            {
                foreach($dbConstructors["options"] as $constantName => $value)
                {
                    $options[constant("PDO::".$constantName)] = $value;
                }
            }

            if(!isset($dbConstructors["username"]))
            {
                throw new Exception("You haven’t provided username for db object in properties ".$identifier, 456554);
            }
            else if(!isset($dbConstructors["password"]))
            {
                throw new Exception("You haven’t provided password for db object in properties  ".$identifier, 456554);
            }
            else if(isset($dbConstructors["dsn"]))
            {
                $this->dbObjects[$identifier] = new _Data_Db($dbConstructors["dsn"].$charset,$dbConstructors["username"],$dbConstructors["password"],$options);
            }
            else if(!isset($dbConstructors["database"]))
            {
                throw new Exception("You haven’t provided database for db object in properties  ".$identifier, 456554);
            }
            else
            {
                if(!isset($dbConstructors["host"]))
                {
                    $dbConstructors["host"] = "localhost";
                }

                if(!isset($dbConstructors["driver"]))
                {
                    $dbConstructors["driver"] = "mysql";
                }

                $this->dbObjects[$identifier] = new _Data_Db($dbConstructors["driver"].':dbname='.$dbConstructors["database"].";host=".$dbConstructors["host"].$charset,$dbConstructors["username"],$dbConstructors["password"],$options);

            }
        }
    }
}

As you can see we can send almost anything in the constructor , example of usage :

error_reporting(E_ALL);
ini_set("display_errors", "1");

// This array could be decoded by a json file above public_html
$dbPropertiesArray =
array(
        ""=>
            array("database" => "test",
            "username" => "testuser",
            "password" => "testpassword"),
        "users"=>
            array("database" => "test_db",
            "username" => "user7411",
            "password" => "74117411",
            "charset" => "UTF8")
);

$dbFactory = new _Data_DbFactory($dbPropertiesArray);

// This will use the "" properties 
$dbFactory->db()->request("INSERT INTO directories (name,url) VALUES (?,?)"
    , array("one more test","testing") );

// notice that "users" have charset UTF8 , the db object could 
// have different options or even use a different dbms
$resutls = $dbFactory->db("users")->request("SELECT * FROM posts");
var_export($resutls);

Data Workers Objects

**
We have now a Db factory but if we have the SQL and the use of the Db factory messed with other code we would create a mess. It is better for a certain logical unit (here we will do per table because is clearer ) to have all the SQL queries and the methods that use it in one place. But if we made that abstraction of a Data Worker class we might want to use it in another application also , there e.g. there could be already a Db object with “users” identifier in the dbProperties , so in order to make that abstraction we should make Data Workers accept also a map of the db objects.

/**
 * _Data_Worker is the class handling everything that has to do with data.
 * In its simple state has private properties with sql queries and public 
 * methods to use them.
**/
abstract class _Data_Worker
{

    /**
     * The _Data_DbFactory of the application
     * @var _Data_DbFactory
     */
    private $dbFactory; 

    /**
     * Array associating a new DB identifier with an existing one 
     * @var array
     */
    private $dbMaps; 

    public final function __construct(_Data_DbFactory $dbFactory,$dbMaps = array())
    {
        $this->dbFactory = $dbFactory;
        $this->dbMaps = $dbMaps;
    }

    /**
     * Returns the _Data_Db that has been created using the constructror identifier 
     * the given identifier can also be pointing to another identifier if dbMaps have beeen used
     * @param string $identifier The identifier of the contruction array of the _Data_Db object 
     * as provided in dbProperties array
     */
    protected final function db($identifier = "")
    {
        return $this->dbFactory->db(isset($this->dbMaps[$identifier]) ? $this->dbMaps[$identifier] : $identifier);
    }

    /**
     * Returns the _Data_DbFactory instance 
     * @return _Data_DbFactory
     */
    protected final function dbFactory()
    {
        return $this->dbFactory;
    }

    /**
     * Returns an array associating a new DB identifier with an existing one 
     */
    protected final function dbMaps()
    {
        return $this->dbMaps;
    }
}

Example of usage:

error_reporting(E_ALL);
ini_set("display_errors", "1");

// This array could be decoded by a json file above public_html
$dbPropertiesArray =
array(
        ""=>
            array("database" => "test",
            "username" => "testuser",
            "password" => "testpassword"),
        "users"=>
            array("database" => "test_db",
            "username" => "user7411",
            "password" => "74117411",
            "charset" => "UTF8")
);

$dbFactory = new _Data_DbFactory($dbPropertiesArray);

$dataFolder = new Model_Data_Folder($dbFactory);
$dataDocument = new Model_Data_Document($dbFactory);

$folderInsertId = $dataFolder->insert(5, "a test name");
$results = $dataFolder->selectAll();
var_export($results);
echo "<br/>";

$dataDocument->insert($folderInsertId, "aNameOfFile");
$results = $dataDocument->selectByFolder($folderInsertId);
var_export($results);
echo "<br/>";

If tomorrow I already have another “” key for db() in dbProperties for the Db Factory all I have to do is pass a map to Data Workers along with the db factory e.g.

error_reporting(E_ALL);
ini_set("display_errors", "1");

// This array could be decoded by a json file above public_html
$dbPropertiesArray =
array(
        ""=>
            array("database" => "anotherdb",
            "username" => "anotheruser",
            "password" => "anotherpassword"),
        "users"=>
            array("database" => "test_db",
            "username" => "user7411",
            "password" => "74117411",
            "charset" => "UTF8"),
        "filesystem"=>
            array("database" => "test",
            "username" => "testuser",
            "password" => "testpassword"),          
);

$dbFactory = new _Data_DbFactory($dbPropertiesArray);

$dataFolder = new Model_Data_Folder($dbFactory,array("","filesystem"));
$dataDocument = new Model_Data_Document($dbFactory,array("","filesystem"));

The goal of those classes is to create a consistent db layer for an OOP PHP application. More over it help us keep our code clean knowing where is what and reusable. I hope it will help people that are trying to create clean applications with PHP. Any comments are more than welcomed

*Classes here are based in a framework (_Underscore) but are simplified for a generic use without framework and structural rules. *

php

happygeek commented: Nice one +13

diafol commented: Thanks jkon. Bookmarked :) +15

jkon 638 Posting Whiz in Training

9 Years Ago

Hello , in Data Workers example of usage I didn't included the actual Data Workers classes that this example uses, here there are:

/**
 * The data worker for the folders
 */
class Model_Data_Folder extends _Data_Worker
{
    private $insertSql = "INSERT INTO people_folders (parent_id,folder_name) VALUES (?,?)";
    private $selectAllSql = "SELECT * FROM people_folders ORDER BY id ASC";

    public function insert($parentId,$folderName)
    {
        $this->db()->request($this->insertSql,array($parentId,$folderName));
        return $this->db()->lastInsertId();
    }

    public function selectAll()
    {
        return $this->db()->request($this->selectAllSql);
    }
}


/**
 * The data worker for the documents
 */
class Model_Data_Document extends _Data_Worker
{
    private $insertSql = "INSERT INTO people_documents (folder_id,file_name) VALUES (?,?)";
    private $selectByFolderIdSql = "SELECT * FROM people_documents WHERE folder_id = ? ORDER BY id ASC";

    public function insert($folderId,$fileName)
    {
        $this->db()->request($this->insertSql,array($folderId,$fileName));
    }

    public function selectByFolder($folderId)
    {
        return $this->db()->request($this->selectByFolderIdSql,$folderId);
    }
}

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.