- <?php
-
- /**
- +------------------------------------------------------------------------------
- * Run Framework 通用数据库访问接口
- +------------------------------------------------------------------------------
- * @date 2017-7
- * @version 1.0
- +------------------------------------------------------------------------------
- */
- class RunDbPdo {
-
- //数据库类型
- public $dbType = null;
- //分页对象
- public $page = null;
- //连接数据库配置文件
- public $configFile = null;
- //当前连接ID
- private $connectId = null;
- //操作所影响的行数
- private $affectedRows = 0;
- //查询结果对象
- private $PDOStatement = null;
- //当前数据库id
- public $dbId = null;
-
- /**
- +----------------------------------------------------------
- * 类的构造子
- +----------------------------------------------------------
- * @access public
- +----------------------------------------------------------
- */
- public function __construct() {
- RunException::throwException('Not Support : PDO');
- }
- }
-
- /**
- +----------------------------------------------------------
- * 类的析构方法(负责资源的清理工作)
- +----------------------------------------------------------
- * @access public
- +----------------------------------------------------------
- */
- public function __destruct() {
- $this->close();
- $this->dbType = null;
- $this->configFile = null;
- $this->connectId = null;
- $this->PDOStatement = null;
- $this->dbId = null;
- }
-
- /**
- +----------------------------------------------------------
- * 打开数据库连接
- +----------------------------------------------------------
- * @access public
- +----------------------------------------------------------
- */
- private function connect() {
- if ($this->connectId == null) {
- }
- require($this->configFile);
- $this->connectId = new PDO("mysql:host={$host};dbname={$db}", $user, $password);
- $this->connectId->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //打开PDO错误提示
- if ($this->dbType == 'mysql') {
- $this->connectId->exec("set names $encode");
- }
- $dsn = $username = $password = $encode = null;
- if ($this->connectId == null) {
- RunException::throwException("PDO CONNECT ERROR");
- }
- }
- }
-
- /**
- +----------------------------------------------------------
- * 关闭数据库连接
- +----------------------------------------------------------
- * @access public
- +----------------------------------------------------------
- */
- public function close() {
- $this->connectId = null;
- }
-
- /**
- +----------------------------------------------------------
- * 释放查询结果
- +----------------------------------------------------------
- * @access public
- +----------------------------------------------------------
- */
- private function free() {
- $this->PDOStatement = null;
- }
-
- /**
- +----------------------------------------------------------
- * 执行语句 针对 INSERT, UPDATE 以及DELETE
- +----------------------------------------------------------
- * @access public
- +----------------------------------------------------------
- * @param string $sql sql指令
- +----------------------------------------------------------
- * @return boolean
- +----------------------------------------------------------
- */
- public function query($sql) {
- if ($this->connectId == null) {
- $this->connect();
- }
- $this->affectedRows = $this->connectId->exec($sql);
- return $this->affectedRows >= 0 ? true : false;
- }
-
- /**
- +----------------------------------------------------------
- * 返回操作所影响的行数(INSERT、UPDATE 或 DELETE)
- +----------------------------------------------------------
- * @access public
- +----------------------------------------------------------
- * @return integer
- +----------------------------------------------------------
- */
- public function getAffected() {
- if ($this->connectId == null) {
- return 0;
- }
- return $this->affectedRows;
- }
-
- /**
- +----------------------------------------------------------
- * 获得一条查询记录
- +----------------------------------------------------------
- * @access public
- +----------------------------------------------------------
- * @param string $sql SQL指令
- +----------------------------------------------------------
- * @return array
- +----------------------------------------------------------
- */
- public function getRow($sql) {
- if ($this->connectId == null) {
- $this->connect();
- }
- $this->PDOStatement = $this->connectId->prepare($sql);
- $this->PDOStatement->execute();
-
- $this->error($sql);
- return $result;
- }
-
- $this->free();
-
- return $result;
- }
-
- /**
- +----------------------------------------------------------
- * 获得多条查询记录
- +----------------------------------------------------------
- * @access public
- +----------------------------------------------------------
- * @param string $sql SQL指令
- +----------------------------------------------------------
- * @return array
- +----------------------------------------------------------
- */
- public function getRows($sql) {
- if ($this->connectId == null) {
- $this->connect();
- }
- $this->PDOStatement = $this->connectId->prepare($sql);
- $this->PDOStatement->execute();
-
- $this->error($sql);
- return $result;
- }
-
- $this->free();
-
- return $result;
- }
-
- /**
- +----------------------------------------------------------
- * 获得多条查询数据(带分页条)
- +----------------------------------------------------------
- * @access public
- +----------------------------------------------------------
- * @param string $query SQL指令
- +----------------------------------------------------------
- * @param int $pageRows 每页显示的记录条数
- +----------------------------------------------------------
- * @return array
- +----------------------------------------------------------
- */
- public function getPageRows($query, $pageRows = 20) {
- if ((int) $pageRows > 0){
- $this->page->pageRows = $pageRows;
- }
- $row = $this->getRow($sqlCount);
-
- // group count
- if (preg_match('!(GROUP[[:space:]]+BY|HAVING|SELECT[[:space:]]+DISTINCT)[[:space:]]+!is', $sqlCount)) {
- $sqlCount = preg_replace("|SELECT.*?FROM([\s])|i", "SELECT COUNT(*) as total FROM$1", $sqlCount, 1);
- $rows = $this->getRows($sqlCount);
- }
-
- //计算分页的偏移量
- $offset = ($pageId - 1) * $pageRows;
- $offset = ($offset < 0) ? 0 : $offset;
- $query .= ' LIMIT ' . $offset . ',' . $this->page->pageRows;
- $data['pageBar'] = $this->page->get($total, $page);
- $data['record'] = $this->getRows($query);
- $data['total'] = $total;
- return $data;
- }
-
- /**
- +----------------------------------------------------------
- * 获得最后一次插入的id
- +----------------------------------------------------------
- * @access public
- +----------------------------------------------------------
- * @return int
- +----------------------------------------------------------
- */
- public function getLastInsertId() {
- if ($this->connectId != null) {
- return $this->connectId->lastInsertId();
- }
- return 0;
- }
-
- /**
- +----------------------------------------------------------
- * 添加数据(辅助方法)
- +----------------------------------------------------------
- * @access public
- +----------------------------------------------------------
- * @param string $table 表名
- +----------------------------------------------------------
- * @param array $arr 插入的数据(键值对)
- +----------------------------------------------------------
- * @return mixed
- +----------------------------------------------------------
- */
- $field = $value = "";
- foreach ($arr as $k => $v) {
- $field .= "$k,";
- $value .= "'$v',";
- }
- $sql = "insert into $table($field) values($value)";
- return $this->query($sql);
- }
- }
-
- /**
- +----------------------------------------------------------
- * 返回最后一次使用 INSERT 指令的 ID
- +----------------------------------------------------------
- * @access public
- +----------------------------------------------------------
- * @return integer
- +----------------------------------------------------------
- */
- public function getLastInsId() {
- if ($this->connectId != null) {
- return $this->connectId->lastInsertId();
- }
- return 0;
- }
-
- /**
- +----------------------------------------------------------
- * 更新数据(辅助方法)
- +----------------------------------------------------------
- * @access public
- +----------------------------------------------------------
- * @param string $table 表名
- +----------------------------------------------------------
- * @param array $arr 更新的数据(键值对)
- +----------------------------------------------------------
- * @param mixed $where 条件
- +----------------------------------------------------------
- * @return mixed
- +----------------------------------------------------------
- */
- $field = "";
- $loop = 1;
- $sql = "UPDATE {$table} SET ";
- foreach ($arr as $k => $v) {
- $field .= $k . "='" . $v . "',";
- }
-
- $sql .= " WHERE ";
- foreach ($where as $wFiled => $wValue){
- $sql .= $wFiled . " = " . "'$wValue'" . " AND ";
- }
- } else {
- $sql .= " WHERE $where";
- }
- }
- return $this->query($sql);
- }
-
- /**
- +----------------------------------------------------------
- * 删除数据(辅助方法)
- +----------------------------------------------------------
- * @access public
- +----------------------------------------------------------
- * @param string $table 表名
- +----------------------------------------------------------
- * @param mixed $where 条件
- +----------------------------------------------------------
- * @return mixed
- +----------------------------------------------------------
- */
- public function delete($table, $where = '') {
- $sql = "delete from {$table} ";
- $sql .= " where ";
- foreach ($where as $wFiled => $wValue)
- $sql .= $wFiled . " = " . $wValue . " AND ";
- } else {
- $sql .= " where $where";
- }
- return $this->query($sql);
- }
- }
-
- /**
- +----------------------------------------------------------
- * 开启事物(辅助方法)
- +----------------------------------------------------------
- * @access public
- +----------------------------------------------------------
- * @param int $isXA 是否开启分布式事务
- +----------------------------------------------------------
- * @return mixed
- +----------------------------------------------------------
- */
- public function startTrans() {
- $result = $this->commit();
- if (!$result) {
- $this->error("开启事务失败!");
- return false;
- }
- $this->query('SET AUTOCOMMIT=0');
- $this->query('START TRANSACTION'); //开启事务
- return true;
- }
-
- /**
- +----------------------------------------------------------
- * 分布式事物准备(辅助方法)
- +----------------------------------------------------------
- * @access public
- +----------------------------------------------------------
- * @return mixed
- +----------------------------------------------------------
- */
- public function prepare($XID) {
- $connectId = $this->XATransConnectId;
- return;
- }
-
- /**
- +----------------------------------------------------------
- * 事物提交(辅助方法)
- +----------------------------------------------------------
- * @access public
- +----------------------------------------------------------
- * @return mixed
- +----------------------------------------------------------
- */
- public function commit() {
- $result = $this->query('COMMIT'); //提交事务
- if (!$result) {
- return false;
- }
- $this->query('SET AUTOCOMMIT=1');
- return true;
- }
-
- /**
- +----------------------------------------------------------
- * 事物回滚(辅助方法)
- +----------------------------------------------------------
- * @access public
- +----------------------------------------------------------
- * @return mixed
- +----------------------------------------------------------
- */
- public function rollback() {
- $result = $this->query('ROLLBACK'); //回滚
- if (!$result){
- return false;
- }
- $this->query('SET AUTOCOMMIT=1');
- return true;
- }
-
- /**
- +----------------------------------------------------------
- * 数据库错误信息
- * 并显示当前的SQL语句
- +----------------------------------------------------------
- * @access private
- +----------------------------------------------------------
- */
- private function error($sql) {
- $error = $this->PDOStatement->errorInfo();
- $str = $error[2];
- if ($sql != ''){
- $str .= "\n [ SQL语句 ] : " . $sql;
- }
- RunException::throwException($str);
- }
-
- }
-
- ?>
使用方法:
创建配置文件:/config/user.config.php
- <?php
- $host = '127.0.0.1';
- $db = 'user';
- $user = 'root';
- $password = 'root';
执行方法:
- require './RunDbPdo.php';
- $model = new RunDbPdo();
- $model->configFile = './config/user.config.php';
- $sql = "insert into mm_user(username,age) values('{$username}','{$age}')";
- $res = $model->query($sql);
- $insert_id = $model->getLastInsId();