PHP-PDO Class

php-pdo-helper.php

<?php
/**
 * 轻量级pdo操作封装函数
 * @author xixing.lu@outlook.com
 * @version 2013-04-16
 * @todo 根据实际情况继续优化或者添加功能
 */
namespace dataservice\helper;

class PDOHelper
{

    private $tb; // 当前操作的表
    private $pdo; // pdo连接
    private $sql; // PDOStatement对象
    private $isOutSql; // 是否输出sql 日志
    public function __construct($table, $isOutSql = false)
    {
        $this->tb = $table;
        $this->isOutSql = $isOutSql;
    }

    public function setPdo(&$pdo)
    {
        $this->pdo = $pdo;
    }
    // 格式化输入参数：针对单条记录插入
    private function setParamas($arr)
    {
        $cols = array_keys($arr);
        $new_cols = '`' . implode('`,`', $cols) . '`';
        $new_values = ':' . implode(', :', $cols);
        return array(
            'cols' => $new_cols,
            'values' => $new_values
        );
    }
    // 格式化输入参数：针对多条记录插入
    private function setMultiParamas($arr, $cols)
    {
        $new_cols = '(`' . implode('`,`', $cols) . '`)';
        $new_values = array();
        $new_paramas = array();
        foreach ($arr as $k => $v) {
            $new_values[] = '(:' . implode($k . ', :', $cols) . $k . ')';
            foreach ($cols as $v1) {
                $new_paramas[$v1 . $k] = $v[$v1];
            }
        }
        return array(
            'cols' => $new_cols,
            'values' => $new_values,
            'paramas' => $new_paramas
        );
    }
    // update语句格式化设定
    private function setUpdate($cols, $op = '=', $suffix = '')
    {
        $func = function ($v) use($op, $suffix)
        {
            return "`$v`" . $op . ':' . $v . $suffix;
        };
        return implode(', ', array_map($func, $cols));
    }
    // select 查询字段格式化
    private function setCols($cols)
    {
        if ($cols == '*') {
            return $cols;
        }
        if (strpos($cols, ',') !== false) {
            $cols = '`' . implode('`, `', explode(',', $cols)) . '`';
        } else {
            $cols = '`' . $cols . '`';
        }
        return $cols;
    }
    // 执行sql,并且记录日志
    private function execSql($sql, $paramas = array())
    {
        if ($this->isOutSql) {
            $str = "sql:\n\ttime:" . date('Y-m-d H:i:s') . "\n\tcontent:$sql\n";
            error_log($str, 3, IPRO_ROOT . '/appdata/log/sql-' . date('Y-m-d') . '.log');
        }
        $this->statement = $this->pdo->prepare($sql);
        $this->statement->execute($paramas);
    }

    /**
     * 向数据表中插入一条数据
     *
     * @param array $paramas
     *            格式：array(colname1=>value1,...,...) 插入的数据内容
     * @param array $onDuplicateKey
     *            格式：array(cols,cols,...) 如果插入的数据中有和uniq_key冲突的，则更新的字段，注意不能包含唯一键
     * @return int lastInsertId
     */
    public function addOne(array $paramas, array $onDuplicateKey = array())
    {
        if (empty($paramas)) {
            return 0;
        }
        $data = $this->setParamas($paramas);
        $sql = 'insert into ' . $this->tb . " ({$data['cols']}) " . " values ({$data['values']})";
        if ($onDuplicateKey && is_array($onDuplicateKey)) {
            foreach ($onDuplicateKey as $k) {
                $paramas[$k . '1'] = $paramas[$k];
            }
            $on_duplicate_key = $this->setUpdate($onDuplicateKey, '=', '1');
            $sql .= ' on duplicate key update ' . $on_duplicate_key;
        }
        $this->execSql($sql, $paramas);
        
        return $this->pdo->lastInsertId();
    }

    /**
     * 向特定数据表中一次插入多条数据
     *
     * @param array $arrParamas
     *            格式:array(array(colname=>value,...,...),array(colname=>value,...,...),array(colname=>value,...,...)...)
     * @return int lastInsertId
     */
    public function addMulti(array $paramas)
    {
        $paramas = array_filter($paramas);
        if (empty($paramas)) {
            return 0;
        }
        $data = $this->setMultiParamas($paramas, array_keys($paramas[0]));
        $sql = "INSERT INTO  `{$this->tb}` {$data['cols']} VALUES " . implode(',', $data['values']);
        $this->execSql($sql, $data['paramas']);
        return $this->pdo->lastInsertId();
    }

    /**
     *
     * @param string $where
     *            可写成： 'col=:col,col>:col,...'的形式,parama自动调用
     * @param array $paramas
     *            要更新的数据（可包含查询条件的参数）
     * @return int 更新的行数
     */
    public function update($where, array $paramas)
    {
        $updata = $this->setUpdate(array_keys($paramas));
        $sql = 'update ' . $this->tb . ' set ' . $updata . ' where ' . $where;
        $this->execSql($sql, $paramas);
        return $this->statement->rowCount();
    }

