File: core/src/RTmysqli.php

Recommend this page to a friend!
  Classes of No name  >  RT PHP  >  core/src/RTmysqli.php  >  Download  
File: core/src/RTmysqli.php
Role: Class source
Content type: text/plain
Description: Class source
Class: RT PHP
Library of general purpose PHP classes
Author: By
Last change:
Date: 4 years ago
Size: 16,650 bytes
 

Contents

Class file image Download
<?php

/**
 * @link https://github.com/rogertiongdev/RTphp RTphp GitHub project
 * @license https://rogertiongdev.github.io/MIT-License/
 */

namespace RTdev\RTphp;

use mysqli;

/**
 * Simple MySQLi wrapper with prepared statements.
 *
 * @version 0.1
 * @author Roger Tiong RTdev
 */
class RTmysqli {

    /**
     * The SQL statement given in last executed query.
     *
     * @var string
     */
    public $sql = '';

    /**
     * The parameter given in last executed query.
     *
     * @var array
     */
    public $param = array();

    /**
     * The duration of last executed statement (millisecond).
     *
     * @var string
     */
    public $duration = '';

    /**
     * The total number of rows changed, deleted, or inserted by the last executed statement.
     *
     * @var integer
     */
    public $affected_rows = 0;

    /**
     * ID generated from the previous INSERT operation.
     *
     * @var integer
     */
    public $insert_id = 0;

    /**
     * The number of rows in statements result set.
     *
     * @var integer
     */
    public $num_rows = 0;

    /**
     * The number of parameter for the given statement.
     *
     * @var integer
     */
    public $param_count = 0;

    /**
     * mysqli object.
     *
     * @var mysqli
     */
    protected $conn;

    /**
     * Last query start time.
     *
     * @var string
     */
    protected $startTime;

    /**
     * Configuration data
     *
     * @var array
     */
    protected $dbConfig = array(
        'host' => '',
        'username' => '',
        'password' => '',
        'name' => ''
    );

    /**
     * A variable temporary store a message to share with others methods.
     *
     * @var string
     */
    private $msgBox = '';

    /**
     * Configure database connection.
     *
     * @param string $host
     * @param string $username
     * @param string $password
     * @param string $name Database name
     */
    public function config($host, $username, $password, $name) {

        $this->dbConfig = array_combine(array_keys($this->dbConfig), array(
            (string) $host,
            (string) $username,
            (string) $password,
            (string) $name
        ));
    }

    /**
     * Configure database connection by using INI file.
     *
     * @param string $filepath
     * @param array $identifiers Keys to define host, username, password and database name.
     * @return boolean
     */
    public function configIni($filepath, $identifiers) {

        $nfp = (string) $filepath;

        if (empty($nfp) || !file_exists($nfp) || $data = parse_ini_file($nfp, TRUE) == FALSE) {

            trigger_error(sprintf('%s Given ini file path not found.', __METHOD__));
            return FALSE;
        }

        if (!is_array($identifiers) && count($identifiers) != count($this->dbConfig)) {

            $msg = '%s Expects parameter 2 to be array and have exactly 4 elements. %s given.';
            trigger_error(sprintf($msg, __METHOD__, gettype($identifiers)));
            return FALSE;
        }

        $this->dbConfig = array_combine(array_keys($this->dbConfig), array_values($data));
        return TRUE;
    }

    /**
     * Configure one of the value for database connection.
     *
     * @param string $option Option: host, username, password, name.
     * @param string $value
     * @return boolean
     */
    public function configSingle($option, $value) {

        $op = (string) $option;

        if (!array_key_exists($op, $this->dbConfig)) {

            $msg = '%s Expects parameter 1 match one of following list (%s) %s given.';
            trigger_error(sprintf($msg, __METHOD__, implode(', ', array_keys($this->dbConfig)), $op));
            return FALSE;
        }

        $this->dbConfig[$op] = (string) $value;
    }

