php实现mysql数据库操作类分享

<?php
/*
数据库操作类
*/
class mysql{
 private $localhost = 'localhost';
 private $loacluser = 'root';
 private $localpass = '123456';
 private $localbase = 'jiangxibaiyi';
 private $localcode = 'utf8';
 private $prefix;
 private $conn;
 private $start     = 0;
 private $error     = false; //数据库连接状态, false表示未连接或连接不正常
 public  $err       = true;  //sql执行结果

 private $table;
 private $field     = '*';
 private $where     = '';
 private $order     = '';

 private $pagesize  = 0; //分页显示->每页多少条，0为不分页显示
 private $pagecount = 1; //分页显示->总共有多少条
 private $pagenum   = 1; //分页显示->总共有多少页
 private $pageno    = 1; //分页显示->当前第几页
 private $pagekey   = 'page'; //分页url参数键
 private $pagestart = 0; //分页显示->当前从第几条开始返回

 
 private $select;
 private $rest;

 private $result    = false;//结果集

 public $formarray = array();

 public $instr_id  = 0;
 private $j =  0;

 
 public function parameter($loca, $root, $pass, $base, $code, $prefix = ''){
  $this->loacluser = $root;
  $this->localbase = $base;
  $this->localcode = $code;
  $this->localhost = $loca;
  $this->localpass = $pass;
  $this->prefix    = $prefix;
  return $this;
 }
 private function connection( $sql ){
  !function_exists(mysqli_connect) ? die('查询失败，无法加载mysqli扩展') : null;
  $this->conn = @new mysqli( $this->localhost, $this->loacluser, $this->localpass, $this->localbase);
  $this->error = mysqli_connect_errno() == 0 ? true : false;
  !$this->error ? die('数据库连接错误，请检查数据库连接参数') : null;
  $this->conn->query('set names ' . $this->localcode);
  $this->rest = $this->conn->query($sql);
  $this->err  = mysqli_error($this->conn);
  $this->instr_id = mysqli_insert_id($this->conn);
  $this->rest->free_result;
  $this->conn->close;
  $this -> formarray = '';
  return $this;
 }

 public function null(){
  $this->pagesize  = 0;
  //$this->pagecount = 1;
  $this->pagestart = 1;
  $this->field     = ' * ';
     $this->select    = '';
  unset($this->table, $this->where,$this->order, $this->result);
 }

 public function table( $tablename ) {//数据表
   $this -> null();
   $this->table = '`' . $this->prefix . $tablename . '`';
   return $this;
 }

 public function field( $array = '*' ) {//数据字段
   !empty( $this->field ) ? $this->field = '' : null;
   $array = explode(',', $array);
   foreach ( $array as $field ) {
   $this->field .= !$this->start ? '`' . $field . '`' :  ', `' . $field . '`';
   $this->start++;
   }
   $this->start = 0;
   return $this;
 }

 public function where( $where ) {//条件
       $this->where = ' where ' .$where;
    return $this;
 }

 public function order( $order ) {//排序
   $this->order = ' order by ' . $order;
   return $this;
 }

 public function pk( $key ) {//分页url参数键
   $this->pagekey = $key;
   return $this;
 }

 public function page( $pagesize ) {//分页
   $this->pagesize = $pagesize;
   $this->pageno   = $this->get( $this->pagekey );
   $this->pageno   = empty( $this->pageno ) || !isset( $this->pageno ) || !is_numeric( $this->pageno ) || $this->pageno < 1 ? 1 : $this->pageno;
   return $this;
 }

 public function post( $key, $filter = true ){
  return $filter ? strip_tags($_post[$key]) : $_post[$key];
 }

 public function get( $key, $filter = true ){
  return $filter ? strip_tags($_get[$key]) : $_get[$key];
 }

 public function sel(){
  $this->select = 'select ' . $this->field . ' from ' . $this->table . $this->where . $this->order;
  $this->connection( $this->select );
  if ( $this->rest->num_rows ) {
   while ( $rs = $this->rest->fetch_assoc() ) {
     $this->result[] = $rs;
    }
  }
    $database = $this->result;
    return empty($database) ? false : $database;
 }

