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 = ''); //获取全部数据 public function fetchAll($sql = ''); //获取一行数据 public function fetchRow($sql = ''); //获取一个数据 public function fetchOne($sql = ''); //转义sql，防止注入 public function escapeString($str = ''); }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['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.[文件]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.在[代码]模型中调用
<?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( 'host' => '***', 'user' => '***', 'password' => '', 'dbname' => '***' ); //$this->_dao = MySqlDB::getInstance($config);//调用mysqldb $this->_dao = PDODB::getInstance($config);//调用pdo } }以上是php mysql数据库操作mysql和pdo的实现。更多相关内容请关注www.AdminJS.cn(www.php.cn)！
adminjs.cn是一个以CSS、JavaScript、Vue、HTML为核心的前端开发技术网站。我们致力于为广大前端开发者提供专业、全面、实用的前端开发知识和技术支持。 在本网站中，您可以学习到最新的前端开发技术，了解前端开发的最新趋势和最佳实践。我们提供丰富的教程和案例，让您可以快速掌握前端开发的核心技术和流程。 Adminjs.cn还提供一系列实用的工具和插件，帮助您更加高效地进行前端开发工作。我们提供的工具和插件都经过精心设计和优化，可以帮助您节省时间和精力，提升开发效率。 在Adminjs.cn中，您可以找到您需要的一切前端开发资源，让您成为一名更加优秀的前端开发者。欢迎您加入我们的大家庭，一起探索前端开发的无限可能！