    /**
     * Format data given by using stripslashes and real_escape_string.
     *
     * @param array|string $data
     * @param boolean $advMode True = run strip_tags, urldecode, nl2br and html_entity_decode.
     * @return array|string
     */
    public function cleaner($data, $advMode = FALSE) {

        if (is_array($data)) {

            foreach ($data as $k => $v) {

                $data[$k] = $this->cleaner($v);
            }
        }
        elseif (!is_numeric($data)) {

            if (get_magic_quotes_gpc()) {

                $data = stripslashes($data);
            }

            if ($advMode) {

                $data = strip_tags(urldecode(nl2br(html_entity_decode($data, ENT_QUOTES, 'UTF-8'))));
            }

            $data = $this->connect()->real_escape_string($data);
        }

        return $data;
    }

    /**
     * Close database connection.
     */
    public function close() {

        if (isset($this->conn)) {

            if (is_object($this->conn)) {

                $this->conn->close();
            }

            unset($this->conn);
        }
    }

    /**
     * Establish database connection.
     *
     * @return boolean|mysqli
     */
    public function connect() {

        if (!isset($this->conn)) {

            $param = array();

            foreach ($this->dbConfig as $v) {

                $param[] = (string) $v;
            }

            $this->conn = new mysqli($param[0], $param[1], $param[2], $param[3]);
        }

        if ($this->conn->connect_errno) {

            trigger_error(sprintf('%s [%s]: [%s]', __METHOD__, $this->conn->connect_errno, $this->conn->connect_error));
            die('Unable connect to database.');
        }

        if (!$this->conn->ping()) {

            trigger_error(sprintf('%s %s', __METHOD__, $this->conn->connect_error));
        }

        return $this->conn;
    }

    /**
     * Performs a query on the database by using multiple Class Methods and PHP Functions.<br>
     * Important: The following Class Method will be auto run :-<br>
     * - $this->query_prepare()<br>
     * - $this->query_bind_param()<br>
     * - $this->query_execute()<br>
     * - $this->close()<br>
     *
     * INSERT multi $param format:<br>
     * - FALSE: array(string $types, mixed &$var1 [, mixed &$... ])<br>
     * - TRUE: array(string $types, array(mixed &$var1 [, mixed &$... ]) [, array(mixed &$var1 [, mixed &$... ]) &$...])<br>
     *
     * @param string $sql
     * @param array $param Parameter for bind param
     * @param boolean $insert_multi True = run multiple insert sql
     * @return mixed
     */
    public function query($sql, $param = array(), $insert_multi = FALSE) {

        $this->startTime = microtime(TRUE);
        $results = FALSE;
        $this->sql = $sql;
        $this->param = $param;

        if (!is_string($sql) || empty($sql) || !is_array($param)) {

            $msg = '%s Expects parameter 1 to be not empty string and parameter 2 to be array. %s, %s given.';
            return $this->queryFail(sprintf($msg, __METHOD__, gettype($sql), gettype($param)));
        }

        if (!$stmt = $this->queryPrepare($sql)) {

            return $this->queryFail(sprintf('%s Prepare fail: %s', __METHOD__, $this->msgBox));
        }

        $this->param_count = $stmt->param_count;
        $bindfail = '%s Bind param fail. The number of parameter doesn\"t match the placeholders in the statement. Placeholders count: %d, Parameter count: %d, Parameter type count: %d.';

        if ($stmt->param_count <= 0) {

            $results = $this->queryExecute($stmt, $sql);
        }
        else {

            $pinfo = $this->qparamSpliter($param);

            if ($pinfo['param_count'] <= 0) {

                $msg = sprintf($bindfail, __METHOD__, $stmt->param_count, (int) $pinfo['param_count'], $pinfo['format_count']);
                return $this->queryFail($msg);
            }

            if ($insert_multi && ($sqltype = $this->getSqlType($sql)) == 'insert') {

                if ($pinfo['format_count'] != ($row1count = count(current(current($pinfo['param_arr']))))) {

                    $msg = sprintf($bindfail, __METHOD__, $stmt->param_count, $row1count, $pinfo['format_count']);
                    return $this->queryFail($msg);
                }

                $results = array();
                $param_arr = current($pinfo['param_arr']);

                foreach ($param_arr as $v) {

                    if ($row1count != count($v)) {

                        $msg = '%s Expects all array from parameter 2\'s element 2 have the same count.';
                        return $this->queryFail(sprintf($msg, __METHOD__));
                    }

                    if (!($stmt = $this->queryBindParam($stmt, $pinfo['format_chars'], $pinfo['format_str'], $v))) {

                        break;
                    }

                    $results[] = $this->queryExecute($stmt, $sql);
                }
            }
            else {

                if ($pinfo['format_count'] != $pinfo['param_count']) {

                    $msg = sprintf($bindfail, __METHOD__, $stmt->param_count, (int) $pinfo['param_count'], $pinfo['format_count']);
                    return $this->queryFail($msg);
                }

                $stmt = $this->queryBindParam($stmt, $pinfo['format_chars'], $pinfo['format_str'], $pinfo['param_arr']);
                $results = ($stmt) ? $this->queryExecute($stmt, $sql) : FALSE;
            }
        }

        $this->durationLap();
        $stmt->close();
        $this->close();
        return $results;
    }

