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

前端技术 2023/09/02 PHP

本文实例讲述了一款简单实用的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\', \'<html><embed><title><meta><body><a><p><br><hr><h1><h2><h3><h4><h5><h6><font><u><i><b><strong><div><span><ol><ul><li><img><table><tr><td><map>\'); 
  $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.\'<br>\';
  $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\', \"\'<\'.preg_replace(array(\'/网页特效:[^\"\']*/i\', \'/(\".implode(\'|\', $disabledattributes).\")[ ]*=[ ]*[\"\'][^\"\']*[\"\']/i\', \'/s+/\'), array(\'\', \'\', \' \'), stripslashes(\'\')) . \'>\'\", 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程序设计有所帮助。

本文地址：https://www.stayed.cn/item/4509

转载请注明出处。

本站部分内容来源于网络,如侵犯到您的权益,请 联系我

我的博客

人生若只如初见，何事秋风悲画扇。

我的标签

随笔档案