php操作 mysql 单例模式

<?php

/**
* author zcs
* version 2.1
*/


class mysql{
    
    var $max_cache_time        = 86400; // 最大的缓存时间，以秒为单位,默认24h
    var $cache_dir            = '/cache/';
    var $_dbhash            = '';
    var $setting            = array();
    var $dblink                = NULL;
    var $trans_depth        = 0;        //事务开启记录
    var $query                = null;    //查询句柄
    var $query_str            = '';    //记录最后一次查询的语句
    static private $_instance = NULL;    //储存对象
    var $mysql_disable_cache_tables = array(); // 不允许被缓存的表，遇到将不会进行缓存


    //$dbhost = 'localhost', $dbuser = 'root', $dbpwd = '', $dbname = 'test', $dbcharset = 'utf8', $pconnect = 0
    //私有化构造函数
    private function __construct(){
        $num_args=func_num_args();
        if($num_args > 0){
            $this->setting = func_get_args();
            $this->_initialize();
        } 

    }
    
    //防止被克隆
    private function __clone(){}
    
    //初始化函數
    private function _initialize()
    {
        try {
            if($this->setting[5]){
                if(!($this->dblink = @mysql_pconnect($this->setting[0], $this->setting[1], $this->setting[2]))){
                    throw new Exception($this->error_message('鏈接數據庫失敗'));
                }
            }else{
                if(!($this->dblink = @mysql_connect($this->setting[0], $this->setting[1], $this->setting[2]))){
                    throw new Exception($this->error_message('鏈接數據庫失敗'));
                }
            }
            
            if($this->select_db($this->setting[3], $this->dblink) === false) {
                throw new Exception($this->error_message());
            }
            //$this->cache_dir = ROOT_PATH. $this->cache_dir;
            //$this->_dbhash = md5(ROOT_PATH . $this->setting[0] . $this->setting[1] . $this->setting[2] . $this->setting[3]);
            mysql_query("SET NAMES '".$this->setting[4]."'");
            
        }catch(Exception $e){
            echo $e->getMessage();
        }
    }
    
    public static function connect($dbhost = 'localhost', $dbuser = 'root', $dbpwd = '', $dbname = '', $dbcharset = 'utf8', $pconnect = 0)
    {
        if(false == (self::$_instance instanceof self)){
            self::$_instance = new self($dbhost, $dbuser, $dbpwd, $dbname, $dbcharset, $pconnect);
        }
        return self::$_instance;

    }
    

    
    /**
     * 插入数据 
     *
     * @access    public
     * @param    string    $table
     * @param   array    $field_values    array('field'=>values)
     * @return    mixed
     */
    public function insert($table, $field_values, $escape = false, $replace = false) {
        $fields = array ();
        $values = array ();
        $field_names = $this->get_column('DESC '.$table);

        if(is_array($field_values)){
            foreach ( $field_names as $value ) {
                if (array_key_exists($value, $field_values ) == true) {
                    $fields[] = $value;
                    $values[] = $escape ? "'" . $this->escape_str($field_values[$value]) . "'" : "'" . $field_values[$value] . "'";
                }
            }
        }
        if ($fields && $values) {
            $sql = ($replace ? 'REPLACE' : 'INSERT') . ' INTO '.$table.' ('.implode(',',$fields).') VALUES ('.implode( ',',$values ).')';
            if($this->query($sql)){
                return $this->affected_rows();
            }
        }
        return false;
    }


    
    /**
     * 删除数据/ $where数组的时候默认是 AND 关系 要是实现更复杂的关系则传入字符串
     *
     * @access    public
     * @param    string    $table
     * @param   mixed    $where
     * @return    mixed
     */
    public function delete($table, $where = null, $escape = false){
        $sql = 'DELETE FROM '.$table;
        if(!empty($where)){
            if(is_array($where)){
                $arr = array();
                foreach($where as $field => $val){
                    $arr[] = $field . ' = ' . $escape ? $this->escape_str($val) : $val;
                }
                if($arr){
                    $where_str = implode(' AND ', array_unique($arr));
                    $sql .= ' WHERE '. $where_str;
                }
            }else{
                $sql .= ' WHERE ' .$escape ? $this->escape_str($where) : $where;
            }
        }
        if($this->query($sql)){
            return $this->affected_rows();
        }else{
            return false;
        }
    }

    
    /**
     *$where数组的时候默认是 AND 关系 要是实现更复杂的关系则传入字符串
     *
     * @access    public
     * @param    string    $table
     * @param   array    $field_values    array('field'=>values)
     * @param    mixed    $where
     * @return    mixed    
     */
    public function update($table, $field_values, $where = null, $escape = false) {
        $field_names = $this->get_column( 'DESC ' . $table );
        $sets = array ();
        if(is_array($field_values)){
            foreach($field_names as $value ){
                if (array_key_exists($value, $field_values ) == true) {
                    $sets[] = $value . " = '" . $escape ? $this->escape_str($field_values[$value]) : $field_values[$value] . "'";
                }
            }
        }

        if(!empty($sets)){
            $sql = 'UPDATE ' . $table . ' SET ' . implode( ', ', $sets );

            if(is_array($where)){
                $arr = array();
                foreach($where as $field => $val){
                    $arr[] = $field . ' = ' . $escape ? $this->escape_str($val) : $val;
                }
                if($arr){
                    $where_str = implode(' AND ', array_unique($arr));
                    $sql .= ' WHERE '. $where_str;
                }
            }else{
                $sql .= ' WHERE '. $escape ? $this->escape_str($where) : $where;
            }
        }else{
            return false;
        }

        if($sql && $this->query($sql)){
            return $this->affected_rows();
        }else{
            return false;
        }
        
    }


