PHP MySQLi wrapper class

Question

I've created a minimal PHP-MySQLi database wrapper class that can be used to run insert, select, update and delete queries via prepared methods with ease.

Here's the wrapper class:

<?php
/**
 * MySQLi Database Class
 * @category  Database Access
 * @package   Database
 * @author    AashikP
 * @copyright Copyright (c) 2018 AashikP
 * @license   https://opensource.org/licenses/MIT The MIT License
 * @version   0.1
 */

namespace database;

class MySQLiDB
{
    // Mysqli instance.
    private $mySqli;

    // Save Prefix if defined.
    private $prefix  = '';

    // Generate an array from given $data values for bindParam
    private $bind_arr = array(''); // Create the empty 0 index

    // Set type to use in bindPar function
    private $type;

    // Set table with prefix if exists
    private $table;

    // array to generate bind_results
    private $result_arr = array('');

    // array to catch multiple rows of results
    private $multi_result_arr = array();

    // array to fetch values
    private $fetch = array();

    public function __construct()
    {
        // Create a connection
        $this->connect();
        // Check if a database prefix is defined. If defined, set prefix value
        defined('DB_PREFIX') ? $this->setPrefix(DB_PREFIX) : null;
    }

    // Connect using a mysqli instance
    private function connect()
    {
        $this->mySqli = new \mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
        // Is there an issue connecting to the database?
        if ($this->mySqli->connect_errno) {
            echo '<br/>', 'Error: Unable to connect to Database.' , '<br>';
            echo "Debugging errno: " .  $this->mySqli->connect_errno , '<br>';
            echo "Debugging error: " .  $this->mySqli->connect_error , '<br>';
            unset($this->mySqli);
            exit;
        }
    }

    // Set prefix for the table name if there's a prefix setup in the config file
    private function setPrefix($value = '')
    {
        $this->prefix  = $value;
    }

    // Function to insert data into table
    public function insert($args)
    {
        // set type
        $this->type = 'insert';
        // set table and configure prefix, if available
        $this->setTable($args['table']);
        // generate insert query
        $query = $this->genQuery($args);
        // prepare query statement
        $stmt = $this->mySqli->prepare($query);
        if ($this->mySqli->errno) {
            die('Unable to insert data:<br /> '.$this->mySqli->errno .' : '. $this->mySqli->error);
        }
        // generate the bind_arr to be used to bind_param
        $this->bindPar($args);
        // bind parameters for statement execution
        call_user_func_array(array($stmt, 'bind_param'), $this->returnRef($this->bind_arr));
        // execute the statement (return error if execution failed)
        if (!$stmt->execute()) {
            die('Error : ('. $this->mySqli->errno .') '. $this->mySqli->error);
        }
        // close statement
        $stmt->close();
        $this->reset();
    }

    // Function to update data
    public function update($args)
    {
        // set type for use in query generator
        $this->type = 'update';
        // set table and configure prefix, if available
        $this->setTable($args['table']);
        // generate update query
        $query = $this->genQuery($args);
        // prepare query statement
        $stmt = $this->mySqli->prepare($query);
        if ($this->mySqli->errno) {
            die('Unable to insert data:<br /> '.$this->mySqli->errno .' : '. $this->mySqli->error);
        }
        // generate the bind_arr to be used to bind_param
        $this->bindPar($args);
        // bind parameters for statement execution
        call_user_func_array(array($stmt, 'bind_param'), $this->returnRef($this->bind_arr));
        // execute the statement (return error if execution failed)
        if (!$stmt->execute()) {
            die('Error : ('. $this->mySqli->errno .') '. $this->mySqli->error);
        }
        // close statement
        $stmt->close();
        $this->reset();
    }

