<?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
*/