    /**
     * 查詢数据 
     *
     * @access    public
     * @param    string    $table
     * @param   string    $fields
     * @return    mixed
     */
    public function find($table, $fields = '*', $where = null, $options = array(), $escape = false)
    {
        $sql = 'SELECT '.$fields.' FROM '. $table;
        if($where != null)
        {
            if(is_array($where)){
                $arr = array();
                foreach($where as $field => $val){
                    $arr[] = $field . ' = ' . $escape ? $this->escape_str($val) : $val;
                }
                if($arr){
                    $where_str = implode(' AND ', array_unique($arr));
                    $sql .= ' WHERE '. $where_str;
                }
            }else{
                $sql .= ' WHERE '. $escape ? $this->escape_str($where) : $where;
            }
        }
        if(isset($options['order_by']))
        {
            $sql .= ' ORDER BY '.$options['order_by'];
            if(isset($options['order_dir']))
            {
                $sql .= ' '.strtoupper($options['order_dir']);
            }
        }
        if(isset($options['limit_start']) && isset($options['limit']))
        {
            $sql .= ' LIMIT '.$options['limit_start'].', '.$options['limit'];
        }
        elseif(isset($options['limit']))
        {
            $sql .= ' LIMIT '.$options['limit'];
        }
        return $this->query($sql);
    } 

    
    /**
     *
     * @param  string $sql_statement
     * @param  $index_key是否以主鍵形式返回
     * @param  $result_type
     * @return array
     */
    function result_array($arg, $index_key = false)
    {
        $rtn = array();
        //若是sql查詢語句
        if(is_string($arg)){
            $query = $this->query($arg);
        }elseif(is_resource($arg)){
            $query = $arg;
        }else{
            return $rtn;
        }
        
        while ($row = $this->fetch_assoc($query)) {
            if($index_key == false){
                $rtn[] = $row;
            } elseif (is_array($index_key)) {
                $index = '';
                foreach ($index_key as $k) {
                    if(isset($row[$k])){
                        $index .= $index == '' ? $row[$k] : '_' . $row[$k];
                    }else{
                        return $rtn;
                    }
                }
                $rtn[$index] = $row;
            } else {
                if(isset($row[$index_key])){
                    $rtn[$row[$index_key]] = $row;
                }else{
                    return $rtn;
                }
            }
        }
        return $rtn;
    }


    /**
     *
     * @param  string $sql_statement
     * @param  $index_key是否以主鍵形式返回
     * @return object
     */
    function result_object($arg, $index_key = false)
    {
        $rtn = array();
        //若是sql查詢語句
        if(is_string($arg)){
            $query = $this->query($arg);
        }elseif(is_resource($arg)){
            $query = $arg;
        }else{
            return $rtn;
        }
        
        while ($row = $this->fetch_object($query)) {
            if($index_key == false){
                $rtn[] = $row;
            } elseif (is_array($index_key)) {
                $index = '';
                foreach ($index_key as $k) {
                    if(isset($row->$k)){
                        $index .= $index == '' ? $row->$k : '_' . $row->$k;
                    }else{
                        return $rtn;
                    }
                }
                $rtn[$index] = $row;
            } else {
                if(isset($row->$index_key)){
                    $rtn[$row->$index_key] = $row;
                }else{
                    return $rtn;
                }
            }
        }
        return $rtn;
    }
    
    /*
     * 发送一条 MySQL指令
     */
    public function query($sql)
    {
        $this->query_str = $sql;
        if(!($this->query = mysql_query($this->query_str, $this->dblink))){
            echo $this->error_message();
            return false;
        }
        return $this->query;
    }


    /**
     * mysql_fetch_array  MYSQL_NUM  MYSQL_ASSOC  MYSQL_BOTH
     */
    public function fetch_array($query, $result_type = MYSQL_ASSOC)
    {
        return mysql_fetch_array($query, $result_type);
    }

