MySQL操作类-PHP源码

&＃39;;
//获取表字段
//print_r($mysql->getFields(&＃39;test&＃39;));
//增
echo $mysql->data(array(&＃39;name&＃39;=>&＃39;test&＃39;,&＃39;password&＃39;=>&＃39;123456&＃39;))->table(&＃39;test&＃39;)->add();
//删
echo $mysql->table(&＃39;test&＃39;)->where(&＃39;id=1&＃39;)->delete();
//改
echo $mysql->table(&＃39;test&＃39;)->data(array(&＃39;name&＃39;=>&＃39;bbbbbbbbbbbb&＃39;))->where(&＃39;id<3&＃39;)->update();
//查
print_r($mysql->table(&＃39;test&＃39;)->where(&＃39;id=4&＃39;)->select());
print_r($mysql->table(&＃39;test&＃39;)->order(&＃39;id desc&＃39;)->select());
//
$mysql->query(&＃39;select * from `test`&＃39;);
$mysql->execute(&＃39;update `test` set password = 123&＃39;);
echo &＃39;

&＃39;; echo &＃39;查询次数:&＃39;.$mysql->query_count.&＃39;
&＃39;; echo &＃39;查询时间:&＃39;.number_format(microtime(true)-($mysql->query_start_time),10).&＃39; 秒
&＃39;; echo &＃39;错误信息:&＃39;.$mysql->error().&＃39;
&＃39;; ?>

2. mysql.class.php

