利用php操作mysql数据库类的实例讲解_程序开发

利用php操作mysql数据库类的实例讲解

创始人

2024-12-12 19:42:24

0次

本文实例讲述了一款简单实用的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', '<meta><body><a><p><br><hr><h2><h3><h4><h5><h6><h7><font><u><i><b><strong><div><span><ol><ul><li><img><table><tr><td><map>'); <br/> $this->pconnect=$pconnect; <br/> $this->dblink=$pconnect?mysql_pconnect($hostname,$username,$userpwd):mysql_connect($hostname,$username,$userpwd); <br/> (!$this->dblink||!is_resource($this->dblink)) && fatal_error("connect to the database unsuccessfully!"); <br/> @mysql_unbuffered_query("set names {$charset}"); <br/> if($this->version()>'5.0.1') <br/> { <br/> @mysql_unbuffered_query("set sql_mode = ''"); <br/> } <br/> @mysql_select_db($database) or fatal_error("can not select table!"); <br/> return $this->dblink; <br/> } <br/> <br/> function query($sql,$unbuffered=false) <br/> { <br/> //echo $sql.'<br>'; <br/> $this->rs=$unbuffered?mysql_unbuffered_query($sql,$this->dblink):mysql_query($sql,$this->dblink); <br/> //(!$this->rs||!is_resource($this->rs)) && fatal_error("execute the query unsuccessfully! error:".mysql_error()); <br/> if(!$this->rs)fatal_error('在执行sql语句 '.$sql.' 时发生以下错误:'.mysql_error()); <br/> return $this->rs; <br/> } <br/> <br/> function fetch_one($sql) <br/> { <br/> $this->rs=$this->query($sql); <br/> return dircms_strips教程lashes($this->filter_pass(mysql_fetch_array($this->rs,mysql_assoc))); <br/> } <br/> <br/> function get_maxfield($filed='id',$table) // 获取$table表中$filed字段的最大值 <br/> { <br/> $r=$this->fetch_one("select {$table}.{$filed} from `{$table}` order by `{$table}`.`{$filed}` desc limit 0,1"); <br/> return $r[$filed]; <br/> } <br/> <br/> function fetch_all($sql) <br/> { <br/> $this->rs=$this->query($sql); <br/> $result=array(); <br/> while($rows=mysql_fetch_array($this->rs,mysql_assoc)) <br/> { <br/> $result[]=$rows; <br/> } <br/> <br/> mysql_free_result($this->rs); <br/> return dircms_stripslashes($this->filter_pass($result)); <br/> } <br/> <br/> function fetch_all_withkey($sql,$key='id') <br/> { <br/> $this->rs=$this->query($sql); <br/> $result=array(); <br/> while($rows=mysql_fetch_array($this->rs,mysql_assoc)) <br/> { <br/> $result[$rows[$key]]=$rows; <br/> } <br/> <br/> mysql_free_result($this->rs); <br/> return dircms_stripslashes($this->filter_pass($result)); <br/> } <br/> <br/> function last_insert_id() <br/> { <br/> if(($insertid=mysql_insert_id($this->dblink))>0)return $insertid; <br/> else //如果 auto_increment 的列的类型是 bigint，则 mysql_insert_id() 返回的值将不正确. <br/> { <br/> $result=$this->fetch_one('select last_insert_id() as insertid'); <br/> return $result['insertid']; <br/> } <br/> } <br/> <br/> function insert($tbname,$varray,$replace=false) <br/> { <br/> $varray=$this->escape($varray); <br/> $tb_fields=$this->get_fields($tbname); // 升级一下，增加判断字段是否存在 <br/> <br/> foreach($varray as $key => $value) <br/> { <br/> if(in_array($key,$tb_fields)) <br/> { <br/> $fileds[]='`'.$key.'`'; <br/> $values[]=is_string($value)?'''.$value.''':$value; <br/> } <br/> } <br/> <br/> if($fileds) <br/> { <br/> $fileds=implode(',',$fileds); <br/> $fileds=str_replace(''','`',$fileds); <br/> $values=implode(',',$values); <br/> $sql=$replace?"replace into {$tbname}({$fileds}) values ({$values})":"insert into {$tbname}({$fileds}) values ({$values})"; <br/> $this->query($sql,true); <br/> return $this->last_insert_id(); <br/> } <br/> else return false; <br/> } <br/> <br/> function update($tbname, $array, $where = '') <br/> { <br/> $array=$this->escape($array); <br/> if($where) <br/> { <br/> $tb_fields=$this->get_fields($tbname); // 增加判断字段是否存在 <br/> <br/> $sql = ''; <br/> foreach($array as $k=>$v) <br/> { <br/> if(in_array($k,$tb_fields)) <br/> { <br/> $k=str_replace(''','',$k); <br/> $sql .= ", `$k`='$v'"; <br/> } <br/> } <br/> $sql = substr($sql, 1); <br/> <br/> if($sql)$sql = "update `$tbname` set $sql where $where"; <br/> else return true; <br/> } <br/> else <br/> { <br/> $sql = "replace into `$tbname`(`".implode('`,`', array_keys($array))."`) values('".implode("','", $array)."')"; <br/> } <br/> return $this->query($sql,true); <br/> } <br/> <br/> function mysql_delete($tbname,$idarray,$filedname='id') <br/> { <br/> $idwhere=is_array($idarray)?implode(',',$idarray):intval($idarray); <br/> $where=is_array($idarray)?"{$tbname}.{$filedname} in ({$idwhere})":" {$tbname}.{$filedname}={$idwhere}"; <br/> <br/> return $this->query("delete from {$tbname} where {$where}",true); <br/> } <br/> <br/> function get_fields($table) <br/> { <br/> $fields=array(); <br/> $result=$this->fetch_all("show columns from `{$table}`"); <br/> foreach($result as $val) <br/> { <br/> $fields[]=$val['field']; <br/> } <br/> return $fields; <br/> } <br/> <br/> function get_table_status($database) <br/> { <br/> $status=array(); <br/> $r=$this->fetch_all("show table status from `".$database."`"); /////// show table status的性质与show table类似，不过，可以提供每个表的大量信息。 <br/> foreach($r as $v) <br/> { <br/> $status[]=$v; <br/> } <br/> return $status; <br/> } <br/> <br/> function get_one_table_status($table) <br/> { <br/> return $this->fetch_one("show table status like '$table'"); <br/> } <br/> <br/> function create_fields($tbname,$fieldname,$size=0,$type='varchar') // 2010-5-14 修正一下 <br/> { <br/> if($size) <br/> { <br/> $size=strtoupper($type)=='varchar'?$size:8; <br/> $this->query("alter table `{$tbname}` add `$fieldname` {$type}( {$size} ) not null",true); <br/> } <br/> else $this->query("alter table `{$tbname}` add `$fieldname` mediumtext not null",true); <br/> return true; <br/> } <br/> <br/> function get_tables() //获取所有表表名 <br/> { <br/> $tables=array(); <br/> $r=$this->fetch_all("show tables"); <br/> foreach($r as $v) <br/> { <br/> foreach($v as $v_) <br/> { <br/> $tables[]=$v_; <br/> } <br/> } <br/> return $tables; <br/> } <br/> <br/> function create_model_table($tbname) //创建一个内容模型表（start：初始只有字段contentid int(20)，用于内容表，/////////////////////// update：2010-5-20 默认加入`content` mediumtext not null,字段） <br/> { <br/> if(in_array($tbname,$this->get_tables())) return false; ///////////////////// 当表名已经存在时，返回 false <br/> if($this->query("create table `{$tbname}` ( <br/>`contentid` mediumint(8) not null , <br/>`content` mediumtext not null, <br/>key ( `contentid` ) <br/>) engine = myisam default charset=utf8",true))return true; //////////////////// 成功则返回 true <br/> return false; //////////////失败返回 false <br/> } <br/> <br/> function create_table($tbname) //创建一个会员模型空表（初始只有字段userid int(20)，用于会员表，2010-4-26） <br/> { <br/> if(in_array($tbname,$this->get_tables())) return false; <br/> if($this->query("create table `{$tbname}` ( <br/>`userid` mediumint(8) not null , <br/>key ( `userid` ) <br/>) engine = myisam default charset=utf8",true))return true; <br/> return false; <br/> } <br/> <br/> function escape($str) // 过滤危险字符 <br/> { <br/> 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)); <br/> foreach($str as $key=>$val) $str[$key] = $this->escape($val); <br/> return $str; <br/> } <br/> <br/> 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')) <br/> { <br/> if(is_array($string)) <br/> { <br/> foreach($string as $key => $val) $string[$key] = $this->filter_pass($val, allowed_htmltags); <br/> } <br/> else <br/> { <br/> $string = preg_replace('/s('.implode('|', $disabledattributes).').*?([s>])/', '', preg_replace('/<(.*?)>/ie', "'<'.preg_replace(array('/网页特效:[^"']*/i', '/(".implode('|', $disabledattributes).")[ ]*=[ ]*["'][^"']*["']/i', '/s+/'), array('', '', ' '), stripslashes('')) . '>'", strip_tags($string, $allowedtags))); <br/> } <br/> return $string; <br/> } <br/> <br/> function drop_table($tbname) <br/> { <br/> return $this->query("drop table if exists `{$tbname}`",true); <br/> } <br/> <br/> function version() <br/> { <br/> return mysql_get_server_info($this->dblink); <br/> } <br/>} <br/></p>  </div>  <div class="mt-5">  </div> <div class="mt-5"> <p class="fc-show-prev-next"> <strong>上一篇：</strong><a href="/kaifa/show-14070467.html">怎么在thinkphp项目中响应ajax与php</a><br> </p> <p class="fc-show-prev-next"> <strong>下一篇：</strong><a href="/kaifa/show-14070475.html">利用php怎么在表单中对敏感字符进行过滤</a> </p> </div>  <div class="d-flex flex-stack mb-2 mt-10">  <h3 class="text-dark fs-5 fw-bold text-gray-800">相关内容</h3>  </div> <div class="separator separator-dashed mb-9"></div>  <div class="row g-10"> </div> </div>  </div>   <div class="col-xl-4 mt-0">  <div class="card card-flush h-md-100">  <div class="card-header pt-5 ">  <h3 class="card-title align-items-start flex-column">  <div class="d-flex align-items-center mb-2">  <span class="fs-5 fw-bold text-gray-800 ">热门资讯</span>  </div>  </h3>  </div>   <div class="card-body pt-3">  <div class="d-flex flex-stack mb-7">  <div class="symbol symbol-60px symbol-2by3 me-4"> <div class="symbol-label" style="background-image: url('/uploadfile/[db:缩略图]')"></div> </div>   <div class="m-0"> <a href="/weixiu/show-14537931.html" class="text-dark fw-bold text-hover-primary fs-6">空调连续工作多久需要停机休息</a> <span class="text-gray-600 fw-semibold d-block pt-1 fs-7">空调连续工作的时间取决于多个因素，包括空调的型号、功率、使用环境和维护情况等。一般来说，空调可以连续...</span> </div>  </div>  <div class="d-flex flex-stack mb-7">  <div class="symbol symbol-60px symbol-2by3 me-4"> <div class="symbol-label" style="background-image: url('/uploadfile/[db:缩略图]')"></div> </div>   <div class="m-0"> <a href="/weixiu/show-14537930.html" class="text-dark fw-bold text-hover-primary fs-6">空调哪个符号是除湿的</a> <span class="text-gray-600 fw-semibold d-block pt-1 fs-7">空调除湿功能通常用一个水滴形状的符号表示。不同品牌和型号的空调遥控器可能会有所差异，但一般来说，除湿...</span> </div>  </div>  <div class="d-flex flex-stack mb-7">  <div class="symbol symbol-60px symbol-2by3 me-4"> <div class="symbol-label" style="background-image: url('/uploadfile/[db:缩略图]')"></div> </div>   <div class="m-0"> <a href="/weixiu/show-14537929.html" class="text-dark fw-bold text-hover-primary fs-6">格力空调多长时间加一次氟利昂</a> <span class="text-gray-600 fw-semibold d-block pt-1 fs-7">1、一般情况下，空调对氟利昂的使用量是非常小的，所以在空调使用了四年左右的时候再考虑加也没问题。2、...</span> </div>  </div>  <div class="d-flex flex-stack mb-7">  <div class="symbol symbol-60px symbol-2by3 me-4"> <div class="symbol-label" style="background-image: url('/uploadfile/[db:缩略图]')"></div> </div>   <div class="m-0"> <a href="/weixiu/show-14537928.html" class="text-dark fw-bold text-hover-primary fs-6">空调用多长时间需要加氟一次</a> <span class="text-gray-600 fw-semibold d-block pt-1 fs-7">一般情况下是五年。当出现以下这几种情况时，空调才需要加氟。1、空调的室内机和室外机各有两个接口，当接...</span> </div>  </div>  <div class="d-flex flex-stack mb-7">  <div class="symbol symbol-60px symbol-2by3 me-4"> <div class="symbol-label" style="background-image: url('/uploadfile/202609/83c0c0800a58e3b.jpg')"></div> </div>   <div class="m-0"> <a href="/news/show-14537927.html" class="text-dark fw-bold text-hover-primary fs-6">格调生活·C标思享荟：卡萨帝在...</a> <span class="text-gray-600 fw-semibold d-block pt-1 fs-7">9月12日的郑州龙湖金融岛，夜风轻拂，灯光璀璨。“C标时刻共传奇——卡萨帝思享荟暨20周年品牌盛典郑...</span> </div>  </div>  <div class="d-flex flex-stack mb-7">  <div class="symbol symbol-60px symbol-2by3 me-4"> <div class="symbol-label" style="background-image: url('/uploadfile/202609/bd5bdfc6ba7da8c.jpg')"></div> </div>   <div class="m-0"> <a href="/news/show-14537926.html" class="text-dark fw-bold text-hover-primary fs-6">品牌价值破千亿、高端份额持续领...</a> <span class="text-gray-600 fw-semibold d-block pt-1 fs-7">9月12日，“C标时刻共传奇——卡萨帝思享荟暨20周年品牌盛典郑州站”在郑州龙湖金融岛举办。2026...</span> </div>  </div>  <div class="d-flex flex-stack mb-7">  <div class="symbol symbol-60px symbol-2by3 me-4"> <div class="symbol-label" style="background-image: url('/uploadfile/[db:缩略图]')"></div> </div>   <div class="m-0"> <a href="/weixiu/show-14537925.html" class="text-dark fw-bold text-hover-primary fs-6">空调主板坏了修800还是换新的</a> <span class="text-gray-600 fw-semibold d-block pt-1 fs-7">空调主板坏了是修还是换新，需要考虑多个因素，以下是一些建议：1. 维修成本：如果维修费用较低，比如 ...</span> </div>  </div>  <div class="d-flex flex-stack mb-7">  <div class="symbol symbol-60px symbol-2by3 me-4"> <div class="symbol-label" style="background-image: url('/uploadfile/[db:缩略图]')"></div> </div>   <div class="m-0"> <a href="/weixiu/show-14537924.html" class="text-dark fw-bold text-hover-primary fs-6">空调开多久需要休息多长时间</a> <span class="text-gray-600 fw-semibold d-block pt-1 fs-7">问题：空调开多久需要休息多长时间回答：1、在使用空调的过程中，一般空调开7~8个小时左右需要休息2~...</span> </div>  </div>  <div class="d-flex flex-stack mb-7">  <div class="symbol symbol-60px symbol-2by3 me-4"> <div class="symbol-label" style="background-image: url('/uploadfile/[db:缩略图]')"></div> </div>   <div class="m-0"> <a href="/weixiu/show-14537923.html" class="text-dark fw-bold text-hover-primary fs-6">中央空调多长时间清洗一次</a> <span class="text-gray-600 fw-semibold d-block pt-1 fs-7">1、一般空调开机前清洗一次即可，空调开机中间时段再清洗一次，空调关机时清洗一次，这样比较合理。2、空...</span> </div>  </div>  <div class="d-flex flex-stack mb-7">  <div class="symbol symbol-60px symbol-2by3 me-4"> <div class="symbol-label" style="background-image: url('/uploadfile/[db:缩略图]')"></div> </div>   <div class="m-0"> <a href="/weixiu/show-14537922.html" class="text-dark fw-bold text-hover-primary fs-6">中央空调氟利昂多久加一次</a> <span class="text-gray-600 fw-semibold d-block pt-1 fs-7">看空调制热和制冷程度，很多人的观点认为氟利昂必须一年放一次，其实这个观点是错误的，理解上面有点偏差，...</span> </div>  </div> </div>  </div>  </div>  </div> </div>  </div>  </div>   <div id="kt_app_footer" class="app-footer">  <div class="app-container container-xxl d-flex flex-column flex-md-row flex-center flex-md-stack py-3">  <div class="text-dark order-2 order-md-1"> <span class="text-muted fw-semibold me-1">2026 ©</span> 太卓网<a href="http://www.yexian114.com/">叶县生活网</a> <a href="https://www.wxx86.cn/">编程知识网</a> <a href="http://www.kcwzh.com/">开创游戏网</a> <a href="https://www.caiding5.net">菜丁网</a><a href="http://cn.office369.com">奥飞商务网</a> <a href="http://www.zlnznjj.com/">众乐知识网</a><a href="http://www.lhjia.com/">乐活家</a><a href="http://www.nengyuan100.com/">能源100网</a> <a href="http://it.tdroid.net/">泰达科技</a><a href="http://cn.tdroid.net/">太卓开发网</a><a href="http://cn.yuansudz.com/">元素科技网</a><a href="http://www.80hlw.com/">八零商务网</a><a href="http://game.tdroid.net/">土豆游戏网</a> </div>   <ul class="menu menu-gray-600 menu-hover-primary fw-semibold order-1"> <li class="menu-item"> <a href="/news" target="_blank" class="menu-link px-2">科技资讯</a> </li> <li class="menu-item"> <a href="/jishu" target="_blank" class="menu-link px-2">技术分享</a> </li> <li class="menu-item"> <a href="/kaifa" target="_blank" class="menu-link px-2">程序开发</a> </li> <li class="menu-item"> <a href="/weixiu" target="_blank" class="menu-link px-2">设备维修</a> </li> </ul>  </div>  </div>  </div>  </div>  </div>  </div>  <div id="kt_scrolltop" class="scrolltop" data-kt-scrolltop="true">  <span class="svg-icon"> <svg width="24" height="24" viewBox="0 0 24 24" fill="none" xmlns="http://www.w3.org/2000/svg"> <rect opacity="0.5" x="13" y="6" width="13" height="2" rx="1" transform="rotate(90 13 6)" fill="currentColor"></rect> <path d="M12.5657 8.56569L16.75 12.75C17.1642 13.1642 17.8358 13.1642 18.25 12.75C18.6642 12.3358 18.6642 11.6642 18.25 11.25L12.7071 5.70711C12.3166 5.31658 11.6834 5.31658 11.2929 5.70711L5.75 11.25C5.33579 11.6642 5.33579 12.3358 5.75 12.75C6.16421 13.1642 6.83579 13.1642 7.25 12.75L11.4343 8.56569C11.7467 8.25327 12.2533 8.25327 12.5657 8.56569Z" fill="currentColor"></path> </svg> </span>  </div>  <script>var hostUrl = "/static/default/pc/";</script>  <script src="/static/default/pc/plugins/global/plugins.bundle.js"></script> <script src="/static/default/pc/js/scripts.bundle.js"></script>   </body>  </html>