    /**
     * mysql_fetch_assoc
     */
    public function fetch_assoc($query)
    {
        return mysql_fetch_assoc($query);
    }

    /**
     * mysql_fetch_object
     */
    public function fetch_object($query)
    {
        return mysql_fetch_object($query);
    }
    
    /**
     * mysql_fetch_row
     */
    public function fetch_row($query)
    {
        return mysql_fetch_row($query);

    }


    
    //获取列
    public function get_column($sql) {
        $res = $this->query($sql);
        if($res !== false) {
            $arr = array();
            $row = $this->fetch_row($res);
            while ($row) {
                $arr [] = $row[0];
                $row = $this->fetch_row($res);
            }
            return $arr;
        }else{
            return false;
        }
    }
    
    function select_db($dbname)
    {
        return mysql_select_db($dbname, $this->dblink);
    }

    //影响行数 delete insert
    public function affected_rows()
    {
        return @mysql_affected_rows($this->dblink);
    }
    
    //影响行数 select
    public function num_rows($res)
    {
        return @mysql_num_rows($res);
    }
    
    //自增插入ID
    public function insert_id()
    {
        return @mysql_insert_id($this->dblink);
    }

    //最后查询一次的sql
    public function last_query()
    {
        return $this->query_str;
    }
    
    

    /**
     * 转义字符串或数组
     * @param    bool  $str    是否使用做like条件的时候
     * @return    string or 数据
     */
    public function escape_str($str, $like = false)
    {
        if(is_array($str)){
            foreach ($str as $key => $val){
                $str[$key] = $this->escape_str($val, $like);
               }
               return $str;
           }

        if(function_exists('mysql_real_escape_string') && is_resource($this->dblink)){
            $str = mysql_real_escape_string($str, $this->dblink);
            
        }elseif(function_exists('mysql_escape_string')){
            $str = mysql_escape_string($str);
            
        }else{
            $str = addslashes($str);
        }

        // escape LIKE condition wildcards
        if ($like === true){
            $str = str_replace(array('%', '_'), array('\\%', '\\_'), $str);
        }

        return $str;
    }
    
    /**
     * 开始一个事务.
     */
    public function begin(){
        if ($this->_trans_depth > 0){
            $this->_trans_depth += 1;
            return;
        }

        mysql_query('SET AUTOCOMMIT=0', $this->dblink);
        mysql_query('START TRANSACTION', $this->dblink);
    }

    /**
     * 提交一个事务.
     */
    public function commit(){
        if ($this->_trans_depth > 1){
            $this->_trans_depth -= 1;
            return true;
        }
        mysql_query('COMMIT', $this->dblink);
    }

    /**
     * 回滚一个事务.
     */
    public function rollback(){
        if ($this->_trans_depth > 0){
            return true;
        }
        mysql_query('ROLLBACK', $this->dblink);
        return true;
    }
    

    /*创建像这样的查询: "IN('a','b')";
     * @param    mix      $item_list      列表数组或字符串,如果为字符串时,字符串只接受数字串
     * @param    string   $field_name     字段名称
    */
    function db_create_in($item_list, $field_name = '')
    {
        if(empty($item_list)) {
            return $field_name . " IN ('') ";
        }else{
            if(!is_array($item_list)){
                $item_list = explode(',', $item_list);
                foreach ($item_list as $k => $v) {
                    $item_list[$k] = $v;
                }
            }
            $item_list = array_unique($item_list);
            $item_list_tmp = '';

            foreach($item_list as $item){
                if($item !== ''){
                    $item_list_tmp .= $item_list_tmp ? ",'$item'" : "'$item'";
                }
            }
            if(empty($item_list_tmp)){
                return $field_name . " IN ('') ";
            }else{
                return $field_name . ' IN (' . $item_list_tmp . ') ';
            }
        }
    }


    //错误输出
    function error_message($message = '')
    {
        if($message == ''){
            $message = @mysql_error($this->dblink);
        }
        return $message;
    }
    
    
    public function free_result()
    {
        return mysql_free_result($this->query);
    }
    
    public function close(){
        return mysql_close($this->dblink);
    }
    
        
        
    
    
}



/* End of file mysql.class.php */


$db = mysql::connect($dbhost = 'localhost', $dbuser = 'root', $dbpwd = '', $dbname = 'test', $dbcharset = 'utf8', $pconnect = 0);

$query = $db->find('pl_user', '*', null, array('order_by'=>'user_auto_id','limit'=>'5'));
$arr = $db->result_array($query);
//or
$arr = $db->result_array('select * from pl_user limit 5');

var_dump($arr);

 

 

转载于:https://www.cnblogs.com/timily/p/3668332.html