欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-07-19 12:27:07
复制代码 代码如下:

复制代码 代码如下:

<?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 );
   }

}

使用方法:
声明数据库对象
$conn = new mysql;
加载数据库参数
$conn->parameter(数据库服务器, 数据库用户名, 数据库密码, 数据库名称, 数据库编码, 数据库表前缀[可为空]);

上面的代码就已经加载这个类文件进来了,并且初始化了一些数据库连接参数!

下面介绍几个基本是方法函数:
1、 $conn -> table();
选择数据表,参数是数据表名称
2、$conn -> field();
选择的字段名称,多个用逗号隔开,如不调用这个方法,则返回全部
3、$conn -> where();
sql where子语句,根据条件筛选
4、$conn -> order();
sql 排序
5、$conn -> page(int);
参数是一个正整数数字,如调用这个方法,记录将分页显示
6、$conn -> select(布尔值);
执行查询,返回查询结果,如果有,则是一个二维数组,如果无,则返回假,参数可省略,如省略,默认为真,返回的数组包含数字元素
7、$conn -> del();
删除记录
8、 $conn -> edit(array());
修改记录,参数是一个一维数组,数组键是字段名称,数组值是字段值
9、$conn -> into(array());
添加记录,参数是一个一维数组,数组键是字段名称,数组值是字段值。

以上方法可连续调用,比如:

复制代码 代码如下:

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