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