一款簡單實用的php操作mysql數據庫類

2020-03-22 20:05:20

字體：大中小

來源：轉載

供稿：網友

本文實例講述了一款簡單實用的php操作mysql數據庫類。分享給大家供大家參考。具體如下：復制代碼代碼如下:
/*
本款數據庫連接類，他會自動加載sql防注入功能，過濾一些敏感的sql查詢關鍵詞，同時還可以增加判斷字段 show table status的性質與show table類獲取數據庫所有表名等。*/
@ini_set('mysql.trace_mode','off');
html' target='_blank'>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-
}

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-
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-
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程序設計有所幫助。PHP教程

鄭重聲明：本文版權歸原作者所有，轉載文章僅為傳播更多信息之目的，如作者信息標記有誤，請第一時間聯系我們修改或刪除，多謝。

上一篇：php如何使用SwooleTaskWorker實現異步操作Mysql（代碼

下一篇：php+mysql實現無限級分類及緩存技術