网站首页mysql
更新一个PDO的mysql数据库操作类
发布时间：2016-05-29 01:24:23编辑：阅读（4427）
布署完PHP7后，发现以前的数据库操作类失效了，原来PHP7已经全面删除了mysql扩展支持，就是说mysql_* 系列的数据库连接函数都没了。
看来只好使用pdo来操作数据库了, 于是临时更新了一个基于pdo的操作类，
如下所示：
<?php
class DB
{
public $pdo;
protected $res;
protected $config;
function __construct(array $config)
{
$this->config = $config;
$this->connect();
}
public function connect()
{
$this->pdo = new PDO($this->config['dsn'], $this->config['name'], $this->config['password']);
$this->pdo->query('set names utf8');
$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
function __destruct()
{
$this->pdo = null;
}
public function close()
{
$this->pdo = null;
}
public function ping()
{
try {
$this->pdo->getAttribute(PDO::ATTR_SERVER_INFO);
} catch (PDOException $e) {
if (stripos($e->getMessage(), 'MySQL server has gone away') !== FALSE) {
$this->connect();
}
}
}
public function query(string $sql, array $params=[])
{
$pres = $this->pdo->prepare($sql);
foreach ($params as $key => $val) {
$pres->bindParam($key, $val);
}
if ($pres->execute()) {
$this->res = $pres;
}
}
public function exec(string $sql)
{
$res = $this->pdo->exec($sql);
if ($res) {
$this->res = $res;
}
}
public function getAll(string $sql, array $params=[])
{
$this->query($sql, $params);
return $this->res->fetchAll(PDO::FETCH_ASSOC);
}
public function getRow(string $sql, array $params=[])
{
$this->query($sql, $params);
return $this->res->fetch(PDO::FETCH_ASSOC);
}
public function getValue(string $sql, array $params=[])
{
$this->query($sql, $params);
return $this->res->fetchColumn();
}
public function getOne(string $sql, array $params=[])
{
$this->query($sql, $params);
return $this->res->fetchColumn();
}
public function insertId()
{
return $this->pdo->lastInsertId();
}
public function insert($table, $dataset, $debug = 0)
{
$this->add($table, $dataset, $debug);
}
public function add(string $table, array $dataset, int $debug = 0)
{
if (empty($table)) {
throw new Exception("表名不能为空.");
}
if (!is_array($dataset) || count($dataset) <= 0) {
throw new Exception('没有要插入的数据');
}
$value = '';
foreach ($dataset as $key=>$val){
$value .= "`{$key}`=" . (is_null($val) ? 'null' : "'" . addslashes($val) . "',");
}
$value = substr($value, 0, -1);
if ($debug === 0) {
$this->exec("insert into `{$table}` set {$value}");
if (!$this->res) {
return FALSE;
} else {
return $this->insertId();
}
} else {
echo "insert into `{$table}` set {$value}";
if ($debug === 2) {
exit;
}
}
}
public function insertAll($table, $dataset, $debug = 0)
{
if (empty($table)) {
throw new Exception("表名不能为空.");
}
if (!is_array($dataset) || count($dataset) <= 0) {
throw new Exception('没有要插入的数据');
}
$fields = array_map(function ($v) {
return "`" . addslashes($v) . "`";
}, array_keys($dataset[0]));
$sql = "INSERT INTO {$table} (" . implode(',', $fields) .") VALUES";
$values = [];
foreach ($dataset as $key => $arr) {
$newArr = array_map(function ($v) {
return is_null($v) ? 'null' : "'" . addslashes($v) . "'";
}, $arr);
array_push($values, "(" . implode(',', $newArr) . ")");
}
$sql .= implode(',', $values);
if ($debug === 0) {
$this->query($sql);
if (!$this->res) {
return FALSE;
} else {
return count($dataset);
}
} else {
echo $sql, PHP_EOL;
if ($debug === 2) {
exit;
}
}
}
public function update(string $table, array $dataset, string $conditions = "", int $debug = 0)
{
if (empty($table)) {
throw new Exception("表名不能为空.");
}
if (!is_array($dataset) || count($dataset) <= 0) {
throw new Exception('没有要更新的数据');
return false;
}
if (empty($conditions)) {
throw new Exception("删除条件为空哦.");
}
$conditions = " where " . $conditions;
$value = '';
foreach ($dataset as $key=>$val){
$value .= "`{$key}`='" . addslashes($val) . "',";
}
$value = substr($value, 0, -1);
//数据库操作
if ($debug === 0) {
$this->exec("update `{$table}` set {$value} {$conditions}");
return $this->res;
} else {
echo "update `{$table}` set {$value} {$conditions}";
if ($debug === 2) {
exit;
}
}
}
public function delete(string $table, string $conditions = "", int $debug = 0)
{
if (empty($table)) {
throw new Exception("表名不能为空.");
}
if (empty($conditions)) {
throw new Exception("删除条件为空哦.");
}
$conditions = " where " . $conditions;
//数据库操作
if ($debug === 0) {
$this->exec("delete from `{$table}` {$conditions}");
return $this->res;
} else {
echo "delete from `{$table}` {$conditions}";
if ($debug === 2) {
exit;
}
}
}
}----------------------------------------------------------------
使用方法，除了pdo的事务之外，和以前的数据库类一样：
test表字段：
CREATE TABLE IF NOT EXISTS `test` ( `id` int(8) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(32) NOT NULL, `password` varchar(32) DEFAULT NULL, `login_times` int(8) DEFAULT '0', PRIMARY KEY (`id`), KEY `username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
具体操作方法：
<?php
header('content-type:text/html;charset=utf-8');
$dbset = array(
'dsn' => 'mysql:host=localhost;dbname=test',
'name' => 'root',
'password' => '123456',
);
$db = new DB($dbset);
try{
$db->pdo->beginTransaction(); //开始事务
$rows = array(
'username' => 'test',
'password' => md5('123456'),
'login_times' => 1,
);
$user_id = $db->add("test", $rows, 'id=2');
$rows = array(
'login_times' => 2,
);
$db->update("test", $rows, 'id='.$user_id);
$db->delete("test", 'id='.$user_id);
$db->pdo->commit(); //提交事务
}catch(Exception $e){
$db->$pdo->rollBack(); //回滚
echo "Failed: " . $e->getMessage();
}------------------------------更新日志1-------------------------------
加了一个ping()方法，用于判断当前的mysql连接是否还在连接状态，如果出现了gone away,则要重新连接一下。
在执行sql查询之前，加上下面的语句：
$db->ping();
------------------------------更新日志2-------------------------------
更新查询类函数，给getAll, getRow, getValue添加参数绑定操作，防注入。
<?php
header('content-type:text/html;charset=utf-8');
$dbset = array(
'dsn' => 'mysql:host=localhost;dbname=test',
'name' => 'root',
'password' => '123456',
);
$db = new DB($dbset);
try{
$group = $db->getAll("select * from user where id>=:id AND name like :name", ['id'=>4, 'name'=>'张%']);
print_r($group);
}catch(Exception $e){
echo "Failed: " . $e->getMessage();
}------------------------------更新日志3-------------------------------
添加 insertAll批量插入函数
$dataset = [ [ 'id'=>1, 'name'=>'xiaohei', 'sex'=>'女'], [ 'id'=>2, 'name'=>'xiaobai', 'sex'=>'男'], ]; $db->insertAll($dataset);
评论