非常好用的mysql类，支持分页读取，insert/update支持数组。

最新推荐文章于 2023-04-18 14:21:25 发布
z109876543210
最新推荐文章于 2023-04-18 14:21:25 发布
阅读量1.2k
点赞数
文章标签： mysql类支持分页
本文链接：https://blog.csdn.net/jianye112/article/details/9500803
版权
本来想搞个超级的建站系统的。只搞了一部分。不少代码感觉不错。分享给需要的人！
非常好用的mysql类，支持分页读取，insert/update支持数组。
//数据库
$db = new Mysql; //创建Mysql类对象
$db->host = 'localhost';
$db->user = 'root';
$db->password = '';
$db->database = 'db1';
$db->charset = 'utf8';
$db->prefix = 'ydtuiguang_;
$db->connect();
$db->select(tablename);
$db->insert(tablename, array('title'=>'test'));
$db->get_page(tablename, '*', '', false,$page, $pagesize);
<?php
/*
// @author : lael
// blog : http://www.ydtuiguang.com/
// http://www.gzyd.net
// all right reserved.
*/

namespace ydsdk\libraries;

class mysqli {
	private $link_id  = 0;
	private $query_id = 0;
		
	private $connent = false;
	private $error = '';
	private $debug = 1;	
	private $host   = 'localhost';
	private $user     = 'root';
	private $password = 'root';
	private $database = 'test';
	private $port = 3306;
	private $timeout = 2;
	private $globalstable = array();
	private $globalsprefix = '';
	private $prefix = '';
	private $prefixkey = '###';//前缀替换符
	private $charset = 'utf8';
	private $restype = MYSQLI_ASSOC; //MYSQLI_ASSOC，MYSQLI_NUM 和 MYSQLI_BOTH
	private $slashes = false;//addslashes
	public $nowsql = '';//方便调试	
	private $wherekeyword = array('where ', 'group by ', 'left join ', 'inner join ', 'order by ', 'limit ');
	private $sqlkeyword = array('select ', 'insert ', 'replace ', 'update ', 'delete ', 'truncate ', 'show ');

	public function __construct($params = array()){
		$this->debug = $params['debug'];
		$this->host = $params['host'];
		$this->user = $params['user'];
		$this->password = $params['pwd'];
		$this->database = $params['dbname'];
		if($params['port'])$this->port = $params['port'];
		$this->prefix = $params['prefix'];
		if($params['prefixkey'])$this->prefixkey = $params['prefixkey'];
		$this->timeout = $params['timeout'];
  		$this->connect();
  	}

	private function connect(){
		if(!$this->connent ){
			$this->link_id = mysqli_init();
			$this->link_id->options(MYSQLI_OPT_CONNECT_TIMEOUT, $this->timeout);//设置超时时间
			if($this->database){
				$this->connent = @$this->link_id->real_connect($this->host, $this->user, $this->password, $this->database, $this->port);			
			}else{
				$this->connent = @$this->link_id->real_connect($this->host, $this->user, $this->password, null, $this->port);			
			}
			if(!$this->connent){
				$this->halt('Link-ID == false, connect failed');
			}else{
				mysqli_query($this->link_id, 'set names \''.$this->charset.'\'');
				mysqli_query($this->link_id, 'set sql_mode=\'\'');
			}
		}
	}
	
	public function database($database){
		$this->database = $database;
		if(!@mysqli_select_db($this->link_id, $this->database)){
			$this->halt('cannot use database '.$this->database);
		}		
	}
	
	//select
  	//$table = 'table1'
  	/*
	$table = array('table1', 'table2', 'table3'); // $table = 'table1,table2,table3';
	$fields = '{0}.id,{1}.id as id2,{2}.id as id3';
	$condition = 'left join {1} on {0}.id={1}.id left join {2} on {0}.id={2}.id where {0}.id=100 order by {0}.id desc limit 0,1';
	*/
	public function select($table, $fields = '*', $condition = ''){
		return $this->get_arr($table, $fields, $condition);
		//$this->query($this->createsql('select', $table, $fields, $condition));
		//return $this->query_id;
	}
	
	public function get_field($table, $fields = '*', $condition = '', $_nowsql = true){
		return $this->get_one($table, $fields, $condition, $_nowsql);
	}
	
	public function get_one($table, $fields = '*', $condition = '', $_nowsql = true){
		$this->query($this->createsql('select', $table, $fields, $this->condition($condition).' limit 0,1'), false, false, $_nowsql);
		$row = $this->get_next(-1, MYSQLI_NUM);
		$this->free();
		return $row[0];
	}
	
