pdo mysql 封装_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工具类

/**

* 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 { }

Composer安装

框架中使用建议

在框架中使用DB类,用单例模式或者用依赖容器来管理较好。