爱资料logo
主页 > 编程资料 > PHP >

一款简单实用的php操作mysql数据库类

发布时间：2015-12-29 作者：网络 阅读：186次

本文实例讲述了一款简单实用的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><h1><h2><h3><h4><h5><h6><font><u><i><b><strong><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><p><br/></p> </div> <div style="padding-top:20px;"></div> <div class='tag'> 关键字词: </div> <div class="clear"></div> <div class='xg'> <h3 class="xiangguantuijian">相关文章</h3> <ul class="xg-ul"> <li><a class="fl" title="关于Yii2高级版多后台开发实例说明" href="https://www.apizl.com/archives/view-148766-1.html">关于Yii2高级版多后台开发实例说明</a></li> <li><a class="fl" title="PHP解码HTML实体字符串类似于&amp;#这种" href="https://www.apizl.com/archives/view-134152-1.html">PHP解码HTML实体字符串类似于&#这种</a></li> <li><a class="fl" title="PHP使用内置函数简单获取url中host协议类型参数等等" href="https://www.apizl.com/archives/view-134101-1.html">PHP使用内置函数简单获取url中host协议类型参数等等</a></li> <li><a class="fl" title="PHP字符串中替换换行符为HTML换行最简单方法" href="https://www.apizl.com/archives/view-134092-1.html">PHP字符串中替换换行符为HTML换行最简单方法</a></li> <li><a class="fl" title="PHP 开发中解决并发问题的几种实现方法分析_编程资料分享" href="https://www.apizl.com/archives/view-133925-1.html">PHP 开发中解决并发问题的几种实现方法分析_编程资料分享</a></li> <li><a class="fl" title="PHP判断json格式是否正确的实现代码" href="https://www.apizl.com/archives/view-133908-1.html">PHP判断json格式是否正确的实现代码</a></li> <li><a class="fl" title="如何直接访问php实例对象的private属性" href="https://www.apizl.com/archives/view-133907-1.html">如何直接访问php实例对象的private属性</a></li> <li><a class="fl" title="PHP下服务器做了反向代理要拿到用户的真实IP方法" href="https://www.apizl.com/archives/view-133853-1.html">PHP下服务器做了反向代理要拿到用户的真实IP方法</a></li> <li><a class="fl" title="php 中奖概率算法实现代码" href="https://www.apizl.com/archives/view-133790-1.html">php 中奖概率算法实现代码</a></li> <li><a class="fl" title="php中简单的对称加密算法实现" href="https://www.apizl.com/archives/view-132993-1.html">php中简单的对称加密算法实现</a></li> </ul> </div> </div> <div class="clear"></div> </div> </div> <div id="baidu_div" class="t-10 label-div border-all"> </div> <!--讨论区--> <div id="comment_div" class="t-10 label-div border-all pr-20 pl-20"> <!-- 评论箱 --> </div> <!--讨论区结束--> </div> <!--右侧代码--> <div class="span4"> <!--栏目导航--> <div class="label-div t-5 border-all"> <div class="label-main tody-hot l-15"> <div class="lanmu-div"> <h3 class="label-title border-b h3-h">栏目导航</h3> <ul> <li><a href='https://www.apizl.com/category/list-9.html' target="_blank">PHP</a></li> <li><a href='https://www.apizl.com/category/list-2.html' target="_blank">C#</a></li> <li><a href='https://www.apizl.com/category/list-139.html' target="_blank">Android</a></li> <li><a href='https://www.apizl.com/category/list-322.html' target="_blank">JSP(JAVA)</a></li> <li><a href='https://www.apizl.com/category/list-83.html' target="_blank">前端教程</a></li> <li><a href='https://www.apizl.com/category/list-158.html' target="_blank">Delphi</a></li> <li><a href='https://www.apizl.com/category/list-10.html' target="_blank">Javascript</a></li> <li><a href='https://www.apizl.com/category/list-354.html' target="_blank">Yii</a></li> <li><a href='https://www.apizl.com/category/list-346.html' target="_blank">微信小程序</a></li> <li><a href='https://www.apizl.com/category/list-425.html' target="_blank">百度小程序</a></li> <li><a href='https://www.apizl.com/category/list-326.html' target="_blank">Python</a></li> <li><a href='https://www.apizl.com/category/list-324.html' target="_blank">ASP.NET</a></li> <li><a href='https://www.apizl.com/category/list-65.html' target="_blank">单片机</a></li> <li><a href='https://www.apizl.com/category/list-155.html' target="_blank">WebApp编程</a></li> <li><a href='https://www.apizl.com/category/list-341.html' target="_blank">nodejs</a></li> <li><a href='https://www.apizl.com/category/list-325.html' target="_blank">正则处理</a></li> <li><a href='https://www.apizl.com/category/list-15.html' target="_blank">Dedecms</a></li> <li><a href='https://www.apizl.com/category/list-56.html' target="_blank">ecshop</a></li> <li><a href='https://www.apizl.com/category/list-23.html' target="_blank">phpcms</a></li> <li><a href='https://www.apizl.com/category/list-66.html' target="_blank">Discuz</a></li> <li><a href='https://www.apizl.com/category/list-159.html' target="_blank">帝国CMS</a></li> <li><a href='https://www.apizl.com/category/list-160.html' target="_blank">WordPress</a></li> <li><a href='https://www.apizl.com/category/list-117.html' target="_blank">易语言</a></li> <li><a href='https://www.apizl.com/category/list-356.html' target="_blank">GO语言</a></li> <li><a href='https://www.apizl.com/category/list-357.html' target="_blank">GIT使用</a></li> <li><a href='https://www.apizl.com/category/list-113.html' target="_blank">Arduino</a></li> <li><a href='https://www.apizl.com/category/list-380.html' target="_blank">VBS</a></li> <li><a href='https://www.apizl.com/category/list-381.html' target="_blank">Cmd批处理</a></li> <li><a href='https://www.apizl.com/category/list-327.html' target="_blank">编程更多</a></li> </ul> </div> </div> </div> <div class="label-div t-5 border-all" style="text-align: center;"> <a href="https://www.taomizhan.com/?apizl_view" target="_blank"><img src="/public/youce.jpg"></a> </div> <!--最新文章--> <div class="label-div t-5 border-all"> <div class="label-main tody-hot l-15"> <h3 class="label-title border-b h3-h">最新文章</h3> <ul> <li class="li-w"><a title="yii2中引入三方类库 " href="https://www.apizl.com/archives/view-148792-1.html">yii2中引入三方类库</a></li> <li class="li-w"><a title="thinkphp where中or多个like模糊搜索 " href="https://www.apizl.com/archives/view-148787-1.html">thinkphp where中or多个like模糊搜索</a></li> <li class="li-w"><a title="mysql 启动提示Plugin 'InnoDB' init functi " href="https://www.apizl.com/archives/view-148786-1.html">mysql 启动提示Plugin 'InnoDB' init functi</a></li> <li class="li-w"><a title="php 网站域名被墙判断请求方法 " href="https://www.apizl.com/archives/view-148783-1.html">php 网站域名被墙判断请求方法</a></li> <li class="li-w"><a title="php中的php.ini设置session超时 " href="https://www.apizl.com/archives/view-148782-1.html">php中的php.ini设置session超时</a></li> <li class="li-w"><a title="php 条形图 树状图 使用GD库生成 非js chart " href="https://www.apizl.com/archives/view-148778-1.html">php 条形图 树状图 使用GD库生成 非js chart</a></li> </ul> </div> </div> <!--相关文章--> <div class="label-div t-5 border-all"> <div class="label-main tody-hot l-15"> <h3 class="label-title border-b h3-h">点击排行</h3> <ul> <li class="li-w"><a title="让自己网站对接google谷歌第三方登录接口详解说明 " href="https://www.apizl.com/archives/view-148749-1.html">让自己网站对接google谷歌第三方登录接口详解说明</a></li> <li class="li-w"><a title="shopnc修改原来的后台菜单新增功能（详解） " href="https://www.apizl.com/archives/view-132884-1.html">shopnc修改原来的后台菜单新增功能（详解）</a></li> <li class="li-w"><a title="php实现mysql数据库分表分段备份 " href="https://www.apizl.com/archives/view-47389-1.html">php实现mysql数据库分表分段备份</a></li> <li class="li-w"><a title="sphinx 建立索引这样提示 无法启动FATAL: out of memory (unable to allocate 802217663 bytes) " href="https://www.apizl.com/archives/view-32218-1.html">sphinx 建立索引这样提示 无法启动FATAL: out of memory (unable to allocate 802217663 bytes)</a></li> <li class="li-w"><a title="PhpMyAdmin出现export.php Missing parameter: what /export " href="https://www.apizl.com/archives/view-41905-1.html">PhpMyAdmin出现export.php Missing parameter: what /export</a></li> <li class="li-w"><a title="php使用xpath来进行采集页面的内容 " href="https://www.apizl.com/archives/view-134324-1.html">php使用xpath来进行采集页面的内容</a></li> </ul> </div> </div> <!--推荐文章--> <div class="label-div t-5 border-all"> <div class="label-main tody-hot l-15"> <h3 class="label-title border-b h3-h">推荐文章</h3> <ul> <li class="li-w"><a title="PHP、C# RSA加密交互问题解决方法 " href="https://www.apizl.com/archives/view-134283-1.html">PHP、C# RSA加密交互问题解决方法</a></li> <li class="li-w"><a title="PHP网站被黑下马处理以及防黑大全解读 " href="https://www.apizl.com/archives/view-148757-1.html">PHP网站被黑下马处理以及防黑大全解读</a></li> <li class="li-w"><a title="php下json_encode使用gbk输出时候null无输出内容解决办法 " href="https://www.apizl.com/archives/view-141566-1.html">php下json_encode使用gbk输出时候null无输出内容解决办法</a></li> <li class="li-w"><a title="window下redis和memache扩展的安装 " href="https://www.apizl.com/archives/view-134164-1.html">window下redis和memache扩展的安装</a></li> <li class="li-w"><a title="shopnc数据库处理的事务调用 " href="https://www.apizl.com/archives/view-132892-1.html">shopnc数据库处理的事务调用</a></li> </ul> </div> </div> <!--打赏--> <div class="label-div t-5 border-all"> <div class="label-main tody-hot l-15"> <h3 class="label-title border-b h3-h">打赏</h3> <img src="/public/weixin.png" width="120" alt="weixin"> <img src="/public/alipay.png" width="120" alt="alipay"> </div> </div> </div> </div> </div> <div class="footer"> <div class="clearfix" style="background:#EDEDED;"> </div> <p> <br/> Copyright ©2014 apizl.com    <a href="http://beian.miit.gov.cn/" rel="nofollow" target="_blank">粤ICP备15076105号-1</a>   本站运行: 3756天 <script async src="https://www.googletagmanager.com/gtag/js?id=UA-131433579-3"></script> <script> window.dataLayer = window.dataLayer || []; function gtag(){dataLayer.push(arguments);} gtag('js', new Date()); gtag('config', 'UA-131433579-3'); </script> <br/> <font>网页模板、字体、软件、资料、资源部分是从国外大小网站收集而来，为朋友们在工作或学习时提高效率、节省时间.</font> <BR/> <font>站内所有资源仅供学习与参考，请勿用于商业用途，否则产生的一切后果将由您自己承担！ </font> <br/> </p> </div> <script type="text/javascript" src="/js/bioV4.min.js"></script> <script type="text/javascript" src="/js/jquery.lazyload.mini.js"></script> <script type="text/javascript" src="/js/function.js"></script> <script type="text/javascript" src="/js/borsertocss.js"></script> <script src="/public/mobile/index/scripts/postbird-img-glass.js"></script> <link href="/include/ueditor/third-party/SyntaxHighlighter/shCoreDefault.css" rel="stylesheet" type="text/css"/> <script type="text/javascript" src="/include/ueditor/third-party/SyntaxHighlighter/shCore.js"></script> <script type="text/javascript" src="//cpro.baidustatic.com/cpro/ui/c.js" async="async" defer="defer" ></script> <script type="text/javascript"> SyntaxHighlighter.all(); PostbirdImgGlass.init({ domSelector: ".view-content img", animation: true }); </script> <script src="https://www.apizl.com/plus/count.php?view=yes&aid=45944&mid=18" type='text/javascript' language="javascript"></script> <script type="text/javascript"> /**baidu tongji**/ (function () { var hm = document.createElement("script"); hm.src = "//hm.baidu.com/hm.js?5c3af8dba9537e88753b3508ea2eb9fa"; var s = document.getElementsByTagName("script")[0]; s.parentNode.insertBefore(hm, s); })(); /**360**/ (function(){ var src = (document.location.protocol == "http:") ? "http://js.passport.qihucdn.com/11.0.1.js?ff16c6ce0bb415bdcb40cdd7adf44451":"https://jspassport.ssl.qhimg.com/11.0.1.js?ff16c6ce0bb415bdcb40cdd7adf44451"; document.write('<script src="' + src + '" id="sozz"><\/script>'); })(); </script> </body> </html>