    // Function to select data from the table
    public function select($args)
    {
        // set type for use in query generator
        $this->type = 'select';
        // set table and configure prefix, if available
        $this->setTable($args['table']);
        // generate select query
        $query = $this->genQuery($args);
        // prepare query statement
        $stmt = $this->mySqli->prepare($query);
        if ($this->mySqli->errno) {
            die('Unable to select data:<br /> '.$this->mySqli->errno .' : '. $this->mySqli->error);
        }
        // generate the bind_arr to be used to bind_param
        $this->bindPar($args);
        // bind parameters for statement execution if bind_arr is not empty
        // bind_arr will be empty if you're trying to retrieve all the values in a row
        if (!empty($this->bind_arr)) {
            call_user_func_array(array($stmt, 'bind_param'), $this->returnRef($this->bind_arr));
        }
        // execute the statement (return error if execution failed)
        if (!$stmt->execute()) {
            die('Error : ('. $this->mySqli->errno .') '. $this->mySqli->error);
        }
        // if you've manually defined the data that you need to retrieve, generate result set
        if (is_array($args['data'])) {
            // generate the result set as an array to be
            $this->genResultArr($args);
            call_user_func_array(array($stmt, 'bind_result'), $this->returnRef($this->result_arr));
            if ($this->mySqli->errno) {
                die('Unable to select data:<br /> '.$this->mySqli->errno .' : '. $this->mySqli->error);
            }
            $this->fetch = array(); // making sure the array is empty
            $i=0;
            while ($stmt->fetch()) {
                $this->multi_result_arr = array_combine($args['data'], $this->result_arr);
                // Get the values and append it to fetch array $i denotes the row number
                foreach ($this->multi_result_arr as $arr => $val) {
                    $this->fetch[$i][$arr] = $val;
                }
                $i++;
            }
            // if there's just one row of results retrieved, just reset the array
            // so that you can directly call the value by $fetch['column_name']
            if (count($this->fetch) == 1) {
                $this->fetch = $this->fetch[0];
            }
        } elseif ($args['data'] == '*') {
            // Generate a result metadata variable to be used to fetch column names in the array
            $res = $stmt->result_metadata();
            // Copy the column tables as an array into the fields variable to generate bind_result later
            $fields = $res->fetch_fields();
            // Field count for iteration
            $count = $res->field_count;
            // row count to chose type of array (multidimensional if more than one row found)
            $row = $res->num_rows;
            for ($i = 0; $i < $count; $i++) {
                $this->multi_result_arr[$i] = $this->result_arr[$i] = $fields[$i]->name;
            }
            call_user_func_array(array($stmt, 'bind_result'), $this->returnRef($this->result_arr));
            if ($this->mySqli->errno) {
                die('Unable to select data:<br /> '.$this->mySqli->errno .' : '. $this->mySqli->error);
            }
            $this->fetch = array(); // making sure the array is empty
            $i=0;
            // create a fetch array that combines the required db column names with the retrieved results
            while ($stmt->fetch()) {
                $this->fetch[$i] = array_combine($this->multi_result_arr, $this->result_arr);
                $i++;
            }
            // if there's just one row of results retrieved, just reset the array
            // so that you can directly call the value by $fetch['column_name']
            if (count($this->fetch) == 1) {
                $this->fetch = $this->fetch[0];
            }
        }
        $stmt->close();
        // reset values for next query
        $this->reset();
        return $this->fetch;
    }

    // Function to delete values from a Database
    public function delete($args)
    {
        // delete function must not be used to truncate tables
        if (!isset($args['where'])) {
            echo 'If you really want to delete all the contents, use truncate() method.';
            return;
        } elseif (isset($args['data'])) { // if you're just deleting fields, use update statement instead
            echo 'If you want to delete certain column in a row, use the update statement instead';
        }
        // set type for use in query generator
        $this->type = 'delete';
        // set table and configure prefix, if available
        $this->setTable($args['table']);
        // generate delete query
        $query = $this->genQuery($args);
        // prepare query statement
        $stmt = $this->mySqli->prepare($query);
        if ($this->mySqli->errno) {
            die('Unable to delete data:<br /> '.$this->mySqli->errno .' : '. $this->mySqli->error);
        }
        // generate the bind_arr to be used to bind_param
        $this->bindPar($args);
        // bind parameters for statement execution
        call_user_func_array(array($stmt, 'bind_param'), $this->returnRef($this->bind_arr));
        // execute the statement (return error if execution failed)
        if (!$stmt->execute()) {
            die('Error : ('. $this->mySqli->errno .') '. $this->mySqli->error);
        }
        // close statement
        $stmt->close();
        $this->reset();
    }