	public function get_count($table, $condition = '', $_nowsql = true){
		return (int)$this->get_one($table, 'count(*)', $condition, $_nowsql);
	}
	
	public function chk_exists($table, $condition = ''){
		$row_count = $this->get_count($table, $condition);
		return !empty($row_count);
	}

	public function get_row($table, $fields = '*', $condition = ''){
		$this->query($this->createsql('select', $table, $fields, $this->condition($condition).' limit 0,1'));
		$row = $this->get_next();
		$this->free();
		return $row;
	}
	
	public function get_col($table, $fields = 'id', $condition = ''){
		$this->query($this->createsql('select', $table, $fields, $condition));
		$col = array();
		while($row = $this->get_next()){
			$keys = array_keys($row);
			if(count($keys) > 2){
				$col[$row[$keys[0]]][] = $row;	
			}elseif(count($keys) == 2){
				$col[$row[$keys[0]]][] = $row[$keys[1]];	
			}else{
				$col[] = $row[$keys[0]];	
			}
		}
		$this->free();
		return $col;
	}
	
	public function get_cols($table, $fields = 'id', $condition = ''){
		$this->query($this->createsql('select', $table, $fields, $condition));
		$cols = array();
		while($row = $this->get_next()){
			$keys = array_keys($row);
			$cols[$row[$keys[0]]] = $row;
		}
		$this->free();
		return $cols;
	}
	
	public function get_arr($table, $fields = '*', $condition = ''){
		$this->query($this->createsql('select', $table, $fields, $condition));
		$arr = array();
		while($row = $this->get_next()){
			$arr[] = $row;
		}
		$this->free();
		return $arr;
	}
	
	//分页读取
	public function get_page($table, $fields = '*', $condition = '', $total = false, $page = 1, $pagesize = 20){
		$res = array('page' => array(), 'data' => false);
		$res['page']['total'] = $total === false ? $this->get_count($table, $condition) : $total;//group by等复杂查询先自己查询total
		$res['page']['page'] = intval($page);
		$res['page']['pagesize'] = intval($pagesize);
		if($res['page']['pagesize'] < 1)$res['page']['pagesize'] = 20;
		if($res['page']['total'] == 0){
			$res['page']['page'] = 1;//第几页
			$res['page']['pagenum'] = 0;//共几页
			$res['data'] = array();
			$res['page']['pagetotal'] = 0;//当前数据条数
		}else{
     		$res['page']['pagenum'] = floor(($res['page']['total'] + $res['page']['pagesize'] - 1) / $res['page']['pagesize']);
			if($res['page']['page'] > $res['page']['pagenum'])$res['page']['page'] = $res['page']['pagenum'];
			if($res['page']['page'] < 1)$res['page']['page'] = 1;		
			$res['data'] = $this->get_arr($table, $fields, $this->condition($condition).' limit '.(($res['page']['page'] - 1) * $res['page']['pagesize']).','.$res['page']['pagesize']);
			$res['page']['pagetotal'] =  count($res['data']);
		}
		return $res;
	}
	
	public function get_next($_query_id = -1, $_restype = -1){
		if(($_query_id === -1 || !$_query_id) && !$this->query_id)return false;
		return mysqli_fetch_array($_query_id === -1 ? $this->query_id : $_query_id, 
								$_restype === -1 ? $this->restype : $_restype
								);
	}
	
	public function num_rows($_query_id = -1) {
		return mysqli_num_rows($_query_id === -1 ? $this->query_id : $_query_id);
	}	
	
	//insert
	public function insert($table, $vals = array()){
		$sqlk = $sqlv = '';
		foreach($vals as $k => $v){
			$sqlk .= ',`'.$k.'`'; 
			if($this->slashes){
				$sqlv .= ',\''.addslashes($v).'\'';
			}else{
				$sqlv .= ',\''.$v.'\'';
			}
		}
        $sqlk = substr($sqlk, 1);
        $sqlv = substr($sqlv, 1);
		return $this->query($this->createsql('insert into', $table, $sqlk, $sqlv));
	}

	public function inserts($table, $vals = array()){
		$sqlk = '';
		$sqlv = array();
		$list = 0;
		foreach($vals as $v1){
			foreach($v1 as $k2 => $v2){
				if($list === 0)$sqlk .= ',`'.$k2.'`'; 
				if($this->slashes){
					$sqlv[$list] .= ',\''.addslashes($v2).'\'';
				}else{
					$sqlv[$list] .= ',\''.$v2.'\'';
				}
			}
			$list ++;
		}
        $sqlk = substr($sqlk, 1);
        foreach($sqlv as $k => $v)$sqlv[$k] = substr($v, 1);
		$sqlv = implode('),(', $sqlv);
		return $this->query($this->createsql('insert into', $table, $sqlk, $sqlv));
	}