    /**
     * Run query SHOW_TABLES
     *
     * @return array
     */
    public function SHOW_TABLES() {

        $result = array();
        $rows = $this->query('SHOW TABLES');

        foreach ($rows as $row) {

            $result[] = $row[sprintf('Tables_in_%s', $this->dbConfig['name'])];
        }

        return $result;
    }

    /**
     * Run query SHOW_COLUMNS_FROM
     *
     * @param array|string $data Table(s) name
     * @return array
     */
    public function SHOW_COLUMNS_FROM($data) {

        if (is_array($data)) {

            foreach ($data as $k => $v) {

                $data[$k] = $this->SHOW_COLUMNS_FROM($v);
            }

            return $data;
        }
        elseif (preg_match('/^[a-zA-Z0-9_-]*$/', (string) $data)) {

            $result = array();
            $rows = $this->query(sprintf('SHOW COLUMNS FROM %s', trim($this->cleaner($data))));

            foreach ($rows as $row) {

                $result[] = $row['Field'];
            }

            return $result;
        }

        trigger_error(sprintf('%s Expects parameter to be string or array. %s given', __METHOD__, gettype($data)));
        return array();
    }

    /**
     * Lap duration.
     */
    protected function durationLap() {

        $this->duration = (microtime(TRUE) - $this->startTime);
    }

    /**
     * Get SQL type.
     *
     * @param string $sql
     * @return string
     */
    protected function getSqlType($sql) {

        return strtolower(current(explode(' ', trim((string) $sql))));
    }

    /**
     * Parameter spliter.<br>
     * Split a given parameter to:<br>
     *  - $info['format_str'] parameter datatype in string format.<br>
     *  - $info['format_chars'] parameter datatype in array format. (included custom datatype 't' and 'a')<br>
     *  - $info['format_count'] Number of datatype given.<br>
     *  - $info['param_arr'] parameter without datatype in array format.<br>
     *  - $info['param_count'] Number of value given.<br>
     * 
     * This function also will duplicate the value (Element: 'format_str') and replace 't' and 'a' to s.<br>
     *
     * @param array $param Query parameter
     * @return array
     */
    protected function qparamSpliter($param) {

        $nparam = array_values($param);
        $paramKeys = array_keys($nparam);
        $info = array();

        $info['format_str'] = current($nparam);
        $info['format_chars'] = str_split($info['format_str']);
        $info['format_count'] = count($info['format_chars']);
        $info['format_str'] = str_replace(array('t', 'a'), 's', $info['format_str']);
        $info['param_arr'] = array_slice($nparam, 1, end($paramKeys));
        $info['param_count'] = count($info['param_arr']);

        return $info;
    }

    /**
     * Methods to handle failed query.
     *
     * @param string $msg
     * @return NULL
     */
    protected function queryFail($msg) {

        trigger_error($msg);
        $this->durationLap();
        return NULL;
    }

