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

一款简单实用的php操作mysql数据库类_PHP

程序员文章站 2022-05-11 22:15:41
...
本文实例讲述了一款简单实用的php操作mysql数据库类。分享给大家供大家参考。具体如下:

代码如下:


/*
本款数据库连接类,他会自动加载sql防注入功能,过滤一些敏感的sql查询关键词,同时还可以增加判断字段 show table status的性质与show table类 获取数据库所有表名等。*/
@ini_set('mysql.trace_mode','off');
class mysql
{
public $dblink;
public $pconnect;
private $search = array('/union(s*(/*.**/)?s*)+select/i', '/load_file(s*(/*.**/)?s*)+(/i', '/into(s*(/*.**/)?s*)+outfile/i');
private $replace = array('union select', 'load_file (', 'into outfile');
private $rs;

function __construct($hostname,$username,$userpwd,$database,$pconnect=false,$charset='utf8')
{
define('allowed_htmltags', '

<meta>



    • 一款简单实用的php操作mysql数据库类_PHP
      ');
      $this->pconnect=$pconnect;
      $this->dblink=$pconnect?mysql_pconnect($hostname,$username,$userpwd):mysql_connect($hostname,$username,$userpwd);
      (!$this->dblink||!is_resource($this->dblink)) && fatal_error("connect to the database unsuccessfully!");
      @mysql_unbuffered_query("set names {$charset}");
      if($this->version()>'5.0.1')
      {
      @mysql_unbuffered_query("set sql_mode = ''");
      }
      @mysql_select_db($database) or fatal_error("can not select table!");
      return $this->dblink;
      }

      function query($sql,$unbuffered=false)
      {
      //echo $sql.'
      ';
      $this->rs=$unbuffered?mysql_unbuffered_query($sql,$this->dblink):mysql_query($sql,$this->dblink);
      //(!$this->rs||!is_resource($this->rs)) && fatal_error("execute the query unsuccessfully! error:".mysql_error());
      if(!$this->rs)fatal_error('在执行sql语句 '.$sql.' 时发生以下错误:'.mysql_error());
      return $this->rs;
      }

      function fetch_one($sql)
      {
      $this->rs=$this->query($sql);
      return dircms_strips教程lashes($this->filter_pass(mysql_fetch_array($this->rs,mysql_assoc)));
      }

      function get_maxfield($filed='id',$table) // 获取$table表中$filed字段的最大值
      {
      $r=$this->fetch_one("select {$table}.{$filed} from `{$table}` order by `{$table}`.`{$filed}` desc limit 0,1");
      return $r[$filed];
      }

      function fetch_all($sql)
      {
      $this->rs=$this->query($sql);
      $result=array();
      while($rows=mysql_fetch_array($this->rs,mysql_assoc))
      {
      $result[]=$rows;
      }

      mysql_free_result($this->rs);
      return dircms_stripslashes($this->filter_pass($result));
      }

      function fetch_all_withkey($sql,$key='id')
      {
      $this->rs=$this->query($sql);
      $result=array();
      while($rows=mysql_fetch_array($this->rs,mysql_assoc))
      {
      $result[$rows[$key]]=$rows;
      }

      mysql_free_result($this->rs);
      return dircms_stripslashes($this->filter_pass($result));
      }

      function last_insert_id()
      {
      if(($insertid=mysql_insert_id($this->dblink))>0)return $insertid;
      else //如果 auto_increment 的列的类型是 bigint,则 mysql_insert_id() 返回的值将不正确.
      {
      $result=$this->fetch_one('select last_insert_id() as insertid');
      return $result['insertid'];
      }
      }

      function insert($tbname,$varray,$replace=false)
      {
      $varray=$this->escape($varray);
      $tb_fields=$this->get_fields($tbname); // 升级一下,增加判断字段是否存在

      foreach($varray as $key => $value)
      {
      if(in_array($key,$tb_fields))
      {
      $fileds[]='`'.$key.'`';
      $values[]=is_string($value)?'''.$value.''':$value;
      }
      }

      if($fileds)
      {
      $fileds=implode(',',$fileds);
      $fileds=str_replace(''','`',$fileds);
      $values=implode(',',$values);
      $sql=$replace?"replace into {$tbname}({$fileds}) values ({$values})":"insert into {$tbname}({$fileds}) values ({$values})";
      $this->query($sql,true);
      return $this->last_insert_id();
      }
      else return false;
      }

      function update($tbname, $array, $where = '')
      {
      $array=$this->escape($array);
      if($where)
      {
      $tb_fields=$this->get_fields($tbname); // 增加判断字段是否存在

      $sql = '';
      foreach($array as $k=>$v)
      {
      if(in_array($k,$tb_fields))
      {
      $k=str_replace(''','',$k);
      $sql .= ", `$k`='$v'";
      }
      }
      $sql = substr($sql, 1);

      if($sql)$sql = "update `$tbname` set $sql where $where";
      else return true;
      }
      else
      {
      $sql = "replace into `$tbname`(`".implode('`,`', array_keys($array))."`) values('".implode("','", $array)."')";
      }
      return $this->query($sql,true);
      }

      function mysql_delete($tbname,$idarray,$filedname='id')
      {
      $idwhere=is_array($idarray)?implode(',',$idarray):intval($idarray);
      $where=is_array($idarray)?"{$tbname}.{$filedname} in ({$idwhere})":" {$tbname}.{$filedname}={$idwhere}";

      return $this->query("delete from {$tbname} where {$where}",true);
      }

      function get_fields($table)
      {
      $fields=array();
      $result=$this->fetch_all("show columns from `{$table}`");
      foreach($result as $val)
      {
      $fields[]=$val['field'];
      }
      return $fields;
      }

      function get_table_status($database)
      {
      $status=array();
      $r=$this->fetch_all("show table status from `".$database."`"); /////// show table status的性质与show table类似,不过,可以提供每个表的大量信息。
      foreach($r as $v)
      {
      $status[]=$v;
      }
      return $status;
      }

      function get_one_table_status($table)
      {
      return $this->fetch_one("show table status like '$table'");
      }

      function create_fields($tbname,$fieldname,$size=0,$type='varchar') // 2010-5-14 修正一下
      {
      if($size)
      {
      $size=strtoupper($type)=='varchar'?$size:8;
      $this->query("alter table `{$tbname}` add `$fieldname` {$type}( {$size} ) not null",true);
      }
      else $this->query("alter table `{$tbname}` add `$fieldname` mediumtext not null",true);
      return true;
      }

      function get_tables() //获取所有表表名
      {
      $tables=array();
      $r=$this->fetch_all("show tables");
      foreach($r as $v)
      {
      foreach($v as $v_)
      {
      $tables[]=$v_;
      }
      }
      return $tables;
      }

      function create_model_table($tbname) //创建一个内容模型表(start:初始只有字段contentid int(20),用于内容表,/////////////////////// update:2010-5-20 默认加入`content` mediumtext not null,字段)
      {
      if(in_array($tbname,$this->get_tables())) return false; ///////////////////// 当表名已经存在时,返回 false
      if($this->query("create table `{$tbname}` (
      `contentid` mediumint(8) not null ,
      `content` mediumtext not null,
      key ( `contentid` )
      ) engine = myisam default charset=utf8",true))return true; //////////////////// 成功则返回 true
      return false; //////////////失败返回 false
      }

      function create_table($tbname) //创建一个会员模型空表(初始只有字段userid int(20),用于会员表,2010-4-26)
      {
      if(in_array($tbname,$this->get_tables())) return false;
      if($this->query("create table `{$tbname}` (
      `userid` mediumint(8) not null ,
      key ( `userid` )
      ) engine = myisam default charset=utf8",true))return true;
      return false;
      }

      function escape($str) // 过滤危险字符
      {
      if(!is_array($str)) return str_replace(array('n', 'r'), array(chr(10), chr(13)),mysql_real_escape_string(preg_replace($this->search,$this->replace, $str), $this->dblink));
      foreach($str as $key=>$val) $str[$key] = $this->escape($val);
      return $str;
      }

      function filter_pass($string, $allowedtags = '', $disabledattributes = array('onabort', 'onactivate', 'onafterprint', 'onafterupdate', 'onbeforeactivate', 'onbeforecopy', 'onbeforecut', 'onbeforedeactivate', 'onbeforeeditfocus', 'onbeforepaste', 'onbeforeprint', 'onbeforeunload', 'onbeforeupdate', 'onblur', 'onbounce', 'oncellchange', 'onchange', 'onclick', 'oncontextmenu', 'oncontrolselect', 'oncopy', 'oncut', 'ondataavaible', 'ondatasetchanged', 'ondatasetcomplete', 'ondblclick', 'ondeactivate', 'ondrag', 'ondragdrop', 'ondragend', 'ondragenter', 'ondragleave', 'ondragover', 'ondragstart', 'ondrop', 'onerror', 'onerrorupdate', 'onfilterupdate', 'onfinish', 'onfocus', 'onfocusin', 'onfocusout', 'onhelp', 'onkeydown', 'onkeypress', 'onkeyup', 'onlayoutcomplete', 'onload', 'onlosecapture', 'onmousedown', 'onmouseenter', 'onmouseleave', 'onmousemove', 'onmoveout', 'onmouseo教程ver', 'onmouseup', 'onmousewheel', 'onmove', 'onmoveend', 'onmovestart', 'onpaste', 'onpropertychange', 'onreadystatechange', 'onreset', 'onresize', 'onresizeend', 'onresizestart', 'onrowexit', 'onrowsdelete', 'onrowsinserted', 'onscroll', 'onselect', 'onselectionchange', 'onselectstart', 'onstart', 'onstop', 'onsubmit', 'onunload'))
      {
      if(is_array($string))
      {
      foreach($string as $key => $val) $string[$key] = $this->filter_pass($val, allowed_htmltags);
      }
      else
      {
      $string = preg_replace('/s('.implode('|', $disabledattributes).').*?([s>])/', '', preg_replace('//ie', "'')) . '>'", strip_tags($string, $allowedtags)));
      }
      return $string;
      }

      function drop_table($tbname)
      {
      return $this->query("drop table if exists `{$tbname}`",true);
      }

      function version()
      {
      return mysql_get_server_info($this->dblink);
      }
      }

      希望本文所述对大家的PHP程序设计有所帮助。

      一款简单实用的php操作mysql数据库类_PHP

      声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn核实处理。

      相关文章

      相关视频


      网友评论

      文明上网理性发言,请遵守 新闻评论服务协议

      我要评论
    • 一款简单实用的php操作mysql数据库类_PHP
    • 专题推荐

      作者信息
      一款简单实用的php操作mysql数据库类_PHP

      认证0级讲师

      推荐视频教程
    • 一款简单实用的php操作mysql数据库类_PHPjavascript初级视频教程
    • 一款简单实用的php操作mysql数据库类_PHPjquery 基础视频教程
    • 视频教程分类