    // Deletes all the data and resets the table. Please use with caution
    public function truncate($table)
    {
        // set table and configure prefix, if available
        $this->setTable($table);
        // query to truncate the entire table
        // NOTE: This is irreversible
        $query = 'TRUNCATE ' . $this->table;
        // prepare query statement
        $stmt = $this->mySqli->prepare($query);
        // execute the statement (return error if execution failed)
        if (!$stmt->execute()) {
            die('Error : ('. $this->mySqli->errno .') '. $this->mySqli->error);
        }
        // close statement
        $stmt->close();
        $this->reset();
    }

    // prefix table name if db prefix is setup
    private function setTable($table)
    {
        $this->table  =  $this->prefix . $table;
    }

    // Generates the mysqli query statement
    private function genQuery($args)
    {
        switch ($this->type) {
            case 'insert':
                $query  = "INSERT INTO `" . $this->table .'` ';
                $query .=  $this->genInsert($args['data']);
                $query .= " VALUES " . $this->genInsval($args['data']);
                break;
            case 'select':
                $query = "SELECT " . $this->genSelect($args) .  " FROM " . $this->table;
                if (isset($args['where'])) {
                    $query .= $this->genWhere($args);
                }
                if (isset($args['order'])) {
                    $query .= $this->genOrder($args);
                }
                if (isset($args['group'])) {
                    $query .= $this->genGroup($args);
                }
                if (isset($args['limit'])) {
                    $query .= " LIMIT " . $args['limit'];
                }
                break;
            case 'update':
                $query  = "UPDATE `" . $this->table . "` SET";
                $query .= $this->genUpdate($args['data']);
                if (isset($args['where'])) {
                    $query .= $this->genWhere($args);
                }
                break;
            case 'delete':
                $query ="DELETE FROM `" . $this->table . '` ';
                if (isset($args['where'])) {
                    $query .= $this->genWhere($args);
                }
                break;
            default:
                $query ='';
                break;
        }
        return $query;
    }

    // Generate insert query
    private function genInsert($data)
    {
        $ins_query = '( ';
        foreach ($data as $key => $value) {
            if ($data[$key] == end($data)) {
                $ins_query .= ' ' . $key . ' ';
                continue;
            }
            $ins_query .= ' ' . $key . ', ';
        }
        $ins_query .= ')';
        return $ins_query;
    }

    // generate the value part of the insert query to be used as a prepared statement
    //  Eg (? , ?, ?)
    private function genInsVal($data)
    {
        $ins_value = '(';
        foreach ($data as $k => $v) {
            if ($data[$k] == end($data)) {
                $ins_value .= '?';
                continue;
            }
            $ins_value .= '?, ';
        }
        $ins_value .=')';
        return $ins_value;
    }

    // generate update query
    private function genUpdate($data)
    {
        $update_query = '';
        foreach ($data as $key => $value) {
            $update_query .= ' ' .$key .' =?,' ;
        }
        $update_query = rtrim($update_query, ',');
        return $update_query;
    }

    // Generate select query
    private function genSelect($sel_array)
    {
        $sel_string = '';
        if (is_array($sel_array['data'])) {
            foreach ($sel_array['data'] as $value) {
                $sel_string .= $value . ', ';
            }
            $sel_string = rtrim($sel_string, ', ');
        } elseif ($sel_array['data'] == '*') {
            $sel_string = '*';
        }
        return $sel_string;
    }

    // Generate where condition for query generator (genQuery)
    private function genWhere($where_arr)
    {
        $where_query = ' WHERE';
        if (isset($where_arr['whereOp'])) {
            $opr = $where_arr['whereOp'];
        } else {
            $opr = '=';
        }
        // Check if the given array is associative
        if ($this->isAssoc($where_arr)) {
            foreach ($where_arr['where'] as $key => $value) {
                $where_query .= ' ' . $key . $opr . '? ';
            }
        } else {
            foreach ($where_arr['where'] as $value) {
                $where_query .= ' ' . $value . $opr . '? ';
            }
        }
        if (isset($where_arr['and']) && !empty($where_arr['and'])) {
            $where_query .= $this->andWhere($where_arr);
        }
        if (isset($where_arr['or']) && !empty($where_arr['or'])) {
            $where_query .= $this->orWhere($where_arr);
        }
        return $where_query;
    }