 public function querys( $sql = '', $type = 'not', $biao = false ) {
  $this->select = $sql;
  $this->connection( $this->select );
  if ( $this->rest->num_rows ) {
     if ( !$biao ) {
   while ( $rs = $this->rest->fetch_array() ) {
     $this->result[] = !preg_match('/^\d+$/i', $type) ?  $rs : $rs[ $type ];
    }
  } else {
   while ( $rs = $this->rest->fetch_assoc() ) {
     $this->result[] = $rs;
    }
  }
  }
    $database = $this->result;
    return empty($database) ? false : $database;

 }

 public function executes( $sql = '' ){
  $this->connection( $sql  );
  return $this->rest;
 }

 
 public function exists( $t = '', $f = '', $w = ''){
  if ( empty( $f ) ) { return 0; }
  $cmd = empty( $w ) ? 'select sum(' . $f . ') as `baiyinum` from `' . $this->prefix . $t .'`' : 'select sum(' . $f . ') as `baiyinum` from `' . $this->prefix . $t .'` where ' . $w;
        $this->connection( $cmd );
  unset( $t, $f, $w, $cmd );
  $rel =  $this->rest->fetch_array();
  return round( $rel['baiyinum'], 2 );
 }

 
 public function existsto( $bili = 10000, $t = '', $f = '', $w = ''){
  if ( empty( $f ) ) { return 0; }
  $cmd = empty( $w ) ? 'select sum(' . $f . ') as `baiyinum` from `' . $this->prefix . $t .'`' : 'select sum(' . $f . ') as `baiyinum` from `' . $this->prefix . $t .'` where ' . $w;
        $this->connection( $cmd );
  unset( $t, $f, $w, $cmd );
  $rel =  $this->rest->fetch_array();
  return round( $rel['baiyinum'] * $bili );
 }

 
 public function select( $type = true, $listnum = 1 ){ //返回记录（数组形式， 返回条数）
   $this->select = 'select ' . $this->field . ' from ' . $this->table . $this->where . $this->order;
   if ( is_numeric( $listnum ) ) {
  if ( $this->pagesize > 0 ) {
   $this->connection( $this->select );//执行查询
   $this->pagecount =  $this->rest->num_rows;//取得记录总数
   $this->pagenum   =  ceil($this->pagecount / $this->pagesize); //总共有多少页
   $this->pageno    = $this->pageno > $this->pagenum ? $this->pagenum : $this->pageno;
   $this->pagestart = ( $this->pageno - 1 ) * $this->pagesize;   //当前从第几条开始返回
   $this->select   .= ' limit ' . $this->pagestart . ', ' .$this->pagesize; //重新构造sql语句
  } else {
   $this->select   .= ' limit ' . $listnum; //重新构造sql语句
  }
   } else {
   $this->select   .= ' limit 1'; //重新构造sql语句
   }
   //echo $this->select;
   $this->connection( $this->select );//再次执行查询
   if ( $this->rest->num_rows ) {//如果记录存在
  if ( $type ) {
    while ( $rs = $this->rest->fetch_array() ) {
     $this->result[] = $rs;
    }
  }else{
    while ( $rs = $this->rest->fetch_assoc() ) {
     $this->result[] = $rs;
    }
  }
   }
   if ( ( $listnum == 1 or !is_numeric( $listnum ) ) && !$this->pagesize) { $this->result = $this->result[0]; }
   $database = $this->result;
   return empty($database) ? false : $database;
 }

   public function num() { //返回记录总数
     $this->select = 'select ' . $this->field . ' from ' . $this->table . $this->where . $this->order;
  $this->connection( $this->select );//执行查询
  return $this->rest->num_rows;//取得记录总数
   }

