PHP MySQL Object Oriented Programming (OOP)

5 min readDec 11, 2018

If you search “Why PHP is good?”, the first result from Google will be something like this.

PHP is the most powerful programming language which is easier to learn and has great scope, importance. PHP is the best and important language for web developers used as server side scripting language. When compared to the other programming languages PHP is used in various platforms of the web development.

I have the same opinion. If you still ask me, “WHY?”, I would say:

It’s easy to learn and maintain
It’s fast and has good security.
It’s cross-platform.
It’s Object Oriented.

But there is only one thing, I hate about PHP is the $ sign. 😖

Today’s article is about PHP and MySQL. If you are using PHP to build your website and you are looking for a good database, MySQL will be the first answer.

Okay, enough of talking. Let’s do some coding:

I assume that you already have a minimum understanding of Object Oriented Programming and know how to CRUD in MySQL. In this article I will cover,

How to validate data before inserting into MySQL database.
How to create a class in PHP that handles almost all kind of database operations.
How to create a MySQL query builder in PHP.

Let’s start with the first one.

We will create a class called Safe, which will try to sanitize any kind of suspicious request in the __construct() method. It will have some public classes which will validate the user input before working with MySQL. You can read more about sanitizing in PHP from this link: php.net

Our Safe class will look something like this:

<?php

class Safe{

    public function __construct(){
        $_GET       = filter_input_array(INPUT_GET, FILTER_SANITIZE_STRING);
        $_POST      = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING);
        $_REQUEST   = (array)$_POST + (array)$_GET + (array)$_REQUEST;
    }

    public function Value($val){
        return trim($val);
    }
    public function noHTMLnoQuotes($string){
        return htmlentities($this->Value($string),ENT_NOQUOTES);
    }
    public function noHTMLquotes($string){
        return htmlentities($this->Value($string),ENT_QUOTES);
    }
    public function firstUpperCase($string){
        return ucfirst(strtolower($this->Value($var)));
    }
    public function allLowerCase($string){
        return strtolower($this->Value($string));
    }
    public function urlEncode($string){
        return urlencode($this->Value($string));
    }
    public function urlDecode($string){
        return urldecode($this->Value($string));
    }
    public function slash($string){
        return addslashes($string);
    }
    public function noSlash($string){
        return stripslashes($string);
    }
    public function sql($connection,$val){
        if(is_numeric($val)){
            return $val;
        }
        return "'".mysqli_real_escape_string($connection,htmlspecialchars($this->Value($val)))."'";
    }
    public function sqlWithArray($connection,$array){
        $return = array();
        foreach($array as $field=>$val){
            $return[$field] = "'".mysqli_real_escape_string($connection,htmlspecialchars($this->Value($val)))."'";
        }
        return $return;
    }
}
?>

Here we are doing some string validation. I believe it’s quite enough to prevent some SQL Injections.

The next part will cover the MySQL class where we will create a __construct() , where we will handle the connection to MySQL. This class will extend our Safe class which will handle the string validations.

class MySQL extends Safe

Before creating connection, let’s declare some connection variables.

    public $connection;
    public $error           = array();

    protected $host, $user, $password, $database;

In the snippet above, maybe you have noticed that we have declared two public variables. Other variables are declared as protected . The reason is, $connection is the MySQL connection variable which may need to handle some functions by other classes, on the other side, variables like $host $user $password $database are declared as protected which are not required to shared by other classes. The $error is an array which will store the MySQL error in any case. Now let’s see how the __construct() function looks like:

public function __construct(){
        parent::__construct();
        try{
            $this->host         = "Some host name";
            $this->user         = "Some user name";
            $this->password     = "Some password";
            $this->database     = "Some database name";

            $this->MySQLConnect();

        }catch(Exception $e){
            echo "Your exception handling".$e;
        }
  }

The parent::__construct(); is used for initializing the Safe class. Now let’s have a look at the MySQLConnect()function.

public function MySQLConnect(){
        $this->connection = mysqli_connect($this->host,$this->user,$this->password,$this->database);
        if(! $this->connection){
            $e                  = 'Failed to connect to DB';
            $this->setError($e);
            echo "Your exception handling".$e;
            return false;
        }
        return $this->connection;
 }

After creating the MySQL connection, it will return $this->connection which can be used for performing all kind of MySQL query execution. Now let’s focus on the Query builder.

A Query builder should have the following functions:

A function to select data from database table.
A function to insert data into database table.
A function to insert multiple data at a time(Bulk Insert)
A function to update data in a table
A function to execute complex query

If you have noticed that I did not include anything related to delete action. This is because, in real world we seldom use delete. Most of the time, we use a flag to determine whether the record was deleted(disabled 😝).

Let’s create the function retrieve data from database table. The function declaration is below:

public function Select($table, $condition = "", $sort = "", $order = " ASC ", $clause = " AND ")

This function expect 5 parameters where the first parameter is compulsory but the other parameters are optional.

/*@param table - the target table name@param condition - condition can be string,array of object,array of array@param sort - the field name that you want to be sorted@param order - ASC/DESC@param clause - AND/OR@return array
*/

Let’s create the function now:

public function Select($table, $condition = "", $sort = "", $order = " ASC ", $clause = " AND "){
        $query = "SELECT * FROM   ".$this->noHTMLnoQuotes($this->Value($table));
        if(!empty($condition)){
            $query .= $this->where($condition,$clause);
        }
        if(!empty($sort)){
            $query .= " ORDER BY ".$sort." $order";
        }
        return $this->Execute($query);
    }

If you have noticed that in the Select function we have called two other functions. Function Execute will be used for executing the MySQL query and function where will produce the query condition with clauses.

In the where function we will check the array type and create the query.

protected function where($condition,$clause){
        $query          = " WHERE ";
        if(is_array($condition)){
            $pair       = array();
            $size       = count($condition);
            if($size > 1){
                for ($i = 0; $i < $size; $i++){
                    $each = $condition[$i];
                    foreach($each as $field=>$val){
                        array_push($pair,"$field=".$this->sql($this->connection,$val));
                    }
                    if($size-1 === $i){
                        $query .= implode(" $clause ",$pair);
                    }
                }
            }else{
                foreach($condition as $field=>$val){
                    array_push($pair,"$field=".$this->sql($this->connection,$val));
                    $query .= implode(" $clause ",$pair);
                }
            }
        }else if(is_string($condition)){
            $query .= $condition;
        }else{
            $query = "";
        }
        return $query;
    }

Like this we can create other methods for Insert and Update as well. The final code is:

class.CreateReadUpdate.php file has the example of how to use the MySQL class.

So this is it. Hope, it was helpful. Thanks

PHP MySQL Object Oriented Programming (OOP)

Written by Mohammad Julfikar