Home php教程 php手册 一款实用的php mysql数据库连接类

一款实用的php mysql数据库连接类

Jun 13, 2016 am 10:11 AM
mysql php load Tutorial database use kind automatic connect

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>



    • 一款实用的php mysql数据库连接类
      ');
        $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.'
      ';
        $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); // mb.bKjia.c0m 升级一下,增加判断字段是否存在
        
        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); // www.bKjia.c0m,增加判断字段是否存在
         
         $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>])/', '2', preg_replace('//ie', "''", 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);
       }
      }

    Statement of this Website
    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

    Hot AI Tools

    Undresser.AI Undress

    Undresser.AI Undress

    AI-powered app for creating realistic nude photos

    AI Clothes Remover

    AI Clothes Remover

    Online AI tool for removing clothes from photos.

    Undress AI Tool

    Undress AI Tool

    Undress images for free

    Clothoff.io

    Clothoff.io

    AI clothes remover

    Video Face Swap

    Video Face Swap

    Swap faces in any video effortlessly with our completely free AI face swap tool!

    Hot Tools

    Notepad++7.3.1

    Notepad++7.3.1

    Easy-to-use and free code editor

    SublimeText3 Chinese version

    SublimeText3 Chinese version

    Chinese version, very easy to use

    Zend Studio 13.0.1

    Zend Studio 13.0.1

    Powerful PHP integrated development environment

    Dreamweaver CS6

    Dreamweaver CS6

    Visual web development tools

    SublimeText3 Mac version

    SublimeText3 Mac version

    God-level code editing software (SublimeText3)

    PHP's Purpose: Building Dynamic Websites PHP's Purpose: Building Dynamic Websites Apr 15, 2025 am 12:18 AM

    PHP is used to build dynamic websites, and its core functions include: 1. Generate dynamic content and generate web pages in real time by connecting with the database; 2. Process user interaction and form submissions, verify inputs and respond to operations; 3. Manage sessions and user authentication to provide a personalized experience; 4. Optimize performance and follow best practices to improve website efficiency and security.

    PHP and Python: Different Paradigms Explained PHP and Python: Different Paradigms Explained Apr 18, 2025 am 12:26 AM

    PHP is mainly procedural programming, but also supports object-oriented programming (OOP); Python supports a variety of paradigms, including OOP, functional and procedural programming. PHP is suitable for web development, and Python is suitable for a variety of applications such as data analysis and machine learning.

    PHP and Python: Code Examples and Comparison PHP and Python: Code Examples and Comparison Apr 15, 2025 am 12:07 AM

    PHP and Python have their own advantages and disadvantages, and the choice depends on project needs and personal preferences. 1.PHP is suitable for rapid development and maintenance of large-scale web applications. 2. Python dominates the field of data science and machine learning.

    Why Use PHP? Advantages and Benefits Explained Why Use PHP? Advantages and Benefits Explained Apr 16, 2025 am 12:16 AM

    The core benefits of PHP include ease of learning, strong web development support, rich libraries and frameworks, high performance and scalability, cross-platform compatibility, and cost-effectiveness. 1) Easy to learn and use, suitable for beginners; 2) Good integration with web servers and supports multiple databases; 3) Have powerful frameworks such as Laravel; 4) High performance can be achieved through optimization; 5) Support multiple operating systems; 6) Open source to reduce development costs.

    PHP: Handling Databases and Server-Side Logic PHP: Handling Databases and Server-Side Logic Apr 15, 2025 am 12:15 AM

    PHP uses MySQLi and PDO extensions to interact in database operations and server-side logic processing, and processes server-side logic through functions such as session management. 1) Use MySQLi or PDO to connect to the database and execute SQL queries. 2) Handle HTTP requests and user status through session management and other functions. 3) Use transactions to ensure the atomicity of database operations. 4) Prevent SQL injection, use exception handling and closing connections for debugging. 5) Optimize performance through indexing and cache, write highly readable code and perform error handling.

    Choosing Between PHP and Python: A Guide Choosing Between PHP and Python: A Guide Apr 18, 2025 am 12:24 AM

    PHP is suitable for web development and rapid prototyping, and Python is suitable for data science and machine learning. 1.PHP is used for dynamic web development, with simple syntax and suitable for rapid development. 2. Python has concise syntax, is suitable for multiple fields, and has a strong library ecosystem.

    How to start mysql by docker How to start mysql by docker Apr 15, 2025 pm 12:09 PM

    The process of starting MySQL in Docker consists of the following steps: Pull the MySQL image to create and start the container, set the root user password, and map the port verification connection Create the database and the user grants all permissions to the database

    MySQL's Role: Databases in Web Applications MySQL's Role: Databases in Web Applications Apr 17, 2025 am 12:23 AM

    The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.

    See all articles