	//replace
	public function replace($table, $vals = array()){
		$sqlk = $sqlv = '';
		foreach($vals as $k => $v){
			$sqlk .= ',`'.$k.'`'; 
			if($this->slashes){
				$sqlv .= ',\''.addslashes($v).'\'';
			}else{
				$sqlv .= ',\''.$v.'\'';
			}
		}
        $sqlk = substr($sqlk, 1);
        $sqlv = substr($sqlv, 1);
		return $this->query($this->createsql('replace into', $table, $sqlk, $sqlv));
	}

	public function replaces($table, $vals = array()){
		$sqlk = '';
		$sqlv = array();
		$list = 0;
		foreach($vals as $v1){
			foreach($v1 as $k2 => $v2){
				if($list === 0)$sqlk .= ',`'.$k2.'`'; 
				if($this->slashes){
					$sqlv[$list] .= ',\''.addslashes($v2).'\'';
				}else{
					$sqlv[$list] .= ',\''.$v2.'\'';
				}
			}
			$list ++;
		}
        $sqlk = substr($sqlk, 1);
        foreach($sqlv as $k => $v)$sqlv[$k] = substr($v, 1);
		$sqlv = implode('),(', $sqlv);
		return $this->query($this->createsql('replace into', $table, $sqlk, $sqlv));
	}

	public function insert_id(){
		return mysqli_insert_id($this->link_id);
	}
	
	//update
	public function update($table, $vals = array(), $condition = ''){
		$sql = '';
		foreach($vals as $k => $v) {
			if($this->slashes){
				$sql .= ',`$k`=\''.addslashes($v).'\'';				
			}else{
				$sql .= ",`$k`='$v'";
			}
		}
        $sql = substr($sql, 1);
		return $this->query($this->createsql('update', $table, $sql, $condition));
	}

	public function affected_rows(){
		return mysqli_affected_rows($this->link_id);
	}
	
	//delete
	public function delete($table, $condition = '', $del_table = '', $truncate = false){
		$result = $this->query($this->createsql('delete', $table, '', $condition, $del_table));
		if($result && $truncate){
			$count = $this->get_count($table, '', false);
			if($count == 0){
				$this->query($table, 'TRUNCATE TABLE {0}', false, false);
			}
		}
		return $result;
	}
	
	//sql执行
	public function query($sql_table, $sql = false, $_free = false, $_nowsql = true){
		if($sql === false){
			$sql_table = str_ireplace($this->prefixkey, $this->prefix, $sql_table);
			if($_nowsql)$this->nowsql = $sql_table;
	
			$this->query_id = @mysqli_query($this->link_id, $sql_table);
			if(!$this->query_id){
				$this->halt('Invalid SQL: '.$sql_table);
			}
			if($_free)$this->free();
			return $this->query_id;
		}else{
			$this->tablename($sql_table, $sql);
			return $this->query($sql, false, $_free, $_nowsql);
		}
	}
	
	public function querys($sql_table, $sql = false, $_free = false, $_nowsql = true){
		if($sql === false){
			$sql_table = str_ireplace($this->prefixkey, $this->prefix, $sql_table);
			if($_nowsql)$this->nowsql = $sql_table;
	
			$this->query_id = @mysqli_multi_query($this->link_id, $sql_table);
			if(!$this->query_id){
				$this->halt('Invalid SQL: '.$sql_table);
			}
			if($_free)$this->free();
			return $this->query_id;
		}else{
			$this->tablename($sql_table, $sql);
			return $this->query($sql, false, $_free, $_nowsql);
		}
	}
	
	public function tablename(&$table, &$fields = '', &$condition = ''){
		if(!is_array($table))$table = explode(',', $table);
		$trarr = array();
		for($i = 0; $i < count($table); $i ++){
			if(in_array($table[$i], $this->globalstable)){
				$trarr['{'.$i.'}'] = $this->tableprefix($this->globalsprefix, $table[$i]);
			}else{
				$trarr['{'.$i.'}'] = $this->tableprefix($this->prefix, $table[$i]);
			}
		}
		if(in_array($table[0], $this->globalstable)){
			$table= $this->tableprefix($this->globalsprefix, $table[0]);
		}else{
			$table= $this->tableprefix($this->prefix, $table[0]);
		}
		$fields = strtr($fields, $trarr);
		$condition = strtr($this->condition($condition), $trarr);
	}
	
