道者编程

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(); //获取新增ID
4:修改

$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原生方法


最新评论:
1楼 广东省深圳市 电信 发表于 2022-04-29 16:18:22
松哥还是强
共有 1 条记录  首页 上一页 下一页 尾页 1
我要评论:

看不清楚