<?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();
            }
        }
    }
}

<?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', '');

<?php
$db = new MySQLiDB;
?>

<?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);
?>

<?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' => '',
    ];
?>

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

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

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

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

<?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'];
?>

<?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);

?>

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

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