    // Generate and condition for query generator (genQuery)
    private function andWhere($and_arr)
    {
        $and_query = ' AND';
        if (isset($where_arr['andOP'])) {
            $opr = $where_arr['andOP'];
        } else {
            $opr = '=';
        }
        foreach ($and_arr['and'] as $key => $value) {
                $and_query .= ' ' . $key . $opr . '? ';
        }
        return $and_query;
    }

    // Generate OR condition for query generator (genQuery)
    private function orWhere($or_arr)
    {
        $or_query = ' OR';
        if (isset($or_arr['orOP'])) {
            $opr = $or_arr['orOp'];
        } else {
            $opr = '=';
        }
        foreach ($or_arr['and'] as $key => $value) {
                $or_query .= ' ' . $key . $opr . '? ';
        }
        return $or_query;
    }

    // Generate order by condition
    private function genOrder($args)
    {
        $order_query = ' ORDER BY ' . $args['order'] .' ';
        if (isset($args['oType']) && (($args['oType'] == 'ASC') || ($args['oType'] == 'DESC'))) {
            $order_query .= $args['oType'];
        }
        return $order_query;
    }

    // Generate group by conditions
    private function genGroup()
    {
        $grp_query = ' GROUP BY ' . $args['group'] .' ';
        if (isset($args['gType']) && (($args['gType'] == 'ASC') || ($args['gType'] == 'DESC'))) {
            $grp_query .= $args['gType'];
        }
        return $grp_query;
    }

    // Check the input array and forward it to bindParam for further processing
    private function bindPar($args)
    {
        if (isset($args['data']) && $this->type != 'select') {
            $this->bindParam($args['data']);
        }
        if (isset($args['where'])) {
            $this->bindParam($args['where']);
        }
        if (isset($args['and'])) {
            $this->bindParam($args['and']);
        }
        if (isset($args['or'])) {
            $this->bindParam($args['or']);
        }
        if ($this->type == 'select' && !isset($args['where']) && !isset($args['and'])  && !isset($args['or'])) {
            unset($this->bind_arr);
        }
    }

    // Organize generation of bind_arr in the below method based on $data
    private function bindParam($data)
    {
        if (is_array($data)) {
            if ($this->isAssoc($data)) {
                foreach ($data as $key => $value) {
                    $this->bindValues($value);
                }
            } else {
                foreach ($data as $value) {
                    $this->bindValues($value);
                }
            }
        } else {
            $this->bindValues($data);
        }
    }

    // Detect type and push values inside the bind_arr to be submitted as bind parameters
    private function bindValues($value)
    {
        $this->bind_arr[0] .= $this->detectType($value);
        array_push($this->bind_arr, $value);
    }

    // Detect value type to generate bind parameter
    protected function detectType($value)
    {
        switch (gettype($value)) {
            case 'string':
                return 's';
                break;
            case 'integer':
                return 'i';
                break;
            case 'blob':
                return 'b';
                break;
            case 'double':
                return 'd';
                break;
        }
        return '';
    }

    protected function returnRef(array &$arr)
    {
        //Referenced data array is required by mysqli since PHP 5.3+
        if (strnatcmp(phpversion(), '5.3') >= 0) {
            $refs = array();
            foreach ($arr as $key => $value) {
                $refs[$key] = & $arr[$key];
            }
            return $refs;
        }
        return $arr;
    }

    // Generate a result array with selected values from database for given data
    private function genResultArr($args)
    {
        $this->result_arr = array();
        foreach ($args['data'] as $value) {
            array_push($this->result_arr, $value);
        }
    }

    // Check if an array is associative
    private function isAssoc(array $array)
    {
        $keys = array_keys($array);
        return array_keys($keys) !== $keys;
    }

    // Reset to default values after an operation
    private function reset()
    {
        $this->type = null;
        $this->table = '';
        $this->bind_arr = array('');
        $this->result_arr = array();
        $this->multi_result_arr = array();
    }

    // Disconnects the active connection
    private function disconnect()
    {
        if (isset($this->mySqli)) {
            $this->mySqli->close();
            unset($this->mySqli);
        }
    }

    // Making sure we don't have open connections
    public function __destruct()
    {
        if (isset($this->mySqli)) {
            // if there's an active connection, close it
            if ($this->mySqli->ping()) {
                $this->disconnect();
            }
        }
    }
}

Here's the optional config.php file that goes with it:

