php mysql数据库操作mysql和pdo的实现
- PHP中文网Original
- 2016-05-23 08:39:301307Durchsuche
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. [代码]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(
'host' => '***',
'user' => '***',
'password' => '',
'dbname' => '***'
);
//$this->_dao = MySqlDB::getInstance($config);//调用mysqldb
$this->_dao = PDODB::getInstance($config);//调用pdo
}
}
以上就是php mysql数据库操作mysql和pdo的实现的内容，更多相关内容请关注PHP中文网（www.php.cn）！
Stellungnahme：
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel：php制作验证码图片Nächster Artikel：图片类 支持GD和Imagick,gif动画类型