第四周作业 2014-8-13 崔景宇

<?php
   
/**
* mysqli 查询数据库;
* @Author Cui;
* @Data 2014/08/05
*/
class db_mysqli
{
    # mysqli 类;
    private $db;
       
    # 表;
    private $sqlTables;
       
    # where条件;
    private $sqlWhere;
       
    # 数据库字段;
    private $sqlFields;
       
    # 排序;
    private $sqlOrder;
       
    # 分页;
    private $sqlLimit;
       
    # prepare;
    private $sqlPrepare;
   
    # 数据;
    private $data;
   
    # 最后的sql语句;
    private $sql;
       
    # 预查询对象;
    private $stmt;    
   
    # 错误信息;
    private $error;
   
   
    # 构造方法;
    public function __construct( $connect ) {
   
        if ( !extension_loaded( 'mysqli' ) ) {
            exit( 'Mysqli Unload!' );
        }
   
        @$this->db = new mysqli( $connect['host'], $connect['user'], $connect['password'] );
   
        if ( mysqli_connect_errno() ) { 
            exit( mysqli_connect_error() );            
        }    
           
        $this->db->select_db( $connect['db'] );
        $this->db->set_charset( $connect['charset'] );
   
    }
   
    # 魔术方法;
    public function __invoke ( $tables = '' ) {
        $this->table( $tables );
        return $this;
    }
   
    public function __call( $method = "", $var = "" ) {
        exit( '你调用了一个未定义的方法'.$method.'!' );
    }
   
    # 设置表;
    public function table( $tables ) {
        if ( is_string( $tables ) ) {
            $tables = array( $tables );
        }
        $this->sqlTables = $tables;
        return $this;
    }
   
    # 设置字段信息;
    public function field( $fields = '' ) {
        if ( is_string( $fields ) ) {
            $fields = array( $fields );
        }
   
        if ( !is_array( reset( $fields ) ) ) {
            $fields = array( $fields );
        }
   
        $this->sqlFields = $fields;
        return $this;
    }
   
    # 设置where条件;
    public function where( $where = '' ) {
        if ( is_string( $where ) ) {
            $where = array( $where );
        }
   
        $this->sqlWhere = $where;
        return $this;
    }
   
    # 设置排序;
    public function order( $order = '' ) {
        if ( is_string( $order ) ) {
            $order = array( $order );
        }    
   
        $this->sqlOrder = $order;
        return $this;
    }
   
    # 设置排序;
    public function limit( $limit = '' ) {    
        if ( is_string( $limit ) || is_integer( $limit ) ) {
            $limit = array( $limit );
        }
   
        $this->sqlLimit = $limit;
        return $this;
    }
   
    # 连表方法;
    public function join( $on = '' ) {
        /*暂无*/
    }
   
    # 查询方法;
    public function select() {
        return $this->core( 'select' );
    }
   
    # insert方法;
    public function add( $data  = '' ) {
        $this->data = $data;
        return $this->core( 'add' );
    }
   
    # updata方法;
    public function save( $data = '' ) {
        $this->data = $data;
        return $this->core( 'save' );
    }
   
    # delete方法;
    public function delete() {
        return $this->core( 'delete' );
    }
   
    # 预处理方法:
    public function prepare() {
        $var = func_get_args();
        if ( count( $var ) < 1 )return $this;
   
        if ( count( $this->sqlTables ) <= 1 ) {
            if ( count($var) >= 1 && !is_array( reset($var) ) ) {
                $var = array( $var );
            }
        }
   
        if ( count( $this->sqlTables ) >1 && !is_array( reset($var) ) ) {
            exit ("预处理参数传入不正确!");
            return false;
        }
   
        # 取类型;
        foreach ( $var as $key => $value ) {
            $type = array_map( function( $v ){
                return substr( gettype( $v ),0,1);
            }, $value);
            array_unshift( $var[$key], implode( $type ) );
        }
   
        $this->sqlPrepare = $var;
        return $this;
    }
   
    # 开启事务;
    public function startTrans( $flag ) {
        if ( !is_bool( $flag ) ) {
            $this->error = '事务开启参数错误';
            return false;
        }
        return $this->db->autocommit( !$flag );
    } 
   
    # 提交事务;
    public function commit() {
        $res = $this->db->commit();
        $this->startTrans( false );
        return $res;
    }
   
    # 回滚事务;
    public function rollback() {
        $res = $this->db->rollback();
        $this->startTrans( false );
        return $res;
    }
   
