前言
pdo扩展为php访问数据库定义了一个轻量级的、一致性的接口，它提供了一个数据访问抽象层，这样，无论使用什么数据库，都可以通过一致的函数执行查询和获取数据。pdo随php5.1发行，在php5.0的pecl扩展中也可以使用。
我个人理解：pdo是一个抽象类，为我们提供访问数据的接口方法，下面这篇将给大家介绍关于php如何利用pdo封装简单易用的db类，下面话不多说，来一起看看详细的介绍：
使用
创建测试库和表
create database db_test;
create table `user` (
`id` int(10) unsigned not null auto_increment,
`name` char(11) not null,
`created_at` int(10) unsigned not null,
primary key (`uid`)
) engine=innodb default charset=utf8;
insert into `user` values ('1', 'wang', '1501109027');
insert into `user` values ('2', 'meng', '1501109026');
insert into `user` values ('3', 'liu', '1501009027');
insert into `user` values ('4', 'yuan', '1500109027');
代码测试
require __dir__ . '/db.php';
$db = new db();
$db->__setup([
'dsn'=>'mysql:dbname=db_test;host=localhost',
'username'=>'root',
'password'=>'******',
'charset'=>'utf8'
]);
$user = $db->fetch('select * from user where id = :id', ['id' => 1]);
echo $user['name'];
echo "n";
$insertid = $db->insert('user', ['name' => 'salamander', 'created_at' => time()]);
echo "insert user {$insertid}n";
$users = $db->fetchall('select * from user');
foreach ($users as $item) {
echo "user {$item['id']} is {$item['name']} n";
}
运行结果
db工具类
<?php
/**
* user: salamander
* date: 2016/9/2
* time: 9:16
*/
class db
{
private $dsn;
private $sth;
private $dbh;
private $user;
private $charset;
private $password;
public $lastsql = '';
public function __setup($config = array())
{
$this->dsn = $config['dsn'];
$this->user = $config['username'];
$this->password = $config['password'];
$this->charset = $config['charset'];
$this->connect();
}
private function connect()
{
if(!$this->dbh){
$options = array(
pdo::mysql_attr_init_command => 'set names ' . $this->charset,
);
$this->dbh = new pdo($this->dsn, $this->user,
$this->password, $options);
}
}
public function begintransaction()
{
return $this->dbh->begintransaction();
}
public function intransaction()
{
return $this->dbh->intransaction();
}
public function rollback()
{
return $this->dbh->rollback();
}
public function commit()
{
return $this->dbh->commit();
}
function watchexception($execute_state)
{
if(!$execute_state){
throw new mysqlexception("sql: {$this->lastsql}n".$this->sth->errorinfo()[2], intval($this->sth->errorcode()));
}
}
public function fetchall($sql, $parameters=[])
{
$result = [];
$this->lastsql = $sql;
$this->sth = $this->dbh->prepare($sql);
$this->watchexception($this->sth->execute($parameters));
while($result[] = $this->sth->fetch(pdo::fetch_assoc)){ }
array_pop($result);
return $result;
}
public function fetchcolumnall($sql, $parameters=[], $position=0)
{
$result = [];
$this->lastsql = $sql;
$this->sth = $this->dbh->prepare($sql);
$this->watchexception($this->sth->execute($parameters));
while($result[] = $this->sth->fetch(pdo::fetch_column, $position)){ }
array_pop($result);
return $result;
}
public function exists($sql, $parameters=[])
{
$this->lastsql = $sql;
$data = $this->fetch($sql, $parameters);
return !empty($data);
}
public function query($sql, $parameters=[])
{
$this->lastsql = $sql;
$this->sth = $this->dbh->prepare($sql);
$this->watchexception($this->sth->execute($parameters));
return $this->sth->rowcount();
}
public function fetch($sql, $parameters=[], $type=pdo::fetch_assoc)
{
$this->lastsql = $sql;
$this->sth = $this->dbh->prepare($sql);
$this->watchexception($this->sth->execute($parameters));
return $this->sth->fetch($type);
}
public function fetchcolumn($sql, $parameters=[], $position=0)
{
$this->lastsql = $sql;
$this->sth = $this->dbh->prepare($sql);
$this->watchexception($this->sth->execute($parameters));
return $this->sth->fetch(pdo::fetch_column, $position);
}
public function update($table, $parameters=[], $condition=[])
{
$table = $this->format_table_name($table);
$sql = "update $table set ";
$fields = [];
$pdo_parameters = [];
foreach ( $parameters as $field=>$value){
$fields[] = '`'.$field.'`=:field_'.$field;
$pdo_parameters['field_'.$field] = $value;
}
$sql .= implode(',', $fields);
$fields = [];
$where = '';
if(is_string($condition)) {
$where = $condition;
} else if(is_array($condition)) {
foreach($condition as $field=>$value){
$parameters[$field] = $value;
$fields[] = '`'.$field.'`=:condition_'.$field;
$pdo_parameters['condition_'.$field] = $value;
}
$where = implode(' and ', $fields);
}
if(!empty($where)) {
$sql .= ' where '.$where;
}
return $this->query($sql, $pdo_parameters);
}
public function insert($table, $parameters=[])
{
$table = $this->format_table_name($table);
$sql = "insert into $table";
$fields = [];
$placeholder = [];
foreach ( $parameters as $field=>$value){
$placeholder[] = ':'.$field;
$fields[] = '`'.$field.'`';
}
$sql .= '('.implode(",", $fields).') values ('.implode(",", $placeholder).')';
$this->lastsql = $sql;
$this->sth = $this->dbh->prepare($sql);
$this->watchexception($this->sth->execute($parameters));
$id = $this->dbh->lastinsertid();
if(empty($id)) {
return $this->sth->rowcount();
} else {
return $id;
}
}
public function errorinfo()
{
return $this->sth->errorinfo();
}
protected function format_table_name($table)
{
$parts = explode(".", $table, 2);
if(count($parts) > 1) {
$table = $parts[0].".`{$parts[1]}`";
} else {
$table = "`$table`";
}
return $table;
}
function errorcode()
{
return $this->sth->errorcode();
}
}
class mysqlexception extends exception { }
框架中使用建议
在框架中使用db类,用单例模式或者用依赖容器来管理较好。
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对硕编程的支持
【说明】：本文章由站长整理发布，文章内容不代表本站观点，如文中有侵权行为，请与本站客服联系(QQ:254677821)！