本来想搞个超级的建站系统的。只搞了一部分。不少代码感觉不错。分享给需要的人!
非常好用的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);
}
}
?>