<?php
/**
 * This is an example configuration file. Even though the file is optional,
 * the constants defined below are required for the wrapper class to work.
 */

/** MySQL database name */
define('DB_NAME', 'DATABASE NAME HERE');

/** MySQL database username */
define('DB_USER', 'DATABASE USER NAME HERE');

/** MySQL database password */
define('DB_PASSWORD', 'DATABASE PASSWORD HERE');

/** MySQL hostname */
define('DB_HOST', 'localhost');

/** [Optional] MySQL database prefix */
define('DB_PREFIX', '');

How it works:

Available functions

insert(); update(); select(); delete(); truncate();

USAGE

Please note

You'll either need to define the required configs in the class file or include the config.php file along with this code in order to make a successful db connection as shown below:

<?php
$db = new MySQLiDB;
?>

INSERT

To insert content, create an array with table and the data to be inserted and then call the insert function 'table' => 'table name'; 'data' is an array with the 'keys' as field names and 'values', as the values that need to be entered in the fields

<?php
$args = [
    'table' => 't1',
    'data' => [
        'f11' => '123',
        'f12' => 'hello'
    ]
    ];
// Calling the below function will submit f11 = 123, f12 ='hello' etc into the table 't1'
$db->insert($args);
?>

UPDATE

To update content, create an array with table and the data to be inserted and then call the update function 'table' => 'table name'; 'data' is an array with the keys as field names and values as the values that need to be updated in the database fields

if you need to update specific values, you can specify where, and and or properties

<?php
$args = [
    'table' => 't1',
    'data' => [
        'f11' => '123',
        'f14' => '456',
    ],
    // [Optional] However, if you do not define a where condition, fields in every row will
    // be overwritten with the arg contents.
    'where' => [
        'id' => 10
    ],
    // [Optional] where operator is '=' by default, you only need to specify
    // this if you would like to use a different operator.
    'whereOp' => '=',
    // [Optional] 'and' condition, works the same way as where condition
    // andOp is '=' by default
    'and' => [

    ],
    'andOp' => '=',
    // [Optional] 'or' condition, works the same way as where condition
    // orOp is '=' by default
    'or' => [

    ],
    'orOp' => '',
    ];
?>

Calling the below function would update value of 'f1' to 'test' where id = 10 and 'f2' = 'foo'

<?php
$args = [
    'table' => 't1',
    'data' => [
        'f1' => 'test',
    ],
    'where' => [
        'id' => 10,
    ],
    'and' => [
        'f2' => 'foo',
    ]
    ];

$db->update($args);
?>

You can even set value to empty if you want to delete certain value from a row. The below code will set the value of field one to '' and field two to 'foo' where id = 1 (f1 and f2 are the fields)

<?php
$args = [
    'table' => 'test',
    'data' => [
        'f1' => '',
        'f2' => 'foo'
    ],
    'where' => [
        'id' => 1
    ]
    ];

$db->update($args);
?>

SELECT

To select content, create an array with table and the data to be selected and then call the select function 'table' => 'table name'; 'data' is an array with the keys as field names that need to be retrieved if you need to select everything, you can use 'data' => '*'

<?php
    //  available options
$args = [
    'table' => 'table_name',
    // data can either be an array with values defining field names that need to be retrieved, or just 'data' => '*'
    'data' => [
        'field1', 'field2'
    ],
    'where' => [
        'field3' => 'foo'
    ],
    'whereOp' => '!=',  // (only need to be defined if its anything other than =)
    'and' => [
    ],
    'andOp' => '', // (only need to be defined if its anything other than =)
    'or' => [
    ],
    'orOp' => '', // (only need to be defined if its anything other than =)
    'limit' => 2, // this will limit the rows returned
    'order' => '', // order by
    'oType' => '', // ASC or DESC
    'group' => '', // group by
    'gType' => '', // ASC or DESC
    ];

// Example
$args = [
    'table' => 't1',
    'data' => [
        'f1'
    ],
    'where' => [
        'f1' => 'hi',
    ]
    ];

$fetch = $db->select($args);
foreach ($fetch as $res) {
// below code will dump all the rows. If you want a specific output,
// check the echo statement below
    var_dump($res);
}
// Or you can chose to display them row wise
echo $fetch[0]['f2'];
?>

