登录  /  注册
首页 > php教程 > PHP源码 > 正文

php mysql数据库操作mysql和pdo的实现

PHP中文网
发布: 2016-05-23 08:39:30
原创
1143人浏览过

php mysql数据库操作mysql和pdo的实现

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

<?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 = &#39;&#39;);
    //获取全部数据
    public function fetchAll($sql = &#39;&#39;);
    //获取一行数据
    public function fetchRow($sql = &#39;&#39;);
    //获取一个数据
    public function fetchOne($sql = &#39;&#39;);
    //转义sql,防止注入
    public function escapeString($str = &#39;&#39;);
 
}
登录后复制

2. [文件] 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[&#39;host&#39;]) ? $config[&#39;host&#39;] : &#39;localhost&#39;;
        $this->_port = isset($config[&#39;port&#39;]) ? $config[&#39;port&#39;] : &#39;3306&#39;;
        $this->_user = isset($config[&#39;user&#39;]) ? $config[&#39;user&#39;] : &#39;&#39;;
        $this->_password = $config[&#39;password&#39;];
        $this->_charset = isset($config[&#39;charset&#39;]) ? $config[&#39;charset&#39;] : &#39;UTF8&#39;;
        $this->_dbname = isset($config[&#39;dbname&#39;]) ? $config[&#39;dbname&#39;] : &#39;test&#39;;
    }
 
    private function _connectServer()
    {
        $connect_result = @mysql_connect("$this->_host:$this->_port", $this->_user, $this->_password);
        if ($connect_result) {
            $this->_link = $connect_result;
        } else {
            echo &#39;数据库连接失败,请确认服务器信息&#39;;
            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 &#39;han%&#39;
     * @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 = &#39;&#39;)
    {
        return "&#39;" . mysql_real_escape_string($str, $this->_link) . "&#39;";
    }
 
}
登录后复制

3. [文件] 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[&#39;host&#39;]) ? $config[&#39;host&#39;] : &#39;localhost&#39;;
        $this->_port = isset($config[&#39;port&#39;]) ? $config[&#39;port&#39;] : &#39;3306&#39;;
        $this->_user = isset($config[&#39;user&#39;]) ? $config[&#39;user&#39;] : &#39;&#39;;
        $this->_password = $config[&#39;password&#39;];
        $this->_charset = isset($config[&#39;charset&#39;]) ? $config[&#39;charset&#39;] : &#39;UTF8&#39;;
        $this->_dbname = isset($config[&#39;dbname&#39;]) ? $config[&#39;dbname&#39;] : &#39;test&#39;;
    }
 
    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 = &#39;&#39;)
    {
        //使用正则过滤,分别使用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 = &#39;&#39;)
    {
        $result = $this->query($sql);
        $rows = $result->fetchAll(PDO::FETCH_ASSOC);
        $result->closeCursor();
        return $rows;
    }
 
    public function fetchRow($sql = &#39;&#39;)
    {
        $result = $this->query($sql);
        $row = $result->fetch(PDO::FETCH_ASSOC);
        $result->closeCursor();
        return $row;
    }
 
    public function fetchOne($sql = &#39;&#39;)
    {
        $result = $this->query($sql);
        $string = $result->fetchColumn();
        $result->closeCursor();
        return $string;
    }
 
    public function escapeString($str = &#39;&#39;)
    {
        return $this->_pdo->quote($str);
    }
}
登录后复制

4. [代码]model中调用

<?php
/**
 * Created by PhpStorm.
 * User: jiangbo
 * Date: 2016/1/19
 * Time: 1:02
 * 基础模型类
 */
 
 
class Model{
    /**
     * DAO : data access object
     */
    protected $_dao;//存储实例化好的数据库对象
 
    /**
     * Model constructor.
     */
    public function __construct()
    {
        $this->_initDAO();//初始化基础模型
    }
 
    protected function _initDAO(){
        
        $config = array(
            &#39;host&#39; => &#39;***&#39;,
            &#39;user&#39; => &#39;***&#39;,
            &#39;password&#39; => &#39;&#39;,
            &#39;dbname&#39; => &#39;***&#39;
        );
        //$this->_dao = MySqlDB::getInstance($config);//调用mysqldb
        $this->_dao = PDODB::getInstance($config);//调用pdo
    }
 
}
登录后复制

 

 以上就是php mysql数据库操作mysql和pdo的实现的内容,更多相关内容请关注PHP中文网(www.php.cn)! 

智能AI问答
PHP中文网智能助手能迅速回答你的编程问题,提供实时的代码和解决方案,帮助你解决各种难题。不仅如此,它还能提供编程资源和学习指导,帮助你快速提升编程技能。无论你是初学者还是专业人士,AI智能助手都能成为你的可靠助手,助力你在编程领域取得更大的成就。
来源:php中文网
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
作者最新文章
最新问题
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板
关于我们 免责申明 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号 技术交流群
PHP中文网订阅号
每天精选资源文章推送
PHP中文网APP
随时随地碎片化学习
PHP中文网抖音号
发现有趣的

Copyright 2014-2024 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号