   public function pagenav($numnav = false ) {  //分页
  $action = $this -> get('action');
  !empty( $action ) or $action =  'index';
  $module = $this -> get('module');
  !empty( $module ) or $module = 'index';
  $navurl   = '/' . $module . '/' . $action . '/' . $this -> pagekey .'/';
  $naindex  = '/' . $module . '/' . $action;
  $pagehtml = "\n<div class=\"pagenav\">";
  $pagehtml .= '<span>' . $this->pagecount . '条记录        ' . $this->pageno . '/' . $this->pagenum . '页</span>            ';
  $this->pageno <= 1 or $pagehtml .= "<a href=\"" . $naindex . "\">首页</a>\n<a href=\"" .  $navurl . ($this->pageno - 1) . "\">上一页</a>\n";
 if ( $numnav ) { $pagehtml .= $this->numpage($navurl); }
 $this->pageno >= $this->pagenum or $pagehtml .= "<a href=\"" . $navurl. ($this->pageno + 1) .  "\">下一页</a>\n<a href=\"" . $navurl . $this->pagenum . "\">尾页</a>\n";
 $pagehtml .= "</div>\n";
 return  $pagehtml;
   }

   private function numpage( $can = '' ) { //数字分页
    $numhtml = '';
    $first   = 1;
    $last    = $this->pagenum;
    if ( $this->pagenum > 5 ) {
     if ( $this->pageno < $this->pagenum ) {
     $first = $this->pageno - 2;
     $last  = $this->pageno + 2;
     }else{
     $first = $this->pageno - 4;
     $last  = $this->pagenum;
     }
    }
    if ( $first < 1 ) { $first   = 1; $last = $first + 4;}
    if ( $last  > $this->pagenum ) { $first   = $this->pagenum - 4; $last = $this->pagenum;}
    for( $i = $first; $i <= $last; $i++) {
     $numhtml .= $this->pageno != $i ? "\n\t" . '<a href="' . $can . $i . '">' . $i . '</a>' . "\n\t" : "\n\t" .'<a class="hover" disabled="disabled">' . $i . '</a>' . "\n\t";
    }
    unset($can, $first, $i, $last);
    return $numhtml;
   }

   public function userpage($numnav = false, $pagename = 'index', $mulu = 'user' ) {  //会员中心分页
     $navurl   = '/' . $mulu . '/' . $pagename . '/' . $this->pagekey . '/';
  $pagehtml = "\n<div class=\"pagenav\">";
  $pagehtml .= '<span>' . $this->pagecount . '条记录        ' . $this->pageno . '/' . $this->pagenum . '页</span>            ';
  $this->pageno <= 1 or $pagehtml .= "<a href=\"" . $navurl . "1\">首页</a>\n<a href=\"" .  $navurl . ($this->pageno - 1) . "\">上一页</a>\n";
 if ( $numnav ) { $pagehtml .= $this->numpage($navurl); }
 $this->pageno >= $this->pagenum or $pagehtml .= "<a href=\"" . $navurl. ($this->pageno + 1) .  "\">下一页</a>\n<a href=\"" . $navurl . $this->pagenum . "\">尾页</a>\n";
 $pagehtml .= "</div><div class=\"clear\"></div>\n";
 return  $pagehtml;
   }

  
   //表单处理开始

 //判断表单时候提交
 public function formis( $keys = 'mm' ) {
  return $_post[ $keys ] == 1 ? true : false;
 }

 //post方式获取数据
 public function _post( $keys = '', $tihuan = '') {
  $values = strip_tags( $_post[ $keys ] );
  $this->formarray[$keys] = empty( $values ) ? $tihuan : $values;
        return  empty( $values ) ? $tihuan : $values;
 }

 //get方法获取数据
 public function _get( $keys = '', $tihuan = '') {
  $values = strip_tags( $_get[ $keys ] );
        return  empty( $values ) ? $tihuan : $values;
 }

 //判断是否为数字并且不小于0
 public function isnum( $num = 0, $mesg = '参数必须为数字' ) {
  if ( is_numeric( $num ) && !empty( $num ) && $num >= 0 ) {
   return $num;
  }else{
   die( $mesg );
  }
 }

 //判断是否为数字并且不小于0返回true/false
 public function number( $num = 0) {
   return  is_numeric( $num ) && !empty( $num ) && $num >= 0 ? true : false;
 }

