搜索

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();
    }
}
声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序,用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

Video Face Swap

Video Face Swap

使用我们完全免费的人工智能换脸工具轻松在任何视频中换脸!

热工具

SublimeText3 Mac版

SublimeText3 Mac版

神级代码编辑软件(SublimeText3)

适用于 Eclipse 的 SAP NetWeaver 服务器适配器

适用于 Eclipse 的 SAP NetWeaver 服务器适配器

将Eclipse与SAP NetWeaver应用服务器集成。

Atom编辑器mac版下载

Atom编辑器mac版下载

最流行的的开源编辑器

SecLists

SecLists

SecLists是最终安全测试人员的伙伴。它是一个包含各种类型列表的集合,这些列表在安全评估过程中经常使用,都在一个地方。SecLists通过方便地提供安全测试人员可能需要的所有列表,帮助提高安全测试的效率和生产力。列表类型包括用户名、密码、URL、模糊测试有效载荷、敏感数据模式、Web shell等等。测试人员只需将此存储库拉到新的测试机上,他就可以访问到所需的每种类型的列表。

SublimeText3 Linux新版

SublimeText3 Linux新版

SublimeText3 Linux最新版