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