 //检测相关数据似乎存在
 public function isdata($types = true, $memg = '数据已经存在' ){
  $this->connection('select ' . $this->field . ' from ' . $this->table . $this->where);
  if ( $types ){
   $this->rest->num_rows > 0 ? die( $memg ) : null;
  } else {
   return $this->rest->num_rows;
  }
 }

 
 //写入数据库记录
 public function into( $mesg = '' ){
  !is_array( $this->formarray ) ? die( $mesg ) : null;
  $sql = 'insert into ' . $this->table . ' (`';
  $i   = 0;
  foreach ( $this->formarray as $key => $val ){
   $duan .= !$i ? $key . '`' : ', `' . $key . '`';
   if ( is_numeric( $val ) ){
    $vals .= !$i ? $val : ', ' . $val;
   }else{
    $vals .= !$i ? '\'' . $val . '\'' : ', \'' . $val . '\'';
   }
   $i++;
  }
  $sql .= $duan . ') values (' . $vals . ')';

  //@file_put_contents('1.sql', $sql, file_append);

  $this->connection( $sql );
  return !empty( $this->err ) ? false : true;
 }

   //数组形式写入数据
   public function msgbox( $table = '', $filed = array() ) {
    $this -> table($table);
    foreach( $filed as $key => $val ) {
    $this -> formarray[ $key ] = $val;
    }
   return $this ->  into('未取得数据');
   }

   //修改数据库记录
   public function edit( $array = array() ) {
    if ( empty( $array ) ) { $array = $this -> formarray; }
    if ( !is_array( $array ) || empty( $array ) ) {
     return false;
    } else {
       $sql = 'update ' . $this -> table . ' set ';
    $i   = 0;
    $sub = '';
    $huan = array('-' => '[jian]', '+' => '[jia]', '*' => '[cheng]', '/' => '[chu]');
    $zhan = array('[jian]' => '-', '[jia]' => '+', '[cheng]' => '*', '[chu]' => '/');

    foreach ( $array as $files => $val ) {
     $val  = !is_numeric( $val ) && !preg_match('/\`\w+\`\s*(\+|\-|\*|\/)/i', $val) ? '\'' . $val . '\'' : $val;
     foreach ( $huan as $key => $val ){
      $val = str_replace($key, $val, $val);
     }
     $duan = !$i ? '`' . $files . '` = ' :  ', `' . $files . '` = ';
     $sub .= $duan . $val;
     $i++;
    }
    $sql .= $sub . $this -> where;
    foreach ( $zhan as $fan => $hui ) {
     $sql = str_replace($fan, $hui, $sql);
    }

    //echo $sql; die;

    $this -> connection( $sql );
    unset( $array, $duan, $fan, $files, $huan, $hui, $i, $key, $sql, $sub, $val, $zhan, $val );
    return !empty( $this -> err ) ? false : true;
   }
   }

   //删除数据库记录
   public function del(){
    $sql = 'delete from ' . $this->table . $this->where;
    $this->connection( $sql );
    unset($sql);
    return !empty( $this->err ) ? false : true;
   }

   //表单处理结束

   //页面跳转
   public function msg( $text = '操作成功' ) {
    echo '<meta http-equiv="content-type" content="text/html; charset=utf-8" />';
    echo '<script type="text/javascript">
  <!--
    alert("' . $text . '");
 document.location="' . $_server['http_referer'] . '";
  //-->  
</script>';
 exit;
   }

   #取得系统当前时间
   public function times(){
    return str_replace('-', '[jian]', date('y-m-d h:i:s'));
   }

   #取得用户ip地址
 public function getip(){
   if (getenv("http_client_ip") && strcasecmp(getenv("http_client_ip"), "unknown"))
    $ip = getenv("http_client_ip");
  else if (getenv("http_x_forwarded_for") && strcasecmp(getenv("http_x_forwarded_for"), "unknown"))
    $ip = getenv("http_x_forwarded_for");
  else if (getenv("remote_addr") && strcasecmp(getenv("remote_addr"), "unknown"))
    $ip = getenv("remote_addr");
  else if (isset($_server['remote_addr']) && $_server['remote_addr'] && strcasecmp($_server['remote_addr'], "unknown"))
    $ip = $_server['remote_addr'];
  else
    $ip = "unknown";
   return($ip);
 }

  
   //最后关闭数据库连接
   public function close(){
    !is_object( $this -> conn ) or mysqli_close( $this -> conn );
   }

}

$rult = $conn -> table('user') -> select(); //查询返回user表的所有记录
$rult = $conn -> table('user') -> page(20) -> select();//查询返回user表的所有记录，并分页显示，每页20条；