本文实例讲述了一款简单实用的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程序设计有所帮助。