    # 核心方法;
    private function core( $action ) {
   
        $tables = $this->sqlTables;
   
        if ( empty( $tables ) ) {
            $this->error = "未定义查询表;";
            return false;
        }
   
        switch ( $action ) {
            case 'select':
                   
                $sql = $this->build_select_sql();
                   
                break;
               
            case 'save':
   
                $data = $this->get_write_data();
                   
                if ( !$data ) {
                    return false;
                }
   
                if ( false === $this->check_where() ) {
                    return false;
                }
   
                $sql = $this->build_update_sql( $data );
                   
                break;
   
            case 'add':
                   
                $data = $this->get_write_data();
                   
                if ( !$data ) {
                    return false;
                }
   
                $sql = $this->build_insert_sql( $data );
                   
                break;
               
            case 'delete':
                   
                if ( false === $this->check_where() ) {
                    return false;
                }
   
                $sql = $this->build_delete_sql();
   
                break;
        }
   
        if ( count( $sql ) <= 1 ) {
   
            $sql  = reset( $sql );
               
            if ( is_array( $this->sqlPrepare ) && !empty( $this->sqlPrepare  ) ) {
                $result = $this->execute( $sql );
            } else {
                $result = $this->query( $sql );
            }
               
        } else {
               
            if ( is_array( $this->sqlPrepare ) && !empty( $this->sqlPrepare  ) ) {
                exit('暂不支持多语句预处理;');
            } else {
                $result = $this->multi_query( $sql );
            }
        }
   
        if ( false === $result ) {
            $this->error = $this->db->error ?: $this->stmt->error;
            return false;
        }
   
        return $this->get_result( $result, $action );
   
    }
   
    # 构建sql语句;
    private function build_select_sql( ) {
           
        $tables = $this->sqlTables;
        $fields = $this->sqlFields;
        $where = $this->sqlWhere;
        $order = $this->sqlOrder;
        $limit = $this->sqlLimit;
   
        $sql = array();
        foreach ( $tables as $key => $table ) {
            $sql_tmp = '';
            $sql_tmp .= 'SELECT ';
            if ( !isset( $fields[$key] ) ) {
                 $fields[$key] = '*';
            }
            if ( is_array( $fields[$key] ) ) {
                array_walk( $fields[$key], function( &$v ) {
                    $v = '`'.$v.'`';
                });
                $fields[$key] = implode( ' , ', $fields[$key] );
            }
            $sql_tmp .= $fields[$key];
            $sql_tmp .= ' FROM '.'`'.$table.'`';
   
            !empty( $where[$key] ) && $sql_tmp .= ' WHERE '.$where[$key];
            !empty( $order[$key] ) && $sql_tmp .= ' ORDER BY '.$order[$key];
            !empty( $limit[$key] ) && $sql_tmp .= ' LIMIT '.$limit[$key];
               
            $sql[$key] = $sql_tmp;
        }
           
        return $sql;
       
    }
   
    private function build_insert_sql( $data ) {
        $tables = $this->sqlTables;
        $sql = array();
        foreach ( $tables as $key => $table ) {
               
            $sql_tmp = '';            
            $sql_tmp .= 'INSERT INTO '.'`'.$table.'`';
               
            $col = array_keys( $data[$key] );
            $callFunc = 
            array_walk( $col, function ( &$v ) {
                $v = '`'.$v.'`';
            } );            
            $col = ' ( '.implode( ' , ', $col ).' ) ';
               
            $val= array_values( $data[$key] );            
            array_walk( $val, function ( &$v ) {
                $v = $v == '?' ? $v : '\''.$v.'\'' ;
            } );            
            $val = ' ( '.implode( ' , ', $val ).' ) ';
               
            $sql_tmp .= $col;
            $sql_tmp .= ' VALUES '.$val;
            $sql[$key] = $sql_tmp;
        }
        return $sql;
    }
   
    private function build_update_sql( $data ) {
        $tables = $this->sqlTables;
        $where = $this->sqlWhere;
        $sql = array();
        foreach ( $tables as $key => $table ) {
            $sql_tmp = '';
            $sql_tmp .= 'UPDATE '.'`'.$table.'`';
               
            $set = array();
   
            foreach ( $data[$key] as $col => $val ) {
                $val = $val == '?' ? $val : '\''.$val.'\'' ;
                $set[] = '`'.$col.'` = '.$val;
            }
            $set = implode( ' , ', $set );
               
            $sql_tmp .= ' SET '.$set;
            $sql_tmp .= ' WHERE '.$where[$key];
            $sql[$key] = $sql_tmp;
        }
        return $sql;
    }
   
