Home > Article > Backend Development > A practical php mysql database connection class_PHP tutorial

A practical php mysql database connection class_PHP tutorial

WBOYOriginal: 2016-07-13 17:09:31946browse

php tutorial mysql tutorial database tutorial connection class
This database connection class will automatically load the sql anti-injection function, filter some sensitive sql query keywords, and can also add judgment fields such as the nature of show table status and the show table class to obtain all table names in the database, etc. */
@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><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> }</p> <p> 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('The following error occurred when executing the sql statement '.$sql.':'.mysql_error());<br> return $this->rs;<br> }</p> <p> function fetch_one($sql)<br> {<br> $this->rs=$this->query($sql);<br> Return dircms_strips tutorial lashes($this->filter_pass(mysql_fetch_array($this->rs,mysql_assoc)));<br> }</p> <p> function get_maxfield($filed='id',$table) // Get the maximum value of $filed field in $table table<br> {<br> $r=$this->fetch_one("select {$table}.{$filed} from `{$table}` order by `{$table}`.`{$filed}` desc limit 0,1") ;<br> Return $r[$filed];<br> }</p> <p> 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> }</p> <p> 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> }</p> <p> function last_insert_id()<br> {<br> if(($insertid=mysql_insert_id($this->dblink))>0)return $insertid;<br> else //If the column type of auto_increment is bigint, the value returned by mysql_insert_id() will be incorrect.<br> {<br> $result=$this->fetch_one('select last_insert_id() as insertid');<br> Return $result['insertid'];<br> }<br> }</p> <p> function insert($tbname,$varray,$replace=false)<br> {<br> $varray=$this->escape($varray);<br> $tb_fields=$this->get_fields($tbname); // mb.bKjia.c0m 升级一下，增加判断字段是否存在<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> }</p> <p> 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> }</p> <p> function update($tbname, $array, $where = '')<br> {<br> $array=$this->escape($array);<br> if($where)<br> {<br> $tb_fields=$this->get_fields($tbname); // www.bKjia.c0m，增加判断字段是否存在<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}";</p> <p> return $this->query("delete from {$tbname} where {$where}",true);<br> }</p> <p> 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> }</p> <p> 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> }</p> <p> function get_one_table_status($table)<br> {<br> return $this->fetch_one("show table status like '$table'");<br> }</p> <p> 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> }</p> <p> 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> }</p> <p> function create_model_table($tbname) //Create a content model table (start: initially only the field contentid int(20), used for content table, ///////////////// ////// update: 2010-5-20 By default, `content` mediumtext not null, field) is added <br> {<br> if(in_array($tbname,$this->get_tables())) return false; /////////////////////// When the table name already exists, return 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; ///////////////////// Return true on success<br> Return false; //////////////Failure returns false<br> }</p> <p> function create_table($tbname) //Create an empty table of member model (initially only the field userid int(20), used for member table, 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> }</p> <p> function escape($str) // Filter dangerous characters<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> }</p> <p> 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', 'onmouseotutorialver', '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>])/', '2', preg_replace('/<(.*?) >/ie', "'<'.preg_replace(array('/Web page effects:[^"']*/i', '/(".implode('|', $disabledattributes).")[ tn ]*=[ tn]*["'][^"']*["']/i', '/s+/'), array('', '', ' '), stripslashes('1') ) . '>'", strip_tags($string, $allowedtags)));<br> }<br> return $string;<br> }</p> <p> function drop_table($tbname)<br> {<br> Return $this->query("drop table if exists `{$tbname}`",true);<br> }</p> <p> function version()<br> {<br> Return mysql_get_server_info($this->dblink);<br> }<br> }</p> <p align="left"></p> <div style="display:none;"> <span id="url" itemprop="url">http://www.bkjia.com/PHPjc/629738.html</span><span id="indexUrl" itemprop="indexUrl">www.bkjia.com</span><span id="isOriginal" itemprop="isOriginal">true</span><span id="isBasedOnUrl" itemprop="isBasedOnUrl">http: //www.bkjia.com/PHPjc/629738.html</span><span id="genre" itemprop="genre">TechArticle</span><span id="description" itemprop="description">php tutorial mysql tutorial database tutorial connection class This database connection class will automatically load the sql anti-injection function and filter Some sensitive SQL query keywords, and you can also add judgment words...</span> </div> <div class="art_confoot"></div></div><div class="nphpQianMsg"><div class="clear"></div></div><div class="nphpQianSheng"><span>Statement：</span><div>The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn</div></div></div><div class="nphpSytBox"><span>Previous article：<a class="dBlack" title="Classic PHP anti-injection function code_PHP tutorial" href="http://m.php.cn/faq/299753.html">Classic PHP anti-injection function code_PHP tutorial</a></span><span>Next article：<a class="dBlack" title="Classic PHP anti-injection function code_PHP tutorial" href="http://m.php.cn/faq/299755.html">Classic PHP anti-injection function code_PHP tutorial</a></span></div><div class="nphpSytBox2"><div class="nphpZbktTitle"><h2>Related articles</h2><em><a href="http://m.php.cn/article.html" class="bBlack"><i>See more</i><b></b></a></em><div class="clear"></div></div><ul class="nphpXgwzList"><li><b></b><a href="http://m.php.cn/faq/1.html" title="How to use cURL to implement Get and Post requests in PHP" class="aBlack">How to use cURL to implement Get and Post requests in PHP</a><div class="clear"></div></li><li><b></b><a href="http://m.php.cn/faq/1.html" title="How to use cURL to implement Get and Post requests in PHP" class="aBlack">How to use cURL to implement Get and Post requests in PHP</a><div class="clear"></div></li><li><b></b><a href="http://m.php.cn/faq/1.html" title="How to use cURL to implement Get and Post requests in PHP" class="aBlack">How to use cURL to implement Get and Post requests in PHP</a><div class="clear"></div></li><li><b></b><a href="http://m.php.cn/faq/1.html" title="How to use cURL to implement Get and Post requests in PHP" class="aBlack">How to use cURL to implement Get and Post requests in PHP</a><div class="clear"></div></li><li><b></b><a href="http://m.php.cn/faq/2.html" title="All expression symbols in regular expressions (summary)" class="aBlack">All expression symbols in regular expressions (summary)</a><div class="clear"></div></li></ul></div></div><div class="nphpFoot"><div class="nphpFootBg"><ul class="nphpFootMenu"><li><a href="http://m.php.cn/"><b class="icon1"></b><p>Home</p></a></li><li><a href="http://m.php.cn/course.html"><b class="icon2"></b><p>Course</p></a></li><li><a href="http://m.php.cn/wenda.html"><b class="icon4"></b><p>Q&A</p></a></li><li><a href="http://m.php.cn/login"><b class="icon5"></b><p>My</p></a></li><div class="clear"></div></ul></div></div><div class="nphpYouBox" style="display: none;"><div class="nphpYouBg"><div class="nphpYouTitle"><span onclick="$('.nphpYouBox').hide()"></span><a href="http://m.php.cn/"></a><div class="clear"></div></div><ul class="nphpYouList"><li><a href="http://m.php.cn/"><b class="icon1"></b><span>Home</span><div class="clear"></div></a></li><li><a href="http://m.php.cn/course.html"><b class="icon2"></b><span>Course</span><div class="clear"></div></a></li><li><a href="http://m.php.cn/article.html"><b class="icon3"></b><span>Article</span><div class="clear"></div></a></li><li><a href="http://m.php.cn/wenda.html"><b class="icon4"></b><span>Q&A</span><div class="clear"></div></a></li><li><a href="http://m.php.cn/dic.html"><b class="icon6"></b><span>Dictionary</span><div class="clear"></div></a></li><li><a href="http://m.php.cn/course/type/99.html"><b class="icon7"></b><span>Manual</span><div class="clear"></div></a></li><li><a href="http://m.php.cn/xiazai/"><b class="icon8"></b><span>Download</span><div class="clear"></div></a></li><li><a href="http://m.php.cn/faq/zt" title="Topic"><b class="icon12"></b><span>Topic</span><div class="clear"></div></a></li><div class="clear"></div></ul></div></div><div class="nphpDing" style="display: none;"><div class="nphpDinglogo"><a href="http://m.php.cn/"></a></div><div class="nphpNavIn1"><div class="swiper-container nphpNavSwiper1"><div class="swiper-wrapper"><div class="swiper-slide"><a href="http://m.php.cn/" >Home</a></div><div class="swiper-slide"><a href="http://m.php.cn/article.html" class="hover">Article</a></div><div class="swiper-slide"><a href="http://m.php.cn/wenda.html" >Q&A</a></div><div class="swiper-slide"><a href="http://m.php.cn/course.html" >Course</a></div><div class="swiper-slide"><a href="http://m.php.cn/faq/zt" >Topic</a></div><div class="swiper-slide"><a href="http://m.php.cn/xiazai" >Download</a></div><div class="swiper-slide"><a href="http://m.php.cn/game" >Game</a></div><div class="swiper-slide"><a href="http://m.php.cn/dic.html" >Dictionary</a></div><div class="clear"></div></div></div><div class="langadivs" ><a href="javascript:;" class="bg4 bglanguage"></a><div class="langadiv" ><a onclick="javascript:setlang('zh-cn');" class="language course-right-orders chooselan " href="javascript:;"><span>简体中文</span><span>（ZH-CN）</span></a><a onclick="javascript:;" class="language course-right-orders chooselan chooselanguage" href="javascript:;"><span>English</span><span>（EN）</span></a><a onclick="javascript:setlang('zh-tw');" class="language course-right-orders chooselan " href="javascript:;"><span>繁体中文</span><span>（ZH-TW）</span></a><a onclick="javascript:setlang('ja');" class="language course-right-orders chooselan " href="javascript:;"><span>日本語</span><span>（JA）</span></a><a onclick="javascript:setlang('ko');" class="language course-right-orders chooselan " href="javascript:;"><span>한국어</span><span>（KO）</span></a><a onclick="javascript:setlang('ms');" class="language course-right-orders chooselan " href="javascript:;"><span>Melayu</span><span>（MS）</span></a><a onclick="javascript:setlang('fr');" class="language course-right-orders chooselan " href="javascript:;"><span>Français</span><span>（FR）</span></a><a onclick="javascript:setlang('de');" class="language course-right-orders chooselan " href="javascript:;"><span>Deutsch</span><span>（DE）</span></a></div></div><script> var swiper = new Swiper('.nphpNavSwiper1', { slidesPerView : 'auto', observer: true,//修改swiper自己或子元素时，自动初始化swiper observeParents: true,//修改swiper的父元素时，自动初始化swiper }); </script></div></div><script>isLogin = 0;</script><script type="text/javascript" src="/static/layui/layui.js"></script><script type="text/javascript" src="/static/js/global.js?4.9.47"></script></div><script src="https://vdse.bdstatic.com//search-video.v1.min.js"></script><link rel='stylesheet' id='_main-css' href='/static/css/viewer.min.css' type='text/css' media='all'/><script type='text/javascript' src='/static/js/viewer.min.js?1'></script><script type='text/javascript' src='/static/js/jquery-viewer.min.js'></script><script>jQuery.fn.wait = function (func, times, interval) { var _times = times || -1, //100次 _interval = interval || 20, //20毫秒每次 _self = this, _selector = this.selector, //选择器 _iIntervalID; //定时器id if( this.length ){ //如果已经获取到了，就直接执行函数 func && func.call(this); } else { _iIntervalID = setInterval(function() { if(!_times) { //是0就退出 clearInterval(_iIntervalID); } _times <= 0 || _times--; //如果是正数就 -- _self = $(_selector); //再次选择 if( _self.length ) { //判断是否取到 func && func.call(_self); clearInterval(_iIntervalID); } }, _interval); } return this; } $("table.syntaxhighlighter").wait(function() { $('table.syntaxhighlighter').append("<p class='cnblogs_code_footer'><span class='cnblogs_code_footer_icon'></span></p>"); }); $(document).on("click", ".cnblogs_code_footer",function(){ $(this).parents('table.syntaxhighlighter').css('display','inline-table');$(this).hide(); }); $('.nphpQianCont').viewer({navbar:true,title:false,toolbar:false,movable:false,viewed:function(){$('img').click(function(){$('.viewer-close').trigger('click');});}}); </script></body></html>