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: 346
  • 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
  • +

Replies To: A Simple Database Abstraction Class

#2 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2906
  • View blog
  • Posts: 11,328
  • Joined: 03-December 12

Posted 12 October 2018 - 08:29 AM

The only negative I have to add, is this does not show how to use prepared statements; which should be required for all database interactions, unless you don't mind a known attack vector.

Keep up the good work.
Was This Post Helpful? 0
  • +
  • -

#3 matthewjohn938   User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 51
  • Joined: 12-October 18

Posted 21 November 2018 - 10:45 PM

Thanks for the tutorial, Its very helpful.
Was This Post Helpful? 0
  • +
  • -

#4 benanamen   User is offline

  • D.I.C Head

Reputation: 36
  • View blog
  • Posts: 241
  • Joined: 28-March 15

Posted 16 February 2019 - 09:12 PM

View Postastonecipher, on 12 October 2018 - 08:29 AM, said:

Keep up the good work.


A little late to the party, but I am going to have to strongly disagree. This is a very bad "tutorial". The only positive thing about it is that it uses PDO.

The very serious SQL Injection Attack vector mentioned by @asonecipher is enough reason to pull this "tutorial" down. A Class is supposed to be re-usable. This is far from it. The login credentials are hard coded, the DSN is hard Coded, and the PDO options are hard coded. The class is extremely tightly coupled to the one and only implementation hard coded within it. You cannot connect to another database or database type without editing the Class. You cannot choose which PDO options you use without editing the Class. You cannot copy this Class to another application without editing the Class. These items should be passed in to the Class. You shouldn't have to edit it to use it.

The SQL Injection Attack vulnerable select Method has a hard coded WHERE condition. What if I don't have a WHERE condition? What if I want more than one result? Now what? Create yet another query method? It would be better for CRUD methods to go in another Class and then use Dependency Injection to pass the PDO connection to it. $fetch has a hard coded parameter of true so the if condition will always be true making the if pointless while the return statement may very well be false. The secondary redundant return will never execute.

This is a very dangerous class and should not be used unless you want to get hacked.
Was This Post Helpful? 1
  • +
  • -

#5 CTphpnwb   User is offline

  • D.I.C Lover
  • member icon

Reputation: 3825
  • View blog
  • Posts: 13,929
  • Joined: 08-August 08

Posted 12 May 2019 - 06:42 PM

View Postbenanamen, on 16 February 2019 - 11:12 PM, said:

A little late to the party, but I am going to have to strongly disagree.

Agreed. The only reason I can see for using an abstraction class is to ensure that PDO is used properly, and this doesn't do that. It's a hard thing to accomplish in reality. Every abstraction layer I'm aware of resorts to allowing raw queries because of all the edge cases where it won't work. To my mind that disqualifies the abstraction layer.

Honestly, we should all be using PDO properly in the first place. That would ensure security without adding another compute layer to slow processing down.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1