db_mysql_hostname = $hostname_or_conf[&＃39;hostname&＃39;];
            $this->db_mysql_username = $hostname_or_conf[&＃39;username&＃39;];
            $this->db_mysql_password = $hostname_or_conf[&＃39;password&＃39;];
            $this->db_mysql_database = $hostname_or_conf[&＃39;database&＃39;];
            $this->db_mysql_port = isset($hostname_or_conf[&＃39;port&＃39;])?$hostname_or_conf[&＃39;port&＃39;]:&＃39;3306&＃39;;
            $this->db_mysql_charset = isset($hostname_or_conf[&＃39;charset&＃39;])?$hostname_or_conf[&＃39;charset&＃39;]:&＃39;utf8&＃39;;
            
        }elseif(!empty($hostname_or_conf)||!empty($username)||!empty($password)||!empty($database))
        {
             $this->db_mysql_hostname = $hostname_or_conf;
             $this->db_mysql_username = $username;
             $this->db_mysql_password = $password;
             $this->db_mysql_database = $database;
             $this->db_mysql_port = $port;
             $this->db_mysql_charset = $char;
             
        }else{
            die(&＃39;configuration error.&＃39;);
        }
        $this->connect();
    }
    
    private function connect(){
        $server = $this->db_mysql_hostname.&＃39;:&＃39;.$this->db_mysql_port;
        $this->cOnn= mysql_connect($server,$this->db_mysql_username,$this->db_mysql_password,true) or die(&＃39;Connect MySQL DB error!&＃39;);
        mysql_select_db($this->db_mysql_database,$this->conn) or die(&＃39;select db error!&＃39;);
        mysql_query("set names " . $this->db_mysql_charset, $this->conn);
    }
    /**
     +----------------------------------------------------------
     * 设置数据对象值
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     *table,where,order,limit,data,field,join,group,having
     +----------------------------------------------------------
     */
    public function table($table){
        $this->query_list[&＃39;table&＃39;] = $table;
        return $this;
    }
    
    public function where($where){
        $this->query_list[&＃39;where&＃39;] = $where;
        return $this;
    }
    
    public function order($order){
        $this->query_list[&＃39;order&＃39;] = $order;
        return $this;
    }
    
    public function limit($offset,$length){
        if(!isset($length)){
            $length = $offset;
            $offset = 0;
        }
        $this->query_list[&＃39;limit&＃39;] = &＃39;limit &＃39;.$offset.&＃39;,&＃39;.$length;
        return $this;
    }
    
    public function data($data){
        /*
        if(is_object($data)){
            $data   =   get_object_vars($data);
        }elseif (is_string($data)){
            parse_str($data,$data);
        }elseif(!is_array($data)){
            //Log:DATA_TYPE_INVALID
        }
        */
        $this->query_list[&＃39;data&＃39;] = $data;
        return $this;
    }
    public function field($fields){
        $this->query_list[&＃39;fields&＃39;] = $fields;
        return $this;
    }
    public function join($join){
        $this->query_list[&＃39;join&＃39;] = $join;
        return $this;
    }
    public function group($group){
        $this->query_list[&＃39;group&＃39;] = $group;
        return $this;
    }
    public function having($having){
        $this->query_list[&＃39;having&＃39;] = $having;
        return $this;
    }
    /**
     +----------------------------------------------------------
     * 查询
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     * @param 
     +----------------------------------------------------------
     */
    public function select(){
        $select_sql = &＃39;select &＃39;;
        $fields = isset($this->query_list[&＃39;fields&＃39;])?$this->query_list[&＃39;fields&＃39;]:&＃39;*&＃39;;
        $select_sql.=$fields;
        $select_sql.= &＃39; from `&＃39;.$this->query_list[&＃39;table&＃39;].&＃39;` &＃39;;
        
        isset($this->query_list[&＃39;join&＃39;])?($select_sql.=$this->query_list[&＃39;join&＃39;]):&＃39;&＃39;;
        isset($this->query_list[&＃39;where&＃39;])?($select_sql.=&＃39; where &＃39;.$this->query_list[&＃39;where&＃39;]):&＃39;&＃39;;
        isset($this->query_list[&＃39;group&＃39;])?($select_sql.=&＃39; group by&＃39;.$this->query_list[&＃39;group&＃39;]):&＃39;&＃39;;
        isset($this->query_list[&＃39;having&＃39;])?($select_sql.=&＃39; mysql having &＃39;.$this->query_list[&＃39;having&＃39;]):&＃39;&＃39;;
        isset($this->query_list[&＃39;order&＃39;])?($select_sql.=&＃39; order by &＃39;.$this->query_list[&＃39;order&＃39;]):&＃39;&＃39;;
        isset($this->query_list[&＃39;limit&＃39;])?($select_sql.=&＃39; &＃39;.$this->query_list[&＃39;limit&＃39;]):&＃39;&＃39;;
        
        return $this->query($select_sql);
    }
    /**
     +----------------------------------------------------------
     * 增加
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     * @param 
     +----------------------------------------------------------
     */
    public function add(){
        $add_sql = &＃39;insert into `&＃39;.$this->query_list[&＃39;table&＃39;].&＃39;` (&＃39;;
        
        $data = $this->query_list[&＃39;data&＃39;];
        $value = $field = &＃39;&＃39;;
        foreach($data as $k=>$v){
            $field .= &＃39;`&＃39;.$k.&＃39;`,&＃39;;
            if(is_numeric($v))
                $value .= $v.&＃39;,&＃39;;
            else
                $value .= &＃39;\&＃39;&＃39;.$v.&＃39;\&＃39;,&＃39;;
        }
        $add_sql .= rtrim($field,&＃39;,&＃39;).&＃39;) values (&＃39;.rtrim($value,&＃39;,&＃39;).&＃39;)&＃39;;
        return $this->execute($add_sql);
    }
    /**
     +----------------------------------------------------------
     * 删除
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     * @param 
     +----------------------------------------------------------
     */
    public function delete(){
        $del_sql = &＃39;delete from `&＃39;.$this->query_list[&＃39;table&＃39;].&＃39;` where &＃39;.$this->query_list[&＃39;where&＃39;];
        
        if(isset($this->query_list[&＃39;order&＃39;]))
            $del_sql .= &＃39;order by &＃39;.$this->query_list[&＃39;order&＃39;];
        if(isset($this->query_list[&＃39;limit&＃39;]))
            $del_sql .= &＃39; &＃39;.$this->query_list[&＃39;limit&＃39;];
            
        return $this->execute($del_sql);
        
    }
    /**
     +----------------------------------------------------------
     * 更新
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     * @param 
     +----------------------------------------------------------
     */
    public function update(){
        $update_sql = &＃39;update `&＃39;.$this->query_list[&＃39;table&＃39;].&＃39;` set &＃39;;
        $data = $this->query_list[&＃39;data&＃39;];
        
        foreach($data as $k=>$v){
            if(is_numeric($v))
                $update_sql .= &＃39;`&＃39;.$k.&＃39;` =&＃39;.$v.&＃39;,&＃39;;
            else
                $update_sql .= &＃39;`&＃39;.$k.&＃39;` =\&＃39;&＃39;.$v.&＃39;\&＃39;,&＃39;;
        }
        $update_sql = rtrim($update_sql,&＃39;,&＃39;);
        if(isset($this->query_list[&＃39;where&＃39;]))
            $update_sql .= &＃39; where &＃39;.$this->query_list[&＃39;where&＃39;];
        if(isset($this->query_list[&＃39;order&＃39;]))
            $update_sql .= &＃39; order by &＃39;.$this->query_list[&＃39;order&＃39;];
        if(isset($this->query_list[&＃39;limit&＃39;]))
            $update_sql .= &＃39; &＃39;.$this->query_list[&＃39;limit&＃39;];
        
        return $this->execute($update_sql);
        
    }
     /**
     +----------------------------------------------------------
     * 执行查询 返回数据集
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     * @param string $sql  sql指令
     */
    public function query($sql) {
        if ( !$this->conn ) return false;
        $this->queryStr = $sql;
        //释放前次的查询结果
        if ( $this->queryID ) {    $this->free();    }
        
        $this->query_start_time = microtime(true);
        
        $this->queryID = mysql_query($sql, $this->conn);
        $this->query_count++;
        if ( false === $this->queryID ) {
            $this->error();
            return false;
        } else {
            $this->numRows = mysql_num_rows($this->queryID);
            return $this->getAll();
        }
    }
    /**
     +----------------------------------------------------------
     * 执行语句
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     * @param string $sql  sql指令
     +----------------------------------------------------------
     */
    public function execute($sql) {
        if ( !$this->conn ) return false;
        $this->queryStr = $sql;
        //释放前次的查询结果
        if ( $this->queryID ) {    $this->free();    }
        
        $this->query_start_time = microtime(true);
        
        $result =   mysql_query($sql, $this->conn) ;
        $this->query_count++;
        if ( false === $result) {
            $this->error();
            return false;
        } else {
            $this->numRows = mysql_affected_rows($this->conn);
            return $this->numRows;
        }
    }
    /**
     +----------------------------------------------------------
     * 获得所有的查询数据
     +----------------------------------------------------------
     * @access private
     +----------------------------------------------------------
     * @return array
     */
    private function getAll() {
        //返回数据集
        $result = array();
        if($this->numRows >0) {
            while($row = mysql_fetch_assoc($this->queryID)){
                $result[]   =   $row;
            }
            mysql_data_seek($this->queryID,0);
        }
        return $result;
    }
    /**
     +----------------------------------------------------------
     * 取得数据表的字段信息
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     */
    public function getFields($tableName) {
        $result =   $this->query(&＃39;SHOW COLUMNS FROM `&＃39;.$tableName.&＃39;`&＃39;);
        $info   =   array();
        if($result) {
            foreach ($result as $key => $val) {
                $info[$val[&＃39;Field&＃39;]] = array(
                    &＃39;name&＃39;    => $val[&＃39;Field&＃39;],
                    &＃39;type&＃39;    => $val[&＃39;Type&＃39;],
                    &＃39;notnull&＃39; => (bool) ($val[&＃39;Null&＃39;] === &＃39;&＃39;), // not null is empty, null is yes
                    &＃39;default&＃39; => $val[&＃39;Default&＃39;],
                    &＃39;primary&＃39; => (strtolower($val[&＃39;Key&＃39;]) == &＃39;pri&＃39;),
                    &＃39;autoinc&＃39; => (strtolower($val[&＃39;Extra&＃39;]) == &＃39;auto_increment&＃39;),
                );
            }
        }
        return $info;
    }
    /**
     +----------------------------------------------------------
     * 取得数据库的表信息
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     */
    public function getTables($dbName=&＃39;&＃39;) {
        if(!empty($dbName)) {
           $sql    = &＃39;SHOW TABLES FROM &＃39;.$dbName;
        }else{
           $sql    = &＃39;SHOW TABLES &＃39;;
        }
        $result =   $this->query($sql);
        $info   =   array();
        foreach ($result as $key => $val) {
            $info[$key] = current($val);
        }
        return $info;
    }
    /**
     +----------------------------------------------------------
     * 最后次操作的ID
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     * @param 
     +----------------------------------------------------------
     */
     public function last_insert_id(){
        return mysql_insert_id($this->conn);
    }
    /**
     * 执行一条带有结果集计数的
     */
    public function count($sql){
        return $this->execute($sql);
    }
    /**
     +----------------------------------------------------------
     * 启动事务
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     * @return void
     +----------------------------------------------------------
     */
    public function startTrans() {
        if ($this->transTimes == 0) {
            mysql_query(&＃39;START TRANSACTION&＃39;, $this->conn);
        }
        $this->transTimes++;
        return ;
    }
    /**
     +----------------------------------------------------------
     * 提交事务
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     * @return boolen
     +----------------------------------------------------------
     */
    public function commit()
    {
        if ($this->transTimes > 0) {
            $result = mysql_query(&＃39;COMMIT&＃39;, $this->conn);
            $this->transTimes = 0;
            if(!$result){
                throw new Exception($this->error());
            }
        }
        return true;
    }
    /**
     +----------------------------------------------------------
     * 事务回滚
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     * @return boolen
     +----------------------------------------------------------
     */
    public function rollback()
    {
        if ($this->transTimes > 0) {
            $result = mysql_query(&＃39;ROLLBACK&＃39;, $this->conn);
            $this->transTimes = 0;
            if(!$result){
                throw new Exception($this->error());
            }
        }
        return true;
    }
    /**
     +----------------------------------------------------------
     * 错误信息
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     * @param 
     +----------------------------------------------------------
     */
     public function error() {
        $this->error = mysql_error($this->conn);
        if(&＃39;&＃39; != $this->queryStr){
            $this->error .= "\n [ SQL语句 ] : ".$this->queryStr;
        }
        return $this->error;
    }
    /**
     +----------------------------------------------------------
     * 释放查询结果
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     */
    public function free() {
        @mysql_free_result($this->queryID);
        $this->queryID = 0;
        $this->query_list = null;
    }
    /**
     +----------------------------------------------------------
     * 关闭连接
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     * @param 
     +----------------------------------------------------------
     */
    function close(){
        if ($this->conn && !mysql_close($this->conn)){
            throw new Exception($this->error());
        }
        $this->cOnn= 0;
        $this->query_count = 0;
    }
    /**
     +----------------------------------------------------------
     * 析构方法
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     */
    function __destruct(){
         $this->close();
    }
}