PHP实现的mysql操作类【MySQL与MySQLi方式】

<?php
class connectionmysql{
  //主机
  private $host="localhost";
  //数据库的username
  private $name="root";
  //数据库的password
  private $pass="";
  //数据库名称
  private $table="phptest";
  //编码形式
  private $ut="utf-8";
  //构造函数
  function __construct(){
    $this->ut=$ut;
    $this->connect();
  }
  //数据库的链接
  function connect(){
    $link=mysql_connect($this->host,$this->name,$this->pass) or die ($this->error());
    mysql_select_db($this->table,$link) or die("没该数据库：".$this->table);
    mysql_query("set names '$this->ut'");
  }
  function query($sql, $type = '') {
    if(!($query = mysql_query($sql))) $this->show('say:', $sql);
    return $query;
  }
  function show($message = '', $sql = '') {
    if(!$sql) echo $message;
    else echo $message.'<br>'.$sql;
  }
  function affected_rows() {
    return mysql_affected_rows();
  }
  function result($query, $row) {
    return mysql_result($query, $row);
  }
  function num_rows($query) {
    return @mysql_num_rows($query);
  }
  function num_fields($query) {
    return mysql_num_fields($query);
  }
  function free_result($query) {
    return mysql_free_result($query);
  }
  function insert_id() {
    return mysql_insert_id();
  }
  function fetch_row($query) {
    return mysql_fetch_row($query);
  }
  function version() {
    return mysql_get_server_info();
  }
  function close() {
    return mysql_close();
  }
  //向$table表中插入值
  function fn_insert($table,$name,$value){
    $this->query("insert into $table ($name) value ($value)");
  }
  //根据$id值删除表$table中的一条记录
  function fn_delete($table,$id,$value){
    $this->query("delete from $table where $id=$value");
    echo "id为". $id." 的记录被成功删除!";
  }
}
$db = new connectionmysql();
$db->fn_insert('test','id,name,sex',"'','hongtenzone','m'");
$db->fn_delete('test', 'id', 1);
?>

<?php
class mysql{
  private $localhost = 'localhost';
  private $loacluser = '';
  private $localpass = '';
  private $localbase = '';
  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 . '" rel="external nofollow" >' . $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 );
  }
}