    private function build_delete_sql() {
        $tables = $this->sqlTables;
        $where = $this->sqlWhere;
        $sql = array();
        foreach ( $tables as $key => $table ) {
            $sql_tmp = '';
            $sql_tmp .= 'DELETE FROM '.'`'.$table.'`';
            $sql_tmp .= ' WHERE '.$where[$key];
            $sql[$key] = $sql_tmp;
        }
        return $sql;
    }
   
    # 单语句查询;
    public function query( $sql ) {
        $this->sql = $sql;
        $this->clean_attribute();
        return $this->db->query( $sql );
    }
   
    # 单语句预查询;
    public function execute( $sql, $flag = false ) {
   
        $param = array_shift( $this->sqlPrepare );
   
        $result = $this->db->prepare( $sql );
   
        /*5.3是引用传递*/
        if ( strnatcmp( phpversion(), '5.3' ) >= 0 ) {
            for ( $i = 0; $i < count( $param ); $i++ ) {
                $param[$i] = &$param[$i]; 
            }
        }
   
        @call_user_func_array( array( $result, "bind_param" ), $param );
        if ( !$flag ) {
            $this->sql = $sql;
            $this->clean_attribute();
        }
           
        !is_object( $this->stmt  ) && $this->stmt = $result;
           
        return $result->execute();
   
    }
   
    # 多语句查询;
    public function multi_query( $sqlArr ) {
        $sql = implode(';', $sqlArr );
        $this->sql = $sql;
        $this->clean_attribute();
        return $this->db->multi_query( $sql );
    }
       
    # 获取结果集内容;
    public function get_result( $result, $action ) {
        switch ( $action ) {
            case 'select':
                $result =  $this->get_fetch_assoc( $result );
                break;
            case 'add':
                $result =  $this->get_insert_id();
                break;
            case 'save':
                $result =  $this->get_affected_rows();
                break;
            case 'delete':
                $result =  $this->get_affected_rows();
                break;
        }
   
        return $result;
   
    }
   
    # 将结果集转化为数组
    public function get_fetch_assoc( $result = '' ) {
           
        $res = array();
        $key = 1;
        do {
               
            if ( is_object( $this->stmt ) ) {
                $result = $this->stmt->get_result();
                $this->stmt = '';
            } else {
                $result = is_object( $result ) && $key <= 1 ? $result : $this->db->store_result();
            }
   
            if ( $result ) {
                while ( $row = $result->fetch_assoc() ) {
                   $res[$key][] = $row;
                }
                $result->free();
            }    
               
            $key++;
   
        } while ( $this->db->more_results() && $this->db->next_result() );
   
        if ( count( $res ) == 1 ) {
            $res = reset( $res );
        }
   
        return $res;
    }
   
    # 获取数据插入后的id
    public function get_insert_id() {
        $insertId = array();
           
        do {
               
            $insertId[] = $this->db->insert_id;
           
        } while ( $this->db->more_results() && $this->db->next_result() );
           
        if ( count( $insertId ) == 1 ) {
            $insertId = reset( $insertId );
        }
           
        return $insertId;
    }
   
    # 获取数据更新/删除的影响的行数;
    public function get_affected_rows() {
        $affectedRows = array();
           
        do {
               
            $affectedRows[] = $this->db->affected_rows;
           
        } while ( $this->db->more_results() && $this->db->next_result() );
   
        if ( count( $affectedRows ) == 1 ) {
            $affectedRows = reset( $affectedRows );
        }
        return $affectedRows;
    }
   
    # 获取写入数据;
    private function get_write_data() {
        $data = $this->data;
   
        if ( !$data || !is_array( $data ) ) {
            $this->error = '数据非法!';
            return false;
        }
           
        if ( count( $this->sqlTables ) > 1 ) {
            for( $i = 0; $i< count( $this->sqlTables ); $i++ ) {
                if ( !is_array( current( $data ) ) ) {
                    $this->error = '需要写入的数据与数据表 数量不一致!';
                    return false;
                }
            }
            return $data;
        }
   
        if ( count( $this->sqlTables ) == 1 && is_array( reset( $data ) ) ) {
            return $data;
        }
   
        return array( $data );
    }
   