	private function tableprefix($prefix, $table){
		if(strpos($table, '(') === 0)return $table;
		return $prefix.$table;	
	}
	
	private function condition($condition){
		if(is_array($condition)){
			$temp = ' 1=1 ';
			foreach($condition as $k => $v){
				$temp .= " and $k='$v'";
			}
			$condition = $temp;
		}
		return $condition;		
	}
	
	//$action = select/insert/update/delete
	private function createsql($action, $table, $fields, $condition, $del_table = ''){
		$sql = '';
		if($this->chk_sqlkeyword($table)){
			$sql = $table.' '.$this->get_wherekeyword($condition);	
		}else{
			$this->tablename($table, $fields, $condition);
			switch($action){
				case 'select':
					$sql = $action.' '.$fields.' from '.$table.' '.$this->get_wherekeyword($condition);
					break;
				case 'insert into':
				case 'replace into':
					$sql = $action.' '.$table.' ('.$fields.') values('.$condition.')';
					break;
				case 'update':
					$sql = $action.' '.$table.' set '.$fields.' '.$this->get_wherekeyword($condition);
					break;	
				case 'delete':
					if(!$del_table){
						$temp = array_filter(explode(' ', $table));
						$del_table = $temp[0];
						if(isset($temp[1]))$del_table = $temp[1];
					}
					$sql = $action.' '.$del_table.' from '.$table.' '.$this->get_wherekeyword($condition);
					break;
			}
		}
		return $sql;
	}
	
	private function get_wherekeyword($condition){
		if(empty($condition) || is_array($condition))return $condition;
		for($i = 0; $i < count($this->wherekeyword); $i ++){
			if(strpos(strtolower(ltrim($condition)), $this->wherekeyword[$i]) === 0)return $condition;
		}
		return 'where '.$condition;		
	}
	
	private function chk_sqlkeyword($sql){
		if(empty($sql) || is_array($sql))return false;
		for($i = 0; $i < count($this->sqlkeyword); $i ++){
			if(strpos(strtolower(ltrim($sql)), $this->sqlkeyword[$i]) === 0)return true;
		}
		return false;		
	}
		
	public function list_tables($pf = false){
		$this->query('SHOW TABLES');
		$arr = array();
		while($row = $this->get_next(-1, MYSQLI_NUM)){
			$temp = 0;
			if(!$pf){
				if(in_array(substr($row[0], strlen($this->globalsprefix)), $this->globalstable)){
					$temp= strlen($this->globalsprefix);
				}else{
					$temp= strlen($this->prefix);
				}
			}
			$arr[] = $pf ? $row[0] : substr($row[0], $temp);
		}
		$this->free();
		return $arr;
	}
	
	public function list_fields($table, $out = array()){
		$this->query($table, 'SHOW FIELDS FROM {0}');
		$arr = array();
		$list = 0;
		$_out = $out;
		if(!is_array($out))$_out = explode(',', $out);
		while($row = $this->get_next()){
			if(in_array($row['Field'], $_out))continue;
			$row['orderby'] = $list;
			$arr[] = $row;
			$list ++;
		}
		$this->free();
		return $arr;
	}
	
	public function link_id(){
		return $this->link_id;
	}

	public function query_id(){
		return $this->query_id;
	}
	
	public function seek($_query_id = -1, $rnum = 0){
		if(($_query_id === -1 || !$_query_id) && !$this->query_id)return false;
		return mysqli_data_seek($_query_id === -1 ? $this->query_id : $_query_id, $rnum);
	}

	public function free($_query_id = -1){
		if(($_query_id === -1 || !$_query_id) && !$this->query_id)return false;
		if(!is_resource($_query_id === -1 ? $this->query_id : $_query_id))return false;
		return mysqli_free_result($_query_id === -1 ? $this->query_id : $_query_id);
	}
  
	public function close(){
		return mysqli_close($this->link_id);
	}
	
	public function error(){
		return $this->error;
	}

	public function halt($msg){
		if($this->debug){
			if($this->connent){
				$msg = mysqli_error($this->link_id);
				$this->error = array(
					'nowsql' => $this->nowsql,
					'error' => $msg,
					'errno' => mysqli_errno($this->link_id)
				);
			}else{
				$this->error = array(
					'nowsql' => $this->nowsql,
					'error' => $msg,
					'errno' => 0
				);
			}
			log_message($this->error);
		}else{
			$msg = 'database error';
			$this->error = array(
				'error' => $msg,
				'errno' => 0
			);
		}
		throw new \Exception($msg); 
	}
}
?>