本文实例讲述了一款简单实用的PHP操作MysqL数据库类。分享给大家供大家参考。具体如下:
function __construct($hostname,$username,$userpwd,$database,$pconnect=false,$charset='utf8') { define('allowed_htmltags','<table><tr><td>'); $this->pconnect=$pconnect; $this->dblink=$pconnect?MysqL_pconnect($hostname,$userpwd):MysqL_connect($hostname,$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->fetchall("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); } }
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]; }
{$table}
{$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; }
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; } }
'.$key.'
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);
$k
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); }
$tbname
".implode('
',array_keys($array))."
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; }
".$database."
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; }
{$tbname}
$fieldname
function get_tables() //获取所有表表名 { $tables=array(); $r=$this->fetchall("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 }
content
contentid
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; }
userid
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程序设计有所帮助。