柳暗花明笔记

    DB class v2.0 use mysqli

    所属分类:[ php&web ] 猫哲学家 发表于:2015-07-22 17:02 浏览:558

    /*
    * @anthor Lanmiz Li 20150722 version 2.0
    * Use mysqli instead mysql. (php 5.5 tip)
    * change log add wirte log.
    */

    class DB
    {
    static public $db_instance = null;

    static public $debug = false;

    static public $mysqli_obj = null;

    static public $mysql_cfg = array();

    public $table = 'test';

    public static function factory($mysql_cfg = array())
    {
    // Check is change DB
    $db_conf_diff = array_diff(self::$mysql_cfg, $mysql_cfg);

    self::$mysql_cfg = $mysql_cfg;

    if ( ! empty($db_conf_diff))
    {
    // Change DB
    self::$mysqli_obj = self::obj_init();
    }

    if (empty(self::$db_instance))
    {
    self::$db_instance = new DB;
    }

    return self::$db_instance;
    }

    public static function log($mess)
    {
    if (isset(self::$mysql_cfg['log_dir']))
    {
    $log_dir = self::$mysql_cfg['log_dir'];
    }
    else
    {
    $log_dir = __DIR__ . DIRECTORY_SEPARATOR;
    }

    if (!is_writable($log_dir))
    {
    exit("Log dir: '{$log_dir}' is not writable.");
    }

    $log_file = $log_dir . 'mysql_error_' . date('Ym') . '.log';

    error_log(date('[Y-m-d H:i:s] ') . print_r($mess, 1) . PHP_EOL, 3, $log_file);
    }

    static function obj_init()
    {
    if (empty(self::$mysqli_obj))
    {
    $name = self::$mysql_cfg['db_name'];

    $host = isset(self::$mysql_cfg['host']) ? self::$mysql_cfg['host'] : '127.0.0.1';

    $user = isset(self::$mysql_cfg['user']) ? self::$mysql_cfg['user'] : 'root';

    $pass = isset(self::$mysql_cfg['pass']) ? self::$mysql_cfg['pass'] : '123456';

    self::$mysqli_obj = new mysqli($host, $user, $pass, $name);

    if (!self::$mysqli_obj)
    {
    if ($error = error_get_last())
    {
    $mess = "{$error['message']} in {$error['file']} on line {$error['line']}";

    self::log($mess);

    die($mess);
    }
    }

    self::$mysqli_obj->query("SET NAMES UTF8");

    return self::$mysqli_obj;
    }
    }

    public function find_one($where = NULL, $fields = NULL, $table = NULL)
    {
    $_fields = '*';

    if (!empty($fields))
    {
    $_fields = $fields;
    }

    if (empty($table))
    {
    $table = $this->table;
    }

    $sql = "SELECT {$_fields} FROM `{$table}` WHERE " . self::_where($where);

    $result = self::getqueryresult($sql);

    return current($result);
    }

    public function count($where = NULL, $table = NULL)
    {
    if (empty($table))
    {
    $table = $this->table;
    }

    $sql = "SELECT count(*) as n FROM `{$table}` WHERE " . self::_where($where);

    $result = self::getqueryresult($sql);

    return $result[0]['n'];
    }

    public function update($data = array(), $where = NULL, $table = NULL)
    {
    if (empty($table))
    {
    $table = $this->table;
    }

    if (!empty($data))
    {
    $update_str = '';

    foreach ($data as $k => $v)
    {
    $update_str .= "`{$k}`='".self::escape($v)."',";
    }

    $update_str = trim($update_str, ',');

    $sql = "UPDATE {$table} SET {$update_str} WHERE " . self::_where($where);

    if (self::query($sql))
    {
    return self::$mysqli_obj->affected_rows;
    }

    return FALSE;
    }

    return FALSE;
    }

    public function delete($where = NULL, $table = NULL)
    {
    if (empty($table))
    {
    $table = $this->table;
    }

    $sql = "DELETE FROM {$table} WHERE " . self::_where($where);

    if (self::query($sql))
    {
    return self::$mysqli_obj->affected_rows;
    }

    return FALSE;
    }

    public function find($limit = NULL, $offset = NULL, $where = NULL, $order_by = NULL, $fields = NULL, $table = NULL)
    {
    $sql = "SELECT ";

    // Deal fields
    if (empty($fields))
    {
    $sql .= '*';
    }
    else
    {
    $sql .= $fields;
    }

    if (empty($table))
    {
    $table = $this->table;
    }

    $sql .= " FROM `{$table}` WHERE " . self::_where($where);

    if (!empty($order_by))
    {
    $sql .= " ORDER BY {$order_by} ";
    }

    // Deal limit and offset
    if (!empty($limit) and !is_null($offset))
    {
    $sql .= "LIMIT {$offset},{$limit}";
    }

    return self::getqueryresult($sql);
    }

    static public function getqueryresult( $sql = null )
    {
    $r = array();

    // If we have more than one argument ($sql)
    if (func_num_args() > 1)
    {
    $argv = func_get_args();

    $binds = (is_array(next($argv))) ? current($argv) : array_slice($argv, 1);
    }

    // Compile binds if needed
    if (isset($binds))
    {
    $sql = self::compile_binds($sql, $binds);
    }

    if ($result = self::query($sql))
    {
    while ($row = $result->fetch_assoc())
    {
    array_push($r, $row);
    }

    $result->free_result();
    }

    return $r;
    }

    static public function query($sql = null)
    {
    self::obj_init();

    if (self::$debug)
    {
    echo $sql . '
    ';
    }

    // If we have more than one argument ($sql)
    if (func_num_args() > 1)
    {
    $argv = func_get_args();

    $binds = (is_array(next($argv))) ? current($argv) : array_slice($argv, 1);
    }

    // Compile binds if needed
    if (isset($binds))
    {
    $sql = self::compile_binds($sql, $binds);
    }

    $result = self::$mysqli_obj->query($sql);

    // self::$mysqli_obj->close();

    if ($result)
    {
    return $result;
    }
    else
    {
    $mess = self::$mysqli_obj->error . " Query: {$sql}";

    self::log($mess);

    die($mess);
    }

    return false;
    }

    static public function escape( $value = null )
    {
    self::obj_init();

    $escape_val = ($value==0) ? 0 : '';

    if (!empty($value))
    {
    $escape_val = self::$mysqli_obj->real_escape_string($value);
    }
    elseif($escape_val==0)
    {
    $escape_val = 0;
    }
    else
    {
    $escape_val = '';
    }

    return $escape_val;
    }

    public function insert($data = array(), $table = null)
    {
    self::obj_init();

    if (empty($data))
    {
    return 0;
    }

    if (empty($table))
    {
    $table = $this->table;
    }

    //array_map('self::escape', $data); // Not work
    $data_escape = array();

    foreach ($data as $k => $v)
    {
    $data_escape[$k] = self::escape($v);
    }

    $sql = "insert into `$table` ";

    $sql .= "(`". implode("`,`", array_keys($data)) ."`) values ";

    $sql .= "('". implode("','", array_values($data_escape)) ."')";

    // IF error will log
    self::query($sql);

    return self::$mysqli_obj->insert_id;
    }

    /**
    * Combine a SQL statement with the bind values. Used for safe queries.
    *
    * @param string query to bind to the values
    * @param array array of values to bind to the query
    * @return string
    */
    public function compile_binds($sql, $binds)
    {
    foreach ((array) $binds as $val)
    {
    // If the SQL contains no more bind marks ("?"), we're done.
    if (($next_bind_pos = strpos($sql, '?')) === FALSE)
    {
    break;
    }

    // Properly escape the bind value.
    if (is_int($val) or is_float($val))
    {
    $val = self::escape($val);
    }
    else
    {
    $val = "'" . self::escape($val) . "'";
    }

    //$val = "'" . self::escape($val) . "'";

    // Temporarily replace possible bind marks ("?"), in the bind value itself, with a placeholder.
    $val = str_replace('?', '?', $val);

    // Replace the first bind mark ("?") with its corresponding value.
    $sql = substr($sql, 0, $next_bind_pos).$val.substr($sql, $next_bind_pos + 1);
    }

    // Restore placeholders.
    return str_replace('?', '?', $sql);
    }

    protected function _where($where = NULL)
    {
    $where_str = '';

    if (is_string($where))
    {
    $where_str = $where;
    }
    elseif (is_array($where) and !empty($where))
    {
    foreach ($where as $w)
    {
    if (strtoupper($w[1]) == 'IN' and is_array($w[2]))
    {
    $value_escape = array();

    foreach ($w[2] as $k => $v)
    {
    $value_escape[$k] = self::escape($v);
    }

    $where_str .= $w[0] . ' IN ' . "('". implode("','", array_values($value_escape)) ."')" . ' AND ';
    }
    else
    {
    $where_str .= "{$w[0]} {$w[1]} '" . self::escape($w[2]) . "' AND ";
    }
    }
    }
    else
    {
    return '1';
    }

    return trim($where_str, 'AND ');
    }
    }

    标签: php  mysql 
    上一编文章 centos 7 开启80端口 firewall 下一编文章 php catch error use register_shutdown_function and log it class
    最近文章
    发表评论