Page 1 of 1

A Simple Database Abstraction Class Rate Topic: -----

#1 Splashsky   User is offline

  • D.I.C Regular
  • member icon

Reputation: 12
  • View blog
  • Posts: 323
  • Joined: 25-August 13

Posted 06 September 2018 - 07:21 PM

Simple Database Abstraction
Hey there! I'm writing this guide in order to show how I would create a simple database abstraction layer - a kind of proxy over normal database interactions.
This tutorial is mostly aimed towards newbies; especially those who don't already subscribe to a framework such as the awe-inspiring Laravel.

Objective(s)
As with any well-thought-out project, we need to set down some objectives. What do we want this to accomplish?
  • create a reusable database connection
  • allow secure access to that instance
  • make a simple function layer to basic CRUD functions

It's overall a really short list, but our class isn't that complex. What we want is a layer over database interactions that makes it more user- and reader-friendly to interact with our database.

CRUD, OOP?
What is CRUD and what is OOP? CRUD stands for Create, Read (Select), Update and Delete. These are the four basic interactions you'll always have with a database. OOP stands for Object Oriented Programming, and it's a design paradigm in which you create reusable objects to be used throughout your code for repetitive tasks. For example, if you had 100 nails to set into a piece of wood, you might make a hammer (or a nail gun) to make that repetitive task easier.

PDO
So the next important piece of information is PDO - what is it? PDO is PHP Data Objects. It's an API built into PHP to make interacting with databases easier and safer, as well as more semantic. Why are we making another layer, then? Because PDO - and PHP itself - is not perfect. It can be made and utilized better! By creating your own layer, you determine what features you want, what safety you want, what flexibility you want. This tutorial will hopefully just set the basics in place for you.

The Class
First off, create a PHP file - name it database.php or database.class.php or whatever you prefer. Here, I'll use database.php. Start by creating the class boilerplate.

<?php

class Database
{
    public function __construct()
    {
        
    }
}



So let's take a quick review. Start with the opening PHP tag, <?php, though you don't need (or want) the closing tag in this file.

Next, you will define the class with class Database, creating an object by the name of Database. Remember this; it's important later. Next, you see the public function named __construct(). What does public mean? It means that any external script can see and access the existence of the function. There's also private and protected, though we won't use protected in this tutorial. The __construct function will be called anytime we use the code $db = new Database().

Update the class, adding in our properties. Properties is the name for class-specific variables.

<?php

class Database
{
    private $username = 'username';
    private $password = 'password';
    private $server = 'localhost';
    private $database = 'database_name';
    private $instance;
    
    public function __construct()
    {
        
    }
}



The additions should be fairly self-explanatory. To login to our database, we need the username and password, we need the server the database is hosted on, and we need the name of the database we want to access. Lastly, we created a private - unseeable and inaccessible to the outside world - variable to store the instance of our database connection. This is so it can't be messed with from the outside, protecting ourselves from misuse in any way.

<?php

class Database
{
    private $username = 'username';
    private $password = 'password';
    private $server = 'localhost';
    private $database = 'database_name';
    private $instance;
    
    public function __construct()
    {
        $dsn = 'mysql:host='.$this->server.';dbname='.$this->database;
        $opt = [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
            PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ];
        
        $this->instance = new PDO($dsn, $this->username, $this->password, $opt);
    }
}



Now, we're adding in the meat of creating our connection. To connect to a database, the database server requires a DSN (click this to read what a DNS is), and so we create the DSN string for our PDO instance to use. Next, we set some "options" for our PDO connection. More on what's been chosen and why can be better explained elsewhere.

Next, at the end, we create the PDO instance and we store it in our private property. This is so we can use and reuse this instance elsewhere.

So how do we get to use this instance? Add this function below the construct function.

public function ins()
{
    return $this->instance;
}



Now, whenever we want to directly access the PDO instance, we simply call $class->ins()! But that's not where we'll stop. We want to make an easy, semantic way to perform basic CRUD functions. For now, I'll only do the R of the CRUD acronym - Read/Select. This function will allow you to grab information from a database.

public function select($what, $from, $cond, $fetch = true)
{
    $query = "SELECT ".$what." FROM ".$from." WHERE ".$cond;
    $result = $this->instance->query($query);
    if ($fetch) { return $result->fetch(); }
    return $result;
}



Here you go! Now to explain it; we create a public function called select, and we'll feed to it $what we want to fetch, from $where we want to fetch it, and what $conditions we require to get what we want - such as specifying a particular ID or username. Finally, we'll tell the function if we want the results of the query (the actual array of what was fetched) or if we just want to return the PDO object to be manipulated later. By default, we'll return the actual set of data we fetched.

Here's an example of a use of this class.

require_once('database.php');
$db = new Database();
$data = $db->select('*', 'settings', 'id=1');



This simple section of code requires the database class file to be imported into the currently running script. We create a new instance of our tool, our class, and put it into $db. Afterwards, we ask the Database class to select all (*) columns from our settings table, where the row has an id of 1. It will then return that full set of data, such as this...

// code:
var_dump($data); // using the $data variable from the example above

// output:
array(42) { ["id"]=> string(1) "1" [0]=> string(1) "1" ["gamename"]=> string(13) "Dragon Knight" [1]=> string(13) "Dragon Knight" ["gamesize"]=> string(3) "250" [2]=> string(3) "250" ["gameopen"]=> string(1) "1" [3]=> string(1) "1" ["gameurl"]=> string(0) "" [4]=> string(0) "" ["adminemail"]=> string(0) "" [5]=> string(0) "" ["forumtype"]=> string(1) "1" [6]=> string(1) "1" ["forumaddress"]=> string(0) "" [7]=> string(0) "" ["class1name"]=> string(4) "Mage" [8]=> string(4) "Mage" ["class2name"]=> string(7) "Warrior" [9]=> string(7) "Warrior" ["class3name"]=> string(7) "Paladin" [10]=> string(7) "Paladin" ["diff1name"]=> string(4) "Easy" [11]=> string(4) "Easy" ["diff1mod"]=> string(1) "1" [12]=> string(1) "1" ["diff2name"]=> string(6) "Medium" [13]=> string(6) "Medium" ["diff2mod"]=> string(3) "1.2" [14]=> string(3) "1.2" ["diff3name"]=> string(4) "Hard" [15]=> string(4) "Hard" ["diff3mod"]=> string(3) "1.5" [16]=> string(3) "1.5" ["verifyemail"]=> string(1) "1" [17]=> string(1) "1" ["shownews"]=> string(1) "1" [18]=> string(1) "1" ["showbabble"]=> string(1) "1" [19]=> string(1) "1" ["showonline"]=> string(1) "1" [20]=> string(1) "1" }



And there we go! This is the basics of creating a database abstraction layer, some of the logic behind it, and how you can put it to use.

I highly recommend reading further on PDO and MySQL as topics in order to maximize your knowledge and the potential of something like an abstraction layer. A great PDO tutorial can be found here. For MySQL, surf some of the SQL tutorials here.

Always keep learning, keep improving, and keep asking questions!
If I'm made a mistake or could improve my writing, please let me know via this thread or a PM.

Happy coding and cheers!

Is This A Good Question/Topic? 1
  • +

Page 1 of 1