    # 增删改时 检查where 
    private function check_where() {
        if ( !$this->sqlWhere ) {
            $this->error = '没有条件,危险操作!';
            return false;
        }
   
        if ( count( $this->sqlWhere ) < count( $this->sqlTables ) ) {
            $this->error = '条件与数据表 数量不一致! 危险!';
            return false;
        }
   
        return true;
    }
   
    # 语句执行后清空部分属性;
    private function clean_attribute( $noRemove = array( 'db', 'sqlTables', 'error', 'sql' ) ) {
        $attributes = get_class_vars( get_class( $this ) );
        foreach ( $attributes as $key => $value ) {
            if ( !in_array( $key, $noRemove ) ) {
                $this->$key = '';
            }
        }
    }
   
    # 获取最后执行的sql语句;
    public function getLastSql() {
        return $this->sql;
    } 
   
    # 获取错误信息;
    public function getError() {
        return $this->error;
    }
   
    public function __destruct() {
        $this->db->close();
        $this->clean_attribute( array() );
    }
   
}
   
   
// 测试
$config = array(
    'host' => 'localhost',
    'user' => 'root',
    'password' => 'admin',
    'db' => 'test',
    'charset' => 'utf8'
);
$M = new db_mysqli( $config );
   
#单语句增删改查;
$data = array('title' => time());
$res = $M('title')->add( $data ); //返回ID号;
var_dump( $res );
var_dump( $M->getLastSql() );
var_dump( $M->getError() );
//INSERT INTO `title` ( `title` )  VALUES  ( '1407911896' );
   
$res = $M('title')->field( 'title' )->where('id > 2')->limit(10)->order('id desc')->select();
var_dump( $res );
var_dump( $M->getLastSql() );
var_dump( $M->getError() );
//SELECT `title` FROM `title` WHERE id > 2 ORDER BY id desc LIMIT 10;
   
   
   
$res = $M('title')->where('id > 10')->delete(); //返回影响行数;
var_dump( $res );
var_dump( $M->getLastSql() );
var_dump( $M->getError() );
//DELETE FROM `title` WHERE id > 10;
   
#事务
$M->startTrans( true );
   
$data = array('title'=>'updat4e!');
$res = $M('title')->where('id = 1')->save( $data );//返回影响行数;
var_dump( $res );
var_dump( $M->getLastSql() );
var_dump( $M->getError() );
$M->rollback();
   
   
#单语句预处理增删改查;
$data = array( 'id' => '?', 'title' => '?' );
$res = $M('title')->prepare( 12,'prepare' )->add( $data );
var_dump( $res );
var_dump( $M->getLastSql() );
var_dump( $M->getError() );
   
$res = $M('title')->where('id > ?')->prepare(5)->order('id desc')->select();
var_dump( $res );
var_dump( $M->getLastSql() );
var_dump( $M->getError() );
   
$res = $M('title')->where('id > ? and title = ?')->prepare(array(9,'update!'))->delete();
var_dump( $res );
var_dump( $M->getLastSql() );
var_dump( $M->getError() );
   
$data = array('title'=>'?');
$res = $M('title')->where('id > ?')->prepare( 'update!' , 6 )->save( $data );
var_dump( $res );
var_dump( $M->getLastSql() );
var_dump( $M->getError() );
   
#多语句查询 原有的参数变为数组;
$data = array();
$data[0] = array('title' => '1'.time());
$data[1] = array('title' => '2'.time());
$tables = array( 'title','title' );
$res = $M( $tables )->add( $data ); //返回ID号;
var_dump( $res );
var_dump( $M->getLastSql() );
var_dump( $M->getError() );
/*INSERT INTO `title` ( `title` )  VALUES  ( '11407913979' ) ;
INSERT INTO `title` ( `title` )  VALUES  ( '21407913979' )*/
   
$field = array();
$field[0] = array('title');
$field[1] = array('id');
$res = $M($tables)->field( $field )->where(array('id > 5', 'id <= 5'))->limit(10)->order('id desc')->select();
var_dump( $res );
var_dump( $M->getLastSql() );
var_dump( $M->getError() );
/*
SELECT `title` FROM `title` WHERE id > 5 ORDER BY id desc LIMIT 10;
SELECT * FROM `title` WHERE id <= 5
*/
   
$res = $M($tables)->where(array('id = 5', 'id = 6'))->delete(); //返回影响行数;
var_dump( $res );
var_dump( $M->getLastSql() );
var_dump( $M->getError() );
/*
DELETE FROM `title` WHERE id = 5;
DELETE FROM `title` WHERE id = 6
*/