php pdo mysql封装
最近在搞一些mysql脚本工作，为了方便工作，对PDO稍微封装下：
一：随便起个名字：Db.php
<?php
/**
* pdo mysql封装
*
* @author jacken 2421102982
* @version 1.0 版本号
*/
namespace Great\lib;
use \PDO;
use \PDOStatement;
use \PDOException;
// 如果不用命令空间的话把以上4行代码去掉
class Db {
/**
* 单例，PDO对象
*
* @var object
*/
private static $getInstance = null;
/**
* 查询构造
*
* @var string
*/
private $p = array('dbname','field'=>'*','leftJoin'=>[],'rightJoin'=>[],'join'=>[],'order','limit','condition','group');
/**
* 获取sql语句
*
* @var string
*/
public $see = false; //是否捕获sql语句
/**
* PDO对象,内/外部均可用
*
* @var pdo object
*/
public $pdo; //访问数据库的PDO对象，可以外部调用
/**
* 构造函数，连接Mysql数据库
*
* @access private
* @param array $list 连接配置参数
*/
private function __construct($list){
$this->pdoConnect($this->parameter($list));
}
/**
* 静态方法，类实例化连接调用
*
* @access public
* @param array $list 连接配置参数
* return pdo object
*/
static public function connect($list) {
if (!self::$getInstance instanceof self) {
self::$getInstance = new self ($list);
}
return self::$getInstance;
}
/**
* 配置参数
*
* @access private
* @param array ['host'] 连接地址
* @param array ['user'] 数据库用户名
* @param array ['password'] 数据库密码
* @param array ['dbname'] 连接数据库名称
* @param array ['charset'] 字符集,默认：utf8,可选
* @param array ['port'] 端口号,默认:3306,可选
*
*/
private function parameter($list=array())
{
return array(
'server' => $list['host'],
'username' => $list['user'],
'password' => $list['password'],
'dbname' => $list['dbname'],
'charset' => isset($list['charset']) ? $list['charset'] : 'utf8',
'port'=>isset($list['port']) ? $list['port'] : '3306'
);
}
/**
* 禁止克隆
*/
private function __clone(){
}
/**
* 连接PDO
*
* @access private
* @param array $o 配置参数
*/
private function pdoConnect($o = null){
try{
$dsn = 'mysql:host='.$o['server'].';dbname='.$o['dbname'];
$this->pdo = new \pdo($dsn,$o['username'],$o['password'],array(PDO::ATTR_AUTOCOMMIT=>1)); //自动提交
$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //开启异常处理
$this->pdo->exec('set names utf8');
}catch(PDOException $e){
die('Connection failed: '. $e->getMessage());
}
}
/**
* 拼接查询sql语句
*
* @access private
* @param string $sql 查询参数:字段,条件,group,order
* @return string 返回待执行的sql语句
*/
private function splic($sql=''){
if(empty($sql)){
$sql = 'SELECT '.$this->p['field'].' FROM '.$this->p['dbname'];
if(count($this->p['leftJoin'])>0){
$left = '';
foreach($this->p['leftJoin'] as $key =>$val){
$left .= $val;
}
$sql .= $left;
}
if(count($this->p['rightJoin'])>0){
$right = '';
foreach($this->p['rightJoin'] as $key =>$val){
$right .= $val;
}
$sql .= $right;
}
if(count($this->p['join'])>0){
$joins = '';
foreach($this->p['join'] as $key =>$val){
$joins .= $val;
}
$sql .= $joins;
}
if(isset($this->p['condition'])){
$sql .= ' '.$this->p['condition'];
}
if(isset($this->p['group'])){
$sql .= $this->p['group'];
}
if(isset($this->p['order'])){
$sql .= $this->p['order'];
}
if(isset($this->p['limit'])){
$sql .= $this->p['limit'];
}
}
$this->p = array('dbname','field'=>'*','leftJoin'=>[],'rightJoin'=>[],'join'=>[],'order','limit','condition','group');
return $sql;
}
/**
* 构建写入数据格式
*
* @access private
* @param array $list 字段、值的键值对
* @return array
*/
private function convert($list){
foreach($list as $key =>$val){
$field[] = '`'.$key.'`';
$seat[] = ':'.trim($key);
}
return array('seat'=>$seat,'field'=>$field);
}
/**
* PDO内置函数:prepare
*
* @access public
* @param return object(PDOStatement)
*/
public function prepare($sql){
return $this->pdo->prepare($sql);
}
/**
* 表名
*
* @access public
* @param string $dbName 表名
* @return pdo object
*/
public function dbname($dbName){
$this->p['dbname'] = $dbName;
return $this;
}
/**
* 构造字段
*
* @access public
* @param string $field 字段
* @return pdo object
*/
public function field($field='*'){
$this->p['field'] = $field;
return $this;
}
/**
* 左连接
*
* @access public
* @param string $tableName 表名
* @param string $where 关联（a.id = b.pid）
* @return pdo object
*/
public function leftJoin($tableName,$where){
$whereList = explode("=",$where);
$leftWhere = trim($whereList['0']).' = '.trim($whereList['1']);
array_push($this->p['leftJoin'],' LEFT JOIN '.$tableName.' ON '.$leftWhere);
return $this;
}
/**
* 右连接
*
* @access public
* @param string $tableName 表名
* @param string $where 关联（a.id = b.pid）
* @return pdo object
*/
public function rightJoin($tableName,$where){
$whereList = explode("=",$where);
$leftWhere = trim($whereList['0']).' = '.trim($whereList['1']);
array_push($this->p['rightJoin'],' RIGHT JOIN '.$tableName.' ON '.$leftWhere);
return $this;
}
/**
* 内连接
*
* @access public
* @param string $tableName 表名
* @param string $where 关联（a.id = b.pid）
* @return pdo object
*/
public function join($tableName,$where){
$whereList = explode("=",$where);
$leftWhere = trim($whereList['0']).' = '.trim($whereList['1']);
array_push($this->p['join'],' INNER JOIN '.$tableName.' ON '.$leftWhere);
return $this;
}
/**
* 构造排序
*
* @access public
* @param string $field 字段
* @param string $sort 排序,默认:ASC 可选
* @return pdo object
*/
public function order($field,$sort='ASC'){
$this->p['order'] = ' ORDER BY '.$field.' '.$sort;
return $this;
}
/**
* 构造查询范围
*
* @access public
* @param string $start 开始值
* @param string $end 结束值
* @return pdo object
*/
public function limit($start,$end=''){
if($end){
$this->p['limit'] = " limit $start,$end";
}else{
$this->p['limit'] = " limit $start";
}
return $this;
}
/**
* 构造查询条件
*
* @access public
* @param string $condition 查询条件
* @return pdo object
*/
public function condition($condition){
$this->p['condition'] = ' WHERE '.$condition;
return $this;
}
/**
* 构造查询group
*
* @access public
* @param string $field group字段
* @return pdo object
*/
public function group($field){
$this->p['group'] = ' GROUP BY '.$field;
return $this;
}
/**
* 获取一条记录,可写原生SQL,也可和构造查询组合使用
*
* @access public
* @param string $sql 原生sql,可选(和构造查询组合使用时，不需要该值)
* @return array 返回查询一维数组
*/
public function fetch($sql=''){
$sql = $this->splic($sql);
if($this->see == true){
return $sql;
}
$res = $this->prepare($sql);
$res->execute();
return $res->fetch(PDO::FETCH_ASSOC);
}
/**
* 获取多条记录,可写原生SQL,也可和构造查询组合使用
*
* @access public
* @param string $sql 原生sql,可选(和构造查询组合使用时，不需要该值)
* @return array 返回查询多维数组
*/
public function fetchAll($sql=''){
$sql = $this->splic($sql);
if($this->see == true){
return $sql;
}
$res = $this->prepare($sql);
$res->execute();
return $res->fetchAll(PDO::FETCH_ASSOC);
}
/**
* 获取行数,可写原生SQL,也可和构造查询组合使用
*
* @access public
* @param string $sql 原生sql,可选(和构造查询组合使用时，不需要该值)
* @return int
*/
public function count($sql=''){
$list = explode(' ',$this->splic($sql));
$list['1'] = 'count(*)';
$rs = $this->query(implode(' ',$list));
$rows = $rs->fetch();
return $rows['0'];
}
/**
* 获取某个指定字段的值
*
* @access public
* @param string $fieldName 要获取的字段名
* @return string
*/
public function getField($fieldName,$sql=''){
$fetch = $this->fetch($sql);
return $fetch[$fieldName];
}
/**
* 更新
*
* @access public
* @param string $dbName 表名
* @param array $list 字段、值的键值对
* @param string $where 条件
* @param return bool 成功:true,失败:false
*/
public function update($dbName,$list,$where){
$str = '';
foreach($list as $key =>$val){
$seat[':'.$key] = $val;
$str .= $key.'='.':'.$key.',';
}
$str = rtrim($str,',');
$sql = "UPDATE `$dbName` SET $str WHERE $where";
if($this->see == true){
return $sql;
}
return $this->prepare($sql)->execute($list); //成功返回 true
}
/**
* 插入、添加
*
* @access public
* @param string $dbName 表名
* @param array $list 字段、值的键值对
* @param return bool 成功:true,失败:false
*/
public function creat($dbName,$list=array()){
$arr = $this->convert($list);
$sql = "INSERT INTO `$dbName` (". implode(',',$arr['field']).') VALUES ('. implode(',',$arr['seat']).')';
// file_put_contents('/tmp/kkk.txt',$sql."\r\n",FILE_APPEND);
if($this->see == true){
return $sql;
}
return $this->prepare($sql)->execute($list); //成功返回 true
}
/**
* 返回最后插入的ID
*
* @access public
* @param return mixed
*/
public function lastInsertId(){
return $this->pdo->lastInsertId();
}
/**
* 删除
* @param string $tableName 表名
* @param string $where 条件
* @access public
* @return int 返回影响的数目
*/
public function del($tableName,$where){
$sql = 'DELETE FROM `'.$tableName.'` WHERE '.$where;
if($this->see == true){
return $sql;
}
return $this->exec($sql);
}
/**
* 执行一条sql,pdo原生方法
*
* @access public
*/
public function query($sql){
return $this->pdo->query($sql);
}
/**
* 执行一条sql,pdo原生方法
*
* @access public
*/
public function exec($sql){
return $this->pdo->exec($sql);
}
//开启事务
public function beginTransaction(){
$this->pdo->beginTransaction();
}
//提交
public function commit(){
$this->pdo->commit();
}
//回滚
public function back(){
$this->pdo->rollBack();
}
}
二：demo：
1：链接：
<?php
require('Db.php');
$db = Db::connect(['host'=>'127.0.0.1','user'=>'root','password'=>123,'dbname'=>'t1']);2：查询：$data = $db->dbname('goods')->order('id','DESC')->condition('id = 2')->fetch(); //构造获取一条
$data = $db->dbname('goods')->order('id','DESC')->fetchAll(); //构造获取多条
$data = $db->dbname('a')->leftJoin('b','a.id=b.series_id')->condition('a.id = 5')->fetch(); //左连接，可写多个
$data = $db->dbname('a')->rightJoin('b','a.id=b.series_id')->condition('a.id = 5')->fetch(); //右连接
$data = $db->dbname('a')->join('b','a.id=b.series_id')->condition('a.id = 5')->fetch(); //内连接
或者直接写SQL：$data = $db->fetch("SELECT * FROM goods WHERE id = 2 ORDER BY id DESC"); //获取一条获取sql语句用：$db->see = true;
$db->see = true;
$data = $db->dbname('a')->join('b','a.id=b.series_id')->condition('a.id = 5')->fetch();
echo $data; //上面设置了see，那么这里就返回sql语句 获取某个字段值：
$name = $db->dbname('goods')->condition('id=2')->getField('name'); //构造获取goods表,id等于2的name字段
$name = $db->getField('name',"SELECT * FROM goods WHERE id=2"); //原生获取name值获取总数：
$count = $db->dbname('goods')->count(); //构造获取总数
$count = $db->count("SELECT * FROM goods"); //原生获取总数3：添加：
$ = $db->creat('goods',['name'=>'李小龙','age'=>123]); //添加数据
echo $db->lastInsertId(); //获取新增ID4：修改$db->update('goods',['name'=>'李小龙456','age'=>123],"id = 3");5：删除
$db->del('goods','id=30');6：事务
try {
$db->beginTransaction(); //开启事物
$db->del('goods','id=1');
$db->del('goods2','id=1'); // 表不存在,报错
$db->commit(); //提交事务
} catch (Exception $e) {
$db->back(); //回滚
echo "Failed: " . $e->getMessage();
}7：如果以上感觉用起来别扭的话，也可以用原生PDO
$db->pdo->PDO原生方法
