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的实现的内容。