php mysql資料庫操作mysql和pdo的實作

最近在项目中用到了pdo，之前一直用的mysql类，查了查手册，发现功能大同小异，于是我用接口封装了一个pdo类，实现了与mysql 的相同实现。

大概用到了：单例模型，接口，静态方法，mvc

第一次写博客，直接上代码吧：

1.公共接口：i-DAO.interface.php

<?php
/**
 * Created by PhpStorm.
 * User: jiangbo
 * Date: 2016/1/24
 * Time: 1:05
 * 与mysql接口一致(模型层调用一致)，利用interface
 */
interface i_DAO{
    //获取与前DAO的接口
    public static function getInstance($config = array());
    //执行sql的方法
    public function query($sql = '');
    //获取全部数据
    public function fetchAll($sql = '');
    //获取一行数据
    public function fetchRow($sql = '');
    //获取一个数据
    public function fetchOne($sql = '');
    //转义sql，防止注入
    public function escapeString($str = '');

}      

2.mysql类：MySqlDB.class.php

<?php

/**
 * Created by PhpStorm.
 * User: jiangbo
 * Date: 2016/1/19
 * Time: 17:27
 * 单例化的mysql类：3私1公
 */
class MySqlDB implements i_DAO
{
    private $_host;
    private $_port;
    private $_user;
    private $_password;
    private $_charset;
    private $_dbname;
    private $_link;

    /**
     * MySqlDB constructor.
     * @param array $config
     */
    private function __construct($config = array())
    {
        $this->_initServer($config);//初始化服务器信息
        $this->_connectServer();//链接服务器
        $this->_setCharset();//设置字符集编码
        $this->_selectDB();//选择默认数据库
    }

    private function __clone()
    {
        echo "不能克隆该对象", "<br>";
        die();
    }

    private static $_instance;

    public static function getInstance($config = array())
    {
        if (!(static::$_instance instanceof static)) {
            static::$_instance = new static($config);
        }
        return static::$_instance;
    }

    private function _initServer($config)
    {
        $this->_host = isset($config['host']) ? $config['host'] : 'localhost';
        $this->_port = isset($config['port']) ? $config['port'] : '3306';
        $this->_user = isset($config['user']) ? $config['user'] : '';
        $this->_password = $config['password'];
        $this->_charset = isset($config['charset']) ? $config['charset'] : 'UTF8';
        $this->_dbname = isset($config['dbname']) ? $config['dbname'] : 'test';
    }

    private function _connectServer()
    {
        $connect_result = @mysql_connect("$this->_host:$this->_port", $this->_user, $this->_password);
        if ($connect_result) {
            $this->_link = $connect_result;
        } else {
            echo '数据库连接失败，请确认服务器信息';
            die();
        }
    }

    private function _setCharset()
    {
        $sql = "SET NAMES $this->_charset";
        $this->query($sql);
    }

    private function _selectDB()
    {
        $sql = "USE `$this->_dbname`";
        $this->query($sql);
    }

    /**
     * 执行SQL语句
     * @param string $sql
     * @return mixed 执行结果。查询类的SQL(select, show, desc),成功返回结果集资源，失败返回false。非查询类(insert, delete, update)，成功返回true，失败返回false.
     */
    public function query($sql)
    {
        $query_result = @mysql_query($sql, $this->_link);
        if (false == $query_result) {
            echo "SQL执行失败:", "<br>";
            echo "错误的SQL:", "<br>", $sql, "<br>";
            echo "错误的消息为:", "<br>", mysql_errno($this->_link), "<br>";
            die();
        } else {
            return $query_result;
        }
    }

    /**
     * @param string $sql 通常为:select * from ...
     * @return array
     */
    public function fetchRow($sql)
    {
        $result = $this->query($sql);
        $row = @mysql_fetch_assoc($result);
        @mysql_free_result($result);
        return $row;
    }

    /**
     * @param string $sql 通常为:select count(*) from ...
     * @return string 如果没有值就返回NULL
     */
    public function fetchOne($sql)
    {
        $result = $this->query($sql);
        $row = @mysql_fetch_row($result);
        @mysql_free_result($result);
        if ($row)
            return $row[0];
        else
            return NULL;
    }

    /**
     * @param string $sql 通常为:select * from ... where ..like 'han%'
     * @return array
     */
    public function fetchAll($sql)
    {
        $result = $this->query($sql);
        $rows = array();
        while ($row = @mysql_fetch_assoc($result))
            $rows[] = $row;
        @mysql_free_result($result);
        return $rows;
    }

