Building the database abstraction class

In PHP, when creating a class, there is a way to call a certain method every time that class is initialized. This is called the constructor of the class. Most classes have a constructor, and so we shall have our own. The constructor function is named with two underscores with the construct() keyword, like this: function __construct(). Functions with two underscores are also known as magic methods.

In our database abstraction class we need to create a constructor to be able to return the link object generated by mysqli:

 Class DB { 
   
  public $db; 
   
  //constructor 
  function __construct($server, $dbname,$user,$pass) { 
    //returns mysqli $link $link = mysqli_connect(''); 
    return $this->db = mysqli_connect($server, $dbname, $user, $pass); 
  } 
} 

Raw query method

The query method will just execute the query of anything passed to it. We will just call MySQLi's db->query method in the query method.

Here is what it looks like:

public function query($sql) { 
 $results =   $this->db->query($sql); 
 return $results; 
} 

Create method

For our database layer, let's create the create method. With this, we will insert items into a database using SQL syntax. In MySQL, the syntax is as follows:

INSERT INTO [TABLE] VALUES ([val1], [val2], [val3]); 
 

We need a way to convert array values into a string with each value separated by commas:

 function create ($table, $arrayValues) { 
  $query = "INSERT INTO  `" . $table . " ($arrayVal);  //TODO: setup arrayVal 
  $results = $this->db->query($link, $query); 
} 

Read method

For our db layer, let's create the read method. With this, we will just query our database using SQL syntax.

The syntax in MySQL is as follows:

SELECT * FROM [table] WHERE [key] = [value] 

We'll need to create a function which is able to accept the preceding parameters in brackets:

public function read($table, $key, $value){ 
         $query  = SELECT * FROM $table WHERE `". $key . "` =  " . $value; 
     return $this->db->query($query); 
} 

Select all method

Our read method accepts a key and value pair. However, there may be cases where we just need to select everything in a table. In this case, we should create a simple method to select all the rows in a table, which only accepts the table to select as the parameter.

In MySQL, you just select all the rows using the following command:

SELECT * FROM [table]; 

We'll need to create a function which is able to accept the preceding parameters in brackets:

public function select_all($table){ 
         $query  = "SELECT * FROM " . $table; 
     return $this ->query($query); 
} 

Delete method

For our db layer, let's create the delete method. With this, we will delete some items in a database using SQL syntax.

The MySQL syntax is simple:

DELETE FROM [table] WHERE [key] = [val]; 

We'll also need to create a function which is able to accept the preceding parameters in brackets:

public function delete($table, $key, $value){ 
         $query  = DELETE FROM $table WHERE `". $key . "` =  " . $value; 
     return $this->query($query); 
} 

Update method

For our database layer, let's create an update method. With this, we will be able to update items in a database using SQL syntax.

The MySQL syntax looks like this:

UPDATE [table] SET [key1] = [val1], [key2] => [val2]  WHERE [key] = [value] 

Note

Note that the WHERE clause can be longer than just one key value pair, meaning you can add AND and OR to the statement. This means that, aside from making the first keys dynamic, the WHERE clause needs to be able to accept AND/OR to its parameter.

For example, you could write the following for the $where parameter to select the person whose firstname is John and lastname is Doe:

firstname='John' AND lastname='Doe' 

This is the reason why we made the condition just a string parameter in our function. The update method in our database class would finally look like this:

public function update($table, $updateSetArray, $where){ 
     Foreach($updateSetArray as $key => $value) { 
         $update_fields .= $key . "=" . $value . ","; 
     } 
      //remove last comma from the foreach loop above 
     $update_fields = substr($update_fields,0, str_len($update_fields)-1); 
    $query  = "UPDATE " . $table. " SET " . $updateFields . " WHERE " $where; //the where 
    return $this->query($query); 
} 

first_of method

In our database we will create a first_of method which will filter out the rest of the results and just get the very first one. We will use PHP's reset function, which just gets the very first element in an array:

//inside DB class  
public function first_of($results) { 
  return reset($results); 
} 

last_of method

The last_of method is similar; we can use PHP's end function:

//inside DB class  
public function last_of($results) { 
  Return end($results); 
} 

iterate_over method

The iterate_over method will be a function which simply adds formatting - before and after HTML code - to a string - for example, for every result we get from the database:

public function iterate_over($prefix, $postfix, $items) { 
    $ret_val = ''; 
    foreach($items as $item) { 
        $ret_val .= $prefix. $item . $postfix; 
    } 
    return $ret_val; 
} 

searchString method

Given an array of results, we will look through a certain field for something to search. The way to do this is to generate the SQL code that looks like this:

    SELECT * FROM {table} WHERE {field} LIKE '%{searchString}%';

The function would accept the table, and the field to check the search string needle in the table:

public function search_string($table, $column, $needle) { 
 $results = $this->query("SELECT * FROM `".$table."` WHERE " .    $column . " LIKE '%" . $needle. "%'"); 
   return $results; 
} 

Using the convert_to_json method to implement a simple API

Sometimes we want the results of a database to be in a specific format. An example is when we work with the results as JSON objects instead of arrays. This is useful when you are building a simple API to be consumed by a mobile application.

This could be possible, for example, in the case of another system that needs it in a certain format, for example, a JSON format, and we can feed it using the JSON format by converting objects to JSON and sending it off.

In PHP, there is the json_encode method, which converts any array or object into a JSON notation. Our class' method will just return the value passed into it as json:

function convertToJSON($object) { 
   return json_encode($object); 
   }