pdo数据库操作类

pdo,有不局限数据库,和防止sql注入等很多优点,也是php官方推荐的方式,所以花点时间写个pdo数据库操作类!

<?php

class PDOX
{
    private $config;
    private $pdo;
    public $lastSql = '';

    /**
     * 事务开启状态
     * @var
     */
    public $Transactions = false;


    function __construct()
    {
        $this->config = parse_ini_file('db.ini', true);

        $dsn = 'mysql:dbname=' . $this->config["dbname"] . ';host=' . $this->config["host"] . '';

        try {
            $this->pdo = new PDO($dsn, $this->config["user"], $this->config["password"], array(
                PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"
            ));

            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

            $this->bConnected = true;
        } catch (PDOException $e) {
            throw new PDOException($e->getMessage(), $e->getCode());
        }
    }

    /**
     * 关闭链接
     */

    public function closeConnect()
    {
        $this->pdo = null;
    }

    /**
     * 转义字符串
     * @param $string
     * @return bool
     * @throws Exception
     */
    public function escapeString($string)
    {
        //参数分析
        if (!$string) {
            throw new Exception('$string parameter is null');
        }
        $_quoteString = $this->pdo->quote($string);
        if ($_quoteString === false) {
            throw new Exception('the driver does not support quoting in this way');
        } else {
            return $_quoteString;
        }
    }

    /**
     * 获取数据库错误信息
     * @return mixed
     */

    public function errorMsg()
    {
        $info = $this->pdo->errorInfo();
        return $info[2];
    }

    /**
     * 获取数据库错误信息代码
     *
     * @access public
     * @return int
     */
    public function errorNum()
    {

        return $this->pdo->errorCode();
    }


    /**
     * 得到插入id
     * @return string
     */
    public function lastInsertId()
    {
        return $this->pdo->lastInsertId();
    }


    /**
     * 得到最近执行的一条sql语句
     * @return string
     */
    public function getLastSql()
    {
        return $this->lastSql;

    }

    /**
     * 开始事务
     * @return bool
     */
    public function startTrans()
    {

        if ($this->Transactions == false) {
            $this->pdo->beginTransaction();
            $this->Transactions = true;
        }

        return true;
    }

    /**
     * 提交事务
     * @return bool
     */
    public function commit()
    {

        if ($this->Transactions == true) {
            if ($this->pdo->commit()) {
                $this->Transactions = false;
            }
        }
        return true;
    }

    /**
     * 事务回滚
     */

    public function rollback()
    {
        if ($this->Transactions == true) {
            if ($this->pdo->rollBack()) {
                $this->Transactions = false;
            }
        }
    }

    /**
     * @param $sql
     * @return PDOStatement
     * @throws Exception|boolean
     */
    public function query($sql)
    {
        if (!$sql) {
            throw new Exception('sql parameter is null');
        }
        $this->lastSql = $sql;
        $result = $this->pdo->query($sql);//返回 true or false
        return $result;
    }

    /**
     * 返回执行sql后影响的行数
     * @param $sql
     * @return int
     * @throws Exception
     */

    public function exec($sql)
    {
        if (!$sql) {
            throw new Exception('sql parameter is null');
        }
        $this->lastSql = $sql;
        $result = $this->pdo->exec($sql);
        return $result;
    }

    public function getRow($sql)
    {
        if (!$sql) {
            throw new Exception('sql parameter is null');
        }
        $this->lastSql = $sql;
        $result = $this->pdo->query($sql);
        if ($result === false) {
            throw new Exception('sql execute failed ' . $this->getLastSql());
        }
        $row = $result->fetch(PDO::FETCH_ASSOC);
        $result = null;
        return $row;
    }

    public function getAll($sql)
    {
        if (!$sql) {
            throw new Exception('sql parameter is null');
        }
        $this->lastSql = $sql;
        $result = $this->pdo->query($sql);
        if ($result === false) {
            throw new Exception('sql execute failed ' . $this->getLastSql());
        }
        return $result->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * @param $table
     * @param $arr_data
     * @return bool
     * @throws Exception
     */
    public function insert($table, $arr_data)
    {
        if (!$table || !$arr_data) {
            throw new Exception('table name and arr_data are null');
        }
        $keys_arr = [];
        $values_arr = [];
        $replace_values = [];
        foreach ($arr_data as $key => $value) {
            $keys_arr[] = '`' . $key . '`';
            $values_arr[] = ':' . $key;
            $replace_values[':' . $key] = $value;
        }
        $keys_str = implode(',', $keys_arr);
        $values_str = implode(',', $values_arr);
        $sql = sprintf('insert into `%s` (%s) values (%s)', $table, $keys_str, $values_str);
        $this->lastSql = $sql;
        $statement = $this->pdo->prepare($sql);
        return $statement->execute($statement);
    }

    public function delete($table, $sql_where)
    {
        if (!$table || !$sql_where) {
            throw new Exception('table name and sql_where are null');
        }
        $sql = sprintf('delete from `%s` where %s', $table, $sql_where);
        $this->lastSql = $sql;
        $result = $this->pdo->exec($sql);
        if ($result === false) {
            throw new Exception('sql execute failed ' . $this->getLastSql());
        }
        return $result;

    }


    public function update($table, $set, $sql_where)
    {
        if (!$table || !$sql_where) {
            throw new Exception('table name and sql_where are null');
        }
        $sql = sprintf('update `%s` %s  %s', $table, $set, $sql_where);
        $this->lastSql = $sql;
        $result = $this->pdo->exec($sql);
        if ($result === false) {
            throw new Exception('sql execute failed ' . $this->getLastSql());
        }
        return $result;

    }


    public function select($fields = '*', $table, $sql_where)
    {
        $fields_arr = [];
        $temp = [];
        if ($fields != '*') {
            $fields_arr = explode(',', $fields);
        }
        foreach ($fields_arr as $k => $v) {
            $temp[] = '`' . $v . '`';
        }
        $fields_str = implode(',', $temp);
        $sql = sprintf('select  %s  from %s where ', $fields_str, $table, $sql_where);
        $this->lastSql = $sql;
        $result = $this->query($sql);
        if ($result === false) {
            throw new Exception('sql execute failed ' . $this->getLastSql());
        }
        return $result;
    }


}