• In the above code, we're displaying the value of field f2 from the first row of returned result set

• If there's only one row in the result set, you can access it directly as shown in the below echo statement

DELETE

This is used to delete a row. If you only want to remove a single field from a row, use the update statement instead and set the value of that field to '' (empty string)

<?php
$args = [
    'table' => 't1',
    'where' => [

    ],
    'or' => [

    ],
    ];

$db->delete($args);
?>

Example
<?php
// Below statements should delete the row where id = 10, in table 'test'
$args = [
    'table' => 'test',
    'where' => [
        'id' => 10
    ]
    ];

$db->delete($args);

?>

TRUNCATE

If you want to delete an entire table, you will need to use the truncate function instead.

<?php
$db->truncate($table_name);
?>

Improvements I'm unsure about:

1. Minimize connection: Should I remove the disconnect option from my __destruct() and then make a check in the connect function to see if there's an active connection and only if there's none at the moment, make the connection? Would it be any better performance wise or in terms of code readability?

   <?php
   // Example
   private function connect()
   {
       if (!$this->mySqli->ping()) {
           // make the connection
       }
   }
   ?>
   

2. Unnecessary comments: I've repeated the comments in multiple cases where there are same functions. I personally believe these comments are unnecessary, but I want to know if this is the recommended method

This is not currently used in production. I'm still learning the art of PHP and I wrote the piece of code as a learning experience. As of now the code works as intended. However, what I would like to know is, if the code can be used in production environment?

Link to repo: Github

Answer 1

This is not much a wrapper. Rather call it a wannabe Query Builder.

I don't know the reason, but many people are constantly trying to write something like this. And I don't understand why.

Okay, for the DML queries it makes sense - it always makes a cool feeling when you automate a routine task using a template. So, for the insert query it asks for the method insert(). But select?

Do you really want to write

$args = [
    'table' => 't1',
    'data' => [
        'f1'
    ],
    'where' => [
        'f1' => 'hi',
    ]
    ];

$fetch = $db->select($args);

instead of just

$fetch = $db->select("SELECT f1 FROM t1 WHERE h1='hi'");

really really? No kidding? But why? Do you think it looks cool? Or make it you write less code? Or make another programmer to understand it better?

Do you really want to make that neat and universal SQL split into array with gibberish keys? WTF is "data"? Okay, I am working on a project with you. Why should I puzzle myself with such questions? Why can't I use the familiar SQL that reads as plain English?

And where are JOINs? And what will be your $settings array when you add them?

Come on, you've gone too far. I understand the logic that led you here but it's time to turn back. Leave SELECT queries alone and let them be written as plain SQL with parameters.

Besides, such a class should be ten times shorter. In the recent years PHP has been improved a lot, making most of tricks you are using obsoleted. Not to mention such rather silly code blocks like

        if ($this->isAssoc($data)) {
            foreach ($data as $key => $value) {
                $this->bindValues($value);
            }
        } else {
            foreach ($data as $value) {
                $this->bindValues($value);
            }
        }

here, the condition is useless as both loops are doing literally the same.

This is not currently used in production.

This is actually the main problem. You are writing a code to handle some imaginary queries. That's the worst idea a developer could have. One should start from writing raw API calls with raw SQL, get the idea what queries are going to be used, and only then start to think about automation.

Here is the similar review I made a while ago. Please check it out, it explains a lot of pitfalls in your code.

Besides, you may refer to my article on the common database wrapper mistakes, as your code suffer from most of them, namely:

• Flawed error reporting.
• Type-hinted binding.
• Select function
• SQL injection in Insert and Update methods
• Statefulness (what if you would need to run a nested query)
• Protected mysqli instance

In a nutshell, a good mysqli wrapper should be just a handful of lines (taken from the linked above answer):

Class DB
{
    public function __construct($host, $user, $pass, $db, $charset)
    {
        mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
        $this->mysqli = new mysqli($host, $user, $pass, $db);
        $this->mysqli->set_charset($charset);
    }

    public function query($sql, $params, $types = "")
    {
        $types = $types ?: str_repeat("s", count($params));
        $stmt = $this->mysqli->prepare($sql);
        $stmt->bind_param($types, ...$params);
        $stmt->execute();
        return $stmt;
    }
} 

while anything else should be added after strong consideration only.