search

MySQL操作类

May 25, 2016 pm 05:12 PM

<?php
header("content-type:text/html;charset:utf-8");
require_once &#39;mysql.class.php&#39;;
$mysql = new MySQL(&#39;localhost&#39;,&#39;user&#39;,&#39;password&#39;,&#39;test&#39;);
echo &#39;<pre class="brush:php;toolbar:false">&#39;;
//获取表字段
//print_r($mysql->getFields(&#39;test&#39;));
//增
echo $mysql->data(array(&#39;name&#39;=>&#39;test&#39;,&#39;password&#39;=>&#39;123456&#39;))->table(&#39;test&#39;)->add();
//删
echo $mysql->table(&#39;test&#39;)->where(&#39;id=1&#39;)->delete();
//改
echo $mysql->table(&#39;test&#39;)->data(array(&#39;name&#39;=>&#39;bbbbbbbbbbbb&#39;))->where(&#39;id<3&#39;)->update();
//查
print_r($mysql->table(&#39;test&#39;)->where(&#39;id=4&#39;)->select());
print_r($mysql->table(&#39;test&#39;)->order(&#39;id desc&#39;)->select());
//
$mysql->query(&#39;select * from `test`&#39;);
$mysql->execute(&#39;update `test` set password = 123&#39;);
echo &#39;
'; echo '查询次数:'.$mysql->query_count.'
'; echo '查询时间:'.number_format(microtime(true)-($mysql->query_start_time),10).' 秒
'; echo '错误信息:'.$mysql->error().'
'; ?>

2. mysql.class.php 

<?php
// +----------------------------------------------------------------------
// |MySQL操作类
// +----------------------------------------------------------------------
// | Author: justmepzy(justmepzy@gmail.com)
// +----------------------------------------------------------------------
class MySQL{
    
    private $db_mysql_hostname;
    private $db_mysql_username;
    private $db_mysql_password;
    private $db_mysql_database;
    private $db_mysql_port;
    private $db_mysql_charset;
    
    private $query_list = array();
    
    //查询次数
    public $query_count = 0;
    //查询开始时间
    public $query_start_time;
    
    //当前查询ID
    protected $queryID;
    //当前连接
    protected $conn;
    // 事务指令数
    protected $transTimes = 0;
    // 返回或者影响记录数
    protected $numRows    = 0;
    // 错误信息
    protected $error      = &#39;&#39;;
    
    public function __construct($hostname_or_conf,$username,$password,$database,$port = &#39;3306&#39;,$char = &#39;utf8&#39;){
        if(is_array($hostname_or_conf)){
            $this->db_mysql_hostname = $hostname_or_conf[&#39;hostname&#39;];
            $this->db_mysql_username = $hostname_or_conf[&#39;username&#39;];
            $this->db_mysql_password = $hostname_or_conf[&#39;password&#39;];
            $this->db_mysql_database = $hostname_or_conf[&#39;database&#39;];
            $this->db_mysql_port = isset($hostname_or_conf[&#39;port&#39;])?$hostname_or_conf[&#39;port&#39;]:&#39;3306&#39;;
            $this->db_mysql_charset = isset($hostname_or_conf[&#39;charset&#39;])?$hostname_or_conf[&#39;charset&#39;]:&#39;utf8&#39;;
            
        }elseif(!empty($hostname_or_conf)||!empty($username)||!empty($password)||!empty($database))
        {
             $this->db_mysql_hostname = $hostname_or_conf;
             $this->db_mysql_username = $username;
             $this->db_mysql_password = $password;
             $this->db_mysql_database = $database;
             $this->db_mysql_port = $port;
             $this->db_mysql_charset = $char;
             
        }else{
            die(&#39;configuration error.&#39;);
        }
        $this->connect();
    }
    
    private function connect(){
        $server = $this->db_mysql_hostname.&#39;:&#39;.$this->db_mysql_port;
        $this->conn = mysql_connect($server,$this->db_mysql_username,$this->db_mysql_password,true) or die(&#39;Connect MySQL DB error!&#39;);
        mysql_select_db($this->db_mysql_database,$this->conn) or die(&#39;select db error!&#39;);
        mysql_query("set names " . $this->db_mysql_charset, $this->conn);
    }
    /**
     +----------------------------------------------------------
     * 设置数据对象值
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     *table,where,order,limit,data,field,join,group,having
     +----------------------------------------------------------
     */
    public function table($table){
        $this->query_list[&#39;table&#39;] = $table;
        return $this;
    }
    
    public function where($where){
        $this->query_list[&#39;where&#39;] = $where;
        return $this;
    }
    
    public function order($order){
        $this->query_list[&#39;order&#39;] = $order;
        return $this;
    }
    
    public function limit($offset,$length){
        if(!isset($length)){
            $length = $offset;
            $offset = 0;
        }
        $this->query_list[&#39;limit&#39;] = &#39;limit &#39;.$offset.&#39;,&#39;.$length;
        return $this;
    }
    
    public function data($data){
        /*
        if(is_object($data)){
            $data   =   get_object_vars($data);
        }elseif (is_string($data)){
            parse_str($data,$data);
        }elseif(!is_array($data)){
            //Log:DATA_TYPE_INVALID
        }
        */
        $this->query_list[&#39;data&#39;] = $data;
        return $this;
    }
    public function field($fields){
        $this->query_list[&#39;fields&#39;] = $fields;
        return $this;
    }
    public function join($join){
        $this->query_list[&#39;join&#39;] = $join;
        return $this;
    }
    public function group($group){
        $this->query_list[&#39;group&#39;] = $group;
        return $this;
    }
    public function having($having){
        $this->query_list[&#39;having&#39;] = $having;
        return $this;
    }
    /**
     +----------------------------------------------------------
     * 查询
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     * @param 
     +----------------------------------------------------------
     */
    public function select(){
        $select_sql = &#39;select &#39;;
        $fields = isset($this->query_list[&#39;fields&#39;])?$this->query_list[&#39;fields&#39;]:&#39;*&#39;;
        $select_sql.=$fields;
        $select_sql.= &#39; from `&#39;.$this->query_list[&#39;table&#39;].&#39;` &#39;;
        
        isset($this->query_list[&#39;join&#39;])?($select_sql.=$this->query_list[&#39;join&#39;]):&#39;&#39;;
        isset($this->query_list[&#39;where&#39;])?($select_sql.=&#39; where &#39;.$this->query_list[&#39;where&#39;]):&#39;&#39;;
        isset($this->query_list[&#39;group&#39;])?($select_sql.=&#39; group by&#39;.$this->query_list[&#39;group&#39;]):&#39;&#39;;
        isset($this->query_list[&#39;having&#39;])?($select_sql.=&#39; mysql having &#39;.$this->query_list[&#39;having&#39;]):&#39;&#39;;
        isset($this->query_list[&#39;order&#39;])?($select_sql.=&#39; order by &#39;.$this->query_list[&#39;order&#39;]):&#39;&#39;;
        isset($this->query_list[&#39;limit&#39;])?($select_sql.=&#39; &#39;.$this->query_list[&#39;limit&#39;]):&#39;&#39;;
        
        return $this->query($select_sql);
    }
    /**
     +----------------------------------------------------------
     * 增加
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     * @param 
     +----------------------------------------------------------
     */
    public function add(){
        $add_sql = &#39;insert into `&#39;.$this->query_list[&#39;table&#39;].&#39;` (&#39;;
        
        $data = $this->query_list[&#39;data&#39;];
        $value = $field = &#39;&#39;;
        foreach($data as $k=>$v){
            $field .= &#39;`&#39;.$k.&#39;`,&#39;;
            if(is_numeric($v))
                $value .= $v.&#39;,&#39;;
            else
                $value .= &#39;\&#39;&#39;.$v.&#39;\&#39;,&#39;;
        }
        $add_sql .= rtrim($field,&#39;,&#39;).&#39;) values (&#39;.rtrim($value,&#39;,&#39;).&#39;)&#39;;
        return $this->execute($add_sql);
    }
    /**
     +----------------------------------------------------------
     * 删除
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     * @param 
     +----------------------------------------------------------
     */
    public function delete(){
        $del_sql = &#39;delete from `&#39;.$this->query_list[&#39;table&#39;].&#39;` where &#39;.$this->query_list[&#39;where&#39;];
        
        if(isset($this->query_list[&#39;order&#39;]))
            $del_sql .= &#39;order by &#39;.$this->query_list[&#39;order&#39;];
        if(isset($this->query_list[&#39;limit&#39;]))
            $del_sql .= &#39; &#39;.$this->query_list[&#39;limit&#39;];
            
        return $this->execute($del_sql);
        
    }
    /**
     +----------------------------------------------------------
     * 更新
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     * @param 
     +----------------------------------------------------------
     */
    public function update(){
        $update_sql = &#39;update `&#39;.$this->query_list[&#39;table&#39;].&#39;` set &#39;;
        $data = $this->query_list[&#39;data&#39;];
        
        foreach($data as $k=>$v){
            if(is_numeric($v))
                $update_sql .= &#39;`&#39;.$k.&#39;` =&#39;.$v.&#39;,&#39;;
            else
                $update_sql .= &#39;`&#39;.$k.&#39;` =\&#39;&#39;.$v.&#39;\&#39;,&#39;;
        }
        $update_sql = rtrim($update_sql,&#39;,&#39;);
        if(isset($this->query_list[&#39;where&#39;]))
            $update_sql .= &#39; where &#39;.$this->query_list[&#39;where&#39;];
        if(isset($this->query_list[&#39;order&#39;]))
            $update_sql .= &#39; order by &#39;.$this->query_list[&#39;order&#39;];
        if(isset($this->query_list[&#39;limit&#39;]))
            $update_sql .= &#39; &#39;.$this->query_list[&#39;limit&#39;];
        
        return $this->execute($update_sql);
        
    }
     /**
     +----------------------------------------------------------
     * 执行查询 返回数据集
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     * @param string $sql  sql指令
     */
    public function query($sql) {
        if ( !$this->conn ) return false;
        $this->queryStr = $sql;
        //释放前次的查询结果
        if ( $this->queryID ) {    $this->free();    }
        
        $this->query_start_time = microtime(true);
        
        $this->queryID = mysql_query($sql, $this->conn);
        $this->query_count++;
        if ( false === $this->queryID ) {
            $this->error();
            return false;
        } else {
            $this->numRows = mysql_num_rows($this->queryID);
            return $this->getAll();
        }
    }
    /**
     +----------------------------------------------------------
     * 执行语句
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     * @param string $sql  sql指令
     +----------------------------------------------------------
     */
    public function execute($sql) {
        if ( !$this->conn ) return false;
        $this->queryStr = $sql;
        //释放前次的查询结果
        if ( $this->queryID ) {    $this->free();    }
        
        $this->query_start_time = microtime(true);
        
        $result =   mysql_query($sql, $this->conn) ;
        $this->query_count++;
        if ( false === $result) {
            $this->error();
            return false;
        } else {
            $this->numRows = mysql_affected_rows($this->conn);
            return $this->numRows;
        }
    }
    /**
     +----------------------------------------------------------
     * 获得所有的查询数据
     +----------------------------------------------------------
     * @access private
     +----------------------------------------------------------
     * @return array
     */
    private function getAll() {
        //返回数据集
        $result = array();
        if($this->numRows >0) {
            while($row = mysql_fetch_assoc($this->queryID)){
                $result[]   =   $row;
            }
            mysql_data_seek($this->queryID,0);
        }
        return $result;
    }
    /**
     +----------------------------------------------------------
     * 取得数据表的字段信息
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     */
    public function getFields($tableName) {
        $result =   $this->query(&#39;SHOW COLUMNS FROM `&#39;.$tableName.&#39;`&#39;);
        $info   =   array();
        if($result) {
            foreach ($result as $key => $val) {
                $info[$val[&#39;Field&#39;]] = array(
                    &#39;name&#39;    => $val[&#39;Field&#39;],
                    &#39;type&#39;    => $val[&#39;Type&#39;],
                    &#39;notnull&#39; => (bool) ($val[&#39;Null&#39;] === &#39;&#39;), // not null is empty, null is yes
                    &#39;default&#39; => $val[&#39;Default&#39;],
                    &#39;primary&#39; => (strtolower($val[&#39;Key&#39;]) == &#39;pri&#39;),
                    &#39;autoinc&#39; => (strtolower($val[&#39;Extra&#39;]) == &#39;auto_increment&#39;),
                );
            }
        }
        return $info;
    }
    /**
     +----------------------------------------------------------
     * 取得数据库的表信息
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     */
    public function getTables($dbName=&#39;&#39;) {
        if(!empty($dbName)) {
           $sql    = &#39;SHOW TABLES FROM &#39;.$dbName;
        }else{
           $sql    = &#39;SHOW TABLES &#39;;
        }
        $result =   $this->query($sql);
        $info   =   array();
        foreach ($result as $key => $val) {
            $info[$key] = current($val);
        }
        return $info;
    }
    /**
     +----------------------------------------------------------
     * 最后次操作的ID
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     * @param 
     +----------------------------------------------------------
     */
     public function last_insert_id(){
        return mysql_insert_id($this->conn);
    }
    /**
     * 执行一条带有结果集计数的
     */
    public function count($sql){
        return $this->execute($sql);
    }
    /**
     +----------------------------------------------------------
     * 启动事务
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     * @return void
     +----------------------------------------------------------
     */
    public function startTrans() {
        if ($this->transTimes == 0) {
            mysql_query(&#39;START TRANSACTION&#39;, $this->conn);
        }
        $this->transTimes++;
        return ;
    }
    /**
     +----------------------------------------------------------
     * 提交事务
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     * @return boolen
     +----------------------------------------------------------
     */
    public function commit()
    {
        if ($this->transTimes > 0) {
            $result = mysql_query(&#39;COMMIT&#39;, $this->conn);
            $this->transTimes = 0;
            if(!$result){
                throw new Exception($this->error());
            }
        }
        return true;
    }
    /**
     +----------------------------------------------------------
     * 事务回滚
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     * @return boolen
     +----------------------------------------------------------
     */
    public function rollback()
    {
        if ($this->transTimes > 0) {
            $result = mysql_query(&#39;ROLLBACK&#39;, $this->conn);
            $this->transTimes = 0;
            if(!$result){
                throw new Exception($this->error());
            }
        }
        return true;
    }
    /**
     +----------------------------------------------------------
     * 错误信息
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     * @param 
     +----------------------------------------------------------
     */
     public function error() {
        $this->error = mysql_error($this->conn);
        if(&#39;&#39; != $this->queryStr){
            $this->error .= "\n [ SQL语句 ] : ".$this->queryStr;
        }
        return $this->error;
    }
    /**
     +----------------------------------------------------------
     * 释放查询结果
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     */
    public function free() {
        @mysql_free_result($this->queryID);
        $this->queryID = 0;
        $this->query_list = null;
    }
    /**
     +----------------------------------------------------------
     * 关闭连接
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     * @param 
     +----------------------------------------------------------
     */
    function close(){
        if ($this->conn && !mysql_close($this->conn)){
            throw new Exception($this->error());
        }
        $this->conn = 0;
        $this->query_count = 0;
    }
    /**
     +----------------------------------------------------------
     * 析构方法
     +----------------------------------------------------------
     * @access public
     +----------------------------------------------------------
     */
    function __destruct(){
         $this->close();
    }
}
Statement
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

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software