    /**
     * Prepare an SQL statement for execution.
     *
     * @param string $sql
     * @param string $method Remark for error. Default: Class Method name.
     * @return boolean|mysqli_stmt
     */
    protected function queryPrepare($sql) {

        $conn = $this->connect();

        if (!$stmt = $conn->prepare($sql)) {

            $this->msgBox = $conn->error;
            return FALSE;
        }

        return $stmt;
    }

    /**
     * Binds a parameter to the specified variable name.<br>
     * This method will automatic format the value based on type given.<br>
     * - i : implement (int).<br>
     * - d : implement (float).<br>
     * - t : implement stripslashes.<br>
     * - a : implement $this->cleaner() with advance mode.<br>
     * 
     * Default will implement $this->cleaner().<br>
     *
     * @param mysqli_stmt $stmt
     * @param array $format_chars
     * @param string $format_str
     * @param array $param_arr
     * @return boolean|mysqli_stmt
     */
    protected function queryBindParam($stmt, $format_chars, $format_str, $param_arr) {

        foreach ($param_arr as $k => $v) {

            $type = $format_chars[$k];

            switch ($type) {

                case 'i':
                    $param_arr[$k] = (int) $v;
                    break;

                case 'd':
                    $param_arr[$k] = (float) $v;
                    break;

                case 't':
                    $param_arr[$k] = stripslashes($v);
                    break;

                case 'a':
                    $param_arr[$k] = $this->cleaner($v, TRUE);
                    break;

                default: $this->cleaner($v);
            }

            $param_arr[$k] = & $param_arr[$k];
        }

        if (call_user_func_array(array($stmt, 'bind_param'), array_merge(array($format_str), $param_arr))) {

            return $stmt;
        }

        trigger_error(sprintf('%s [%s]: [%s]', __METHOD__, $stmt->errno, $stmt->error));
        return FALSE;
    }

    /**
     * Executes a query after prepared SQL OR bind_param.<br>
     * Return format:<br>
     * - INSERT, UPDATE and DELETE [integer].<br>
     * - SELECT and SHOW [array] Format: key = start from 0, value = array('field_name' => 'data', field_name2' => 'data2', ...).<br>
     *
     * @param mysqli_stmt $stmt
     * @param string $sql
     * @return mixed
     */
    protected function queryExecute($stmt, $sql) {

        if (!$stmt->execute() || $stmt->errno > 0) {

            trigger_error(sprintf('%s [%s]: [%s]', __METHOD__, $stmt->errno, $stmt->error));
            return FALSE;
        }

        $sqlType = $this->getSqlType($sql);

        if ($sqlType == 'insert') {

            return $this->insert_id = $stmt->insert_id;
        }
        elseif (in_array($sqlType, array('update', 'delete'))) {

            return $this->affected_rows = $stmt->affected_rows;
        }
        elseif (in_array($sqlType, array('select', 'show'))) {

            $stmt->store_result();

            if (($this->num_rows = $stmt->num_rows) <= 0) {

                return array();
            }

            // Get all fields name from db based on sql statement
            $flds = $stmt->result_metadata()->fetch_fields();
            $data = array();

            foreach ($flds as $fld) {

                $dataKey = $fld->name;
                $result[$dataKey] = "";

                for ($i = 1; ++$i < 999;) {

                    if (!array_key_exists($dataKey, $data)) {

                        $data[$dataKey] = &$result[$dataKey];
                        break;
                    }

                    $dataKey = $fld->name . '_' . $i;
                }
            }

            if (!call_user_func_array(array($stmt, 'bind_result'), $data)) {

                trigger_error(sprintf('%s [%s]: [%s]', __METHOD__, $stmt->errno, $stmt->error));
                return FALSE;
            }

            $row = $return = array();

            while ($stmt->fetch()) {

                foreach ($data as $k => $v) {

                    $row[$k] = stripslashes($v);
                }

                $return[] = $row;
            }

            return $return;
        }

        return NULL;
    }

}

For more information send a message to info at phpclasses dot org.