    /**
     * 原子更新，对某个字段做原子操作
     *
     * @param string $where            
     * @param array $col
     *            array(key=>value),数组之允许一个值
     * @param string $change            
     * @return int 受影响行数
     */
    public function updateAtom($where, array $col, $change = '+')
    {
        if (count($col) > 1) {
            return 0;
        }
        $k = key($col);
        $sql = 'update ' . $this->tb . " set `$k`" . "=$k {$change} :$k" . ' where ' . $where;
        $this->execSql($sql, $col);
        return $this->statement->rowCount();
    }

    /**
     * 从表中获取指定条件的一条记录
     *
     * @param string $where            
     * @param array $paramas
     *            array(key=>value,...,...)
     * @param string $cols            
     * @param string $orderBy            
     * @return array
     */
    public function fetchOne($where = '1', array $paramas = array(), $cols = '*', $orderBy = '')
    {
        $cols = $this->setCols($cols);
        $sql = 'select ' . $cols . ' from ' . $this->tb . ' where ' . $where;
        $sql .= $orderBy ? ' order by ' . $orderBy . ' limit 1' : ' limit 1';
        $this->execSql($sql, $paramas);
        return $this->statement->fetch(\PDO::FETCH_ASSOC);
    }

    /**
     * 从表中获取指定条件的所有数据
     *
     * @param string $where            
     * @param array $paramas
     *            array(key=>value,...,...)
     * @param string $cols            
     * @param string $orderBy            
     * @param string $limit            
     * @return array
     */
    public function fetchAll($where = '1', array $paramas = array(), $cols = '*', $orderBy = '', $limit = '')
    {
        $cols = $this->setCols($cols);
        $sql = "select $cols from {$this->tb} where $where";
        if ($orderBy) {
            $sql .= ' order by ' . $orderBy;
        }
        if ($limit) {
            $sql .= ' limit ' . $limit;
        }
        $this->execSql($sql, $paramas);
        return $this->statement->fetchAll(\PDO::FETCH_ASSOC);
    }
    /**
     * 从表中获取指定条件的所有列
     *
     * @param string $where
     * @param array $paramas
     *            array(key=>value,...,...)
     * @param string $col 只能是一个字段的名称
     * @param string $orderBy
     * @param string $limit
     * @return array
     */
    public function fetchCol($where = '1', array $paramas = array(), $col = '', $orderBy = '', $limit = '')
    {
        $ret = array();
        if($col == '*')
        {
            return $ret;
        }
        if(count(explode(',', $col)) > 1)
        {
            return $ret;
        }
        $sql = "select `$col` from {$this->tb} where $where";
        if ($orderBy) {
            $sql .= ' order by ' . $orderBy;
        }
        if ($limit) {
            $sql .= ' limit ' . $limit;
        }
        $this->execSql($sql, $paramas);
        $rows = $this->statement->fetchAll(\PDO::FETCH_ASSOC);
        foreach ($rows as $v)
        {
            $ret[] = $v[$col];
        }
        return $ret;
    }
    /**
     * 给定条件，查询数量
     *
     * @param string $where            
     * @param array $paramas            
     * @return int
     */
    public function getCount($where = '1', array $paramas = array())
    {
        $sql = 'select count(*) as c  from ' . $this->tb . ' where ' . $where;
        $this->execSql($sql, $paramas);
        $rows = $this->statement->fetch(\PDO::FETCH_ASSOC);
        return $rows['c'];
    }

    /**
     * 指定条件删除记录
     *
     * @param string $where            
     * @param array $paramas            
     * @return int 返回删除行数
     */
    public function remove($where, array $paramas = array())
    {
        $sql = 'delete from ' . $this->tb . ' where ' . $where;
        $this->execSql($sql, $paramas);
        return $this->statement->rowCount();
    }
    // 严重不支持使用此方法，自定义sql语句，返回所有结果
    public function fetchSql($sql)
    {
        $this->execSql($sql);
        return $this->statement->fetchAll(\PDO::FETCH_ASSOC);
    }
    // 严重不支持使用此方法，清空指定表
    public function truncate()
    {
        $sql = 'truncate table ' . $this->tb;
        $this->execSql($sql);
    }
}

php-pdo.php

<?php
$pdo_dns = 'mysql:host=host;port=port;dbname=dbname';
$pdo_options = array(
    \PDO::MYSQL_ATTR_INIT_COMMAND => 'set names utf8', // 字符编码设定
    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, // 错误处理模式
    \PDO::ATTR_PERSISTENT => true, // 启用长连接
    \PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true, //启用mysql 查询缓存
);
try {
    $pdo = new \PDO($pdo_dns, $user,$pw, $pdo_options);
} catch (\PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}