    /*
     * 关闭当前数据库连接, 一般无需使用. 连接会随php脚本结束自动关闭
     */
    /*public function close()
    {
        return @mysql_close($this->_link);
    }*/

    /**
     * 防止sql注入：转义字符串，在模型中使用
     * @param string $str 带转义的字符串
     * @return string 带引号包裹的转义后的字符串
     */
    public function escapeString($str = '')
    {
        return "'" . mysql_real_escape_string($str, $this->_link) . "'";
    }

}      

3.pdo类： PDODB.class.php

<?php

/**
 * Created by PhpStorm.
 * User: jiangbo
 * Date: 2016/1/24
 * Time: 1:00
 * dao层使用dao扩展封装实现
 */
class PDODB implements i_DAO
{
    private $_host;
    private $_port;
    private $_user;
    private $_password;
    private $_charset;
    private $_dbname;

    private $_dsn;
    private $_option;
    private $_pdo;


    /**
     * PDODB constructor.
     * @param array $config
     */
    private function __construct($config = array())
    {
        $this->_initServer($config);
        $this->_newPDO();
    }

    private function _initServer($config)
    {
        $this->_host = isset($config['host']) ? $config['host'] : 'localhost';
        $this->_port = isset($config['port']) ? $config['port'] : '3306';
        $this->_user = isset($config['user']) ? $config['user'] : '';
        $this->_password = $config['password'];
        $this->_charset = isset($config['charset']) ? $config['charset'] : 'UTF8';
        $this->_dbname = isset($config['dbname']) ? $config['dbname'] : 'test';
    }

    private function _newPDO()
    {
        //设置参数
        $this->_setDSN();//设置数据源参数
        $this->_setOption();//设置选项
        $this->_getPDO();//得到PDO对象
    }

    private function _setDSN()
    {
        $this->_dsn = "mysql:host=$this->_host;port=$this->_port;dbname=$this->_dbname";
    }

    private function _setOption()
    {
        $this->_option = array(
            PDO::MYSQL_ATTR_INIT_COMMAND => "set names $this->_charset"
        );
    }

    private function _getPDO()
    {
        $this->_pdo = new PDO($this->_dsn, $this->_user, $this->_password, $this->_option);

    }

    private function __clone()
    {
        echo "不能克隆该对象", "<br>";
        die();
    }

    private static $_instance;

    public static function getInstance($config = array())
    {
        if (!(static::$_instance instanceof static)) {
            static::$_instance = new static($config);
        }
        return static::$_instance;
    }
    //执行方法，适用的场景
    private static $_queryStr = array(
        "select",
        "show",
        "desc"
    );
    public function query($sql = '')
    {
        //使用正则过滤，分别使用query和exec

        foreach (static::$_queryStr as $str){

            if (preg_match("/^\s*".$str.".*?/i",$sql)){
                //查询类 返回结果集对象
                $result = $this->_pdo->query($sql);
            }else{
                //非查询类 返回bool
                $result = $this->_pdo->exec($sql) !== false;//有可能是0
            }
            //如果执行失败，报错
            if($result === false){
                $error_info = $this->errorInfo();
                echo "SQL执行失败:", "<br>";
                echo "错误的SQL:", "<br>", $sql, "<br>";
                echo "错误的消息为:", "<br>", $error_info[2], "<br>";
                die();
            }else{
                return $result;
            }
            break;
        }
    }

    public function fetchAll($sql = '')
    {
        $result = $this->query($sql);
        $rows = $result->fetchAll(PDO::FETCH_ASSOC);
        $result->closeCursor();
        return $rows;
    }

    public function fetchRow($sql = '')
    {
        $result = $this->query($sql);
        $row = $result->fetch(PDO::FETCH_ASSOC);
        $result->closeCursor();
        return $row;
    }

    public function fetchOne($sql = '')
    {
        $result = $this->query($sql);
        $string = $result->fetchColumn();
        $result->closeCursor();
        return $string;
    }

    public function escapeString($str = '')
    {
        return $this->_pdo->quote($str);
    }
}      

4.在基类modle中调用：Modle.class.php

class Model{
    /**
     * DAO : data access object
     */
    protected $_dao;//存储实例化好的数据库对象

    /**
     * Model constructor.
     */
    public function __construct()
    {
        $this->_initDAO();//初始化基础模型
    }

    protected function _initDAO(){
      
        $config = array(
            'host' => '***',
            'user' => '***',
            'password' => '***',
            'dbname' => '***'
        );
        //$this->_dao = MySqlDB::getInstance($config);//调用mysqldb
        $this->_dao = PDODB::getInstance($config);//调用pdo
    }

}      

第一次写博客，码代码不容易，转载请标明出处，谢谢大家。