非常に使いやすいmysqlクラスで、ページング読み取りをサポートし、insert/updateは配列をサポートします.
17144 ワード
スーパーのステーションシステムを作りたいと思っていました.一部だけやった.多くのコードがいい感じです.必要な人に分かち合え!
非常に使いやすい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);
非常に使いやすい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);
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, MYSQL_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(-1, MYSQL_NUM)){
$col[] = $row[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[$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['page']['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 = '', $truncate = false){
$result = $this->query($this->createsql('delete', $table, '', $condition));
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->globalsprefix.$table[$i];
}else{
$trarr['{'.$i.'}'] = $this->prefix.$table[$i];
}
}
if(in_array($table[0], $this->globalstable)){
$table= $this->globalsprefix.$table[0];
}else{
$table= $this->prefix.$table[0];
}
$fields = strtr($fields, $trarr);
$condition = strtr($this->condition($condition), $trarr);
}
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){
$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':
$sql = $action.' 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, MYSQL_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){
$this->error = array(
'nowsql' => $this->nowsql,
'error' => mysqli_error($this->link_id),
'errno' => mysqli_errno($this->link_id)
);
}else{
$this->error = array(
'nowsql' => $this->nowsql,
'error' => $msg,
'errno' => 0
);
}
log_message($this->error);
}else{
$this->error = array(
'error' => 'database error',
'errno' => 0
);
}
}
}
?>