一款简单实用的php操作mysql数据库类
本文实例讲述了一款简单实用的php操作mysql数据库类。分享给大家供大家参考。具体如下:
/* 本款数据库连接类,他会自动加载sql防注入功能,过滤一些敏感的sql查询关键词,同时还可以增加判断字段showtablestatus的性质与showtable类获取数据库所有表名等。*/ @ini_set('mysql.trace_mode','off'); classmysql { 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("connecttothedatabaseunsuccessfully!"); @mysql_unbuffered_query("setnames{$charset}"); if($this->version()>'5.0.1') { @mysql_unbuffered_query("setsql_mode=''"); } @mysql_select_db($database)orfatal_error("cannotselecttable!"); return$this->dblink; } functionquery($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("executethequeryunsuccessfully!error:".mysql_error()); if(!$this->rs)fatal_error('在执行sql语句'.$sql.'时发生以下错误:'.mysql_error()); return$this->rs; } functionfetch_one($sql) { $this->rs=$this->query($sql); returndircms_strips教程lashes($this->filter_pass(mysql_fetch_array($this->rs,mysql_assoc))); } functionget_maxfield($filed='id',$table)//获取$table表中$filed字段的最大值 { $r=$this->fetch_one("select{$table}.{$filed}from`{$table}`orderby`{$table}`.`{$filed}`desclimit0,1"); return$r[$filed]; } functionfetch_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); returndircms_stripslashes($this->filter_pass($result)); } functionfetch_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); returndircms_stripslashes($this->filter_pass($result)); } functionlast_insert_id() { if(($insertid=mysql_insert_id($this->dblink))>0)return$insertid; else//如果auto_increment的列的类型是bigint,则mysql_insert_id()返回的值将不正确. { $result=$this->fetch_one('selectlast_insert_id()asinsertid'); return$result['insertid']; } } functioninsert($tbname,$varray,$replace=false) { $varray=$this->escape($varray); $tb_fields=$this->get_fields($tbname);//升级一下,增加判断字段是否存在 foreach($varrayas$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?"replaceinto{$tbname}({$fileds})values({$values})":"insertinto{$tbname}({$fileds})values({$values})"; $this->query($sql,true); return$this->last_insert_id(); } elsereturnfalse; } functionupdate($tbname,$array,$where='') { $array=$this->escape($array); if($where) { $tb_fields=$this->get_fields($tbname);//增加判断字段是否存在 $sql=''; foreach($arrayas$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$sqlwhere$where"; elsereturntrue; } else { $sql="replaceinto`$tbname`(`".implode('`,`',array_keys($array))."`)values('".implode("','",$array)."')"; } return$this->query($sql,true); } functionmysql_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("deletefrom{$tbname}where{$where}",true); } functionget_fields($table) { $fields=array(); $result=$this->fetch_all("showcolumnsfrom`{$table}`"); foreach($resultas$val) { $fields[]=$val['field']; } return$fields; } functionget_table_status($database) { $status=array(); $r=$this->fetch_all("showtablestatusfrom`".$database."`");///////showtablestatus的性质与showtable类似,不过,可以提供每个表的大量信息。 foreach($ras$v) { $status[]=$v; } return$status; } functionget_one_table_status($table) { return$this->fetch_one("showtablestatuslike'$table'"); } functioncreate_fields($tbname,$fieldname,$size=0,$type='varchar')//2010-5-14修正一下 { if($size) { $size=strtoupper($type)=='varchar'?$size:8; $this->query("altertable`{$tbname}`add`$fieldname`{$type}({$size}) notnull",true); } else$this->query("altertable`{$tbname}`add`$fieldname`mediumtext notnull",true); returntrue; } functionget_tables()//获取所有表表名 { $tables=array(); $r=$this->fetch_all("showtables"); foreach($ras$v) { foreach($vas$v_) { $tables[]=$v_; } } return$tables; } functioncreate_model_table($tbname)//创建一个内容模型表(start:初始只有字段contentidint(20),用于内容表,///////////////////////update:2010-5-20 默认加入`content`mediumtextnotnull,字段) { if(in_array($tbname,$this->get_tables()))returnfalse; /////////////////////当表名已经存在时,返回false if($this->query("createtable`{$tbname}`( `contentid`mediumint(8)notnull, `content`mediumtextnotnull, key(`contentid`) )engine=myisamdefaultcharset=utf8",true))returntrue; //////////////////// 成功则返回true returnfalse;//////////////失败返回false } functioncreate_table($tbname)//创建一个会员模型空表(初始只有字段useridint(20),用于会员表,2010-4-26) { if(in_array($tbname,$this->get_tables()))returnfalse; if($this->query("createtable`{$tbname}`( `userid`mediumint(8)notnull, key(`userid`) )engine=myisamdefaultcharset=utf8",true))returntrue; returnfalse; } functionescape($str)//过滤危险字符 { if(!is_array($str))returnstr_replace(array('n','r'),array(chr(10),chr(13)),mysql_real_escape_string(preg_replace($this->search,$this->replace,$str),$this->dblink)); foreach($stras$key=>$val)$str[$key]=$this->escape($val); return$str; } functionfilter_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($stringas$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; } functiondrop_table($tbname) { return$this->query("droptableifexists`{$tbname}`",true); } functionversion() { returnmysql_get_server_info($this->dblink); } }
希望本文所述对大家的PHP程序设计有所帮助。