PHP MySQL操作PDO封装类

Jackey MySQL, PHP 4,550 次浏览 , 没有评论 
  1. <?php
  2.  
  3. /**
  4.   +------------------------------------------------------------------------------
  5.  * Run Framework 通用数据库访问接口
  6.   +------------------------------------------------------------------------------
  7.  * @date    2017-7
  8.  * @version 1.0
  9.   +------------------------------------------------------------------------------
  10.  */
  11. class RunDbPdo {
  12.  
  13.     //数据库类型
  14.     public $dbType = null;
  15.     //分页对象
  16.     public $page = null;
  17.     //连接数据库配置文件
  18.     public $configFile = null;
  19.     //当前连接ID
  20.     private $connectId = null;
  21.     //操作所影响的行数
  22.     private $affectedRows = 0;
  23.     //查询结果对象
  24.     private $PDOStatement = null;
  25.     //当前数据库id
  26.     public $dbId = null;
  27.  
  28.     /**
  29.       +----------------------------------------------------------
  30.      * 类的构造子
  31.       +----------------------------------------------------------
  32.      * @access public 
  33.       +----------------------------------------------------------
  34.      */
  35.     public function __construct() {
  36.         if (!class_exists('PDO')) {
  37.             RunException::throwException('Not Support : PDO');
  38.         }
  39.     }
  40.  
  41.     /**
  42.       +----------------------------------------------------------
  43.      * 类的析构方法(负责资源的清理工作)
  44.       +----------------------------------------------------------
  45.      * @access public 
  46.       +----------------------------------------------------------
  47.      */
  48.     public function __destruct() {
  49.         $this->close();
  50.         $this->dbType = null;
  51.         $this->configFile = null;
  52.         $this->connectId = null;
  53.         $this->PDOStatement = null;
  54.         $this->dbId = null;
  55.     }
  56.  
  57.     /**
  58.       +----------------------------------------------------------
  59.      * 打开数据库连接
  60.       +----------------------------------------------------------
  61.      * @access public 
  62.       +----------------------------------------------------------
  63.      */
  64.     private function connect() {
  65.         if ($this->connectId == null) {
  66.             if (!file_exists($this->configFile)) {
  67.                 die("not configFile!");
  68.             }
  69.             require($this->configFile);
  70.             $this->connectId = new PDO("mysql:host={$host};dbname={$db}", $user, $password);
  71.             $this->connectId->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //打开PDO错误提示
  72.             if ($this->dbType == 'mysql') {
  73.                 $this->connectId->exec("set names $encode");
  74.             }
  75.             $dsn = $username = $password = $encode = null;
  76.             if ($this->connectId == null) {
  77.                 RunException::throwException("PDO CONNECT ERROR");
  78.             }
  79.         }
  80.     }
  81.  
  82.     /**
  83.       +----------------------------------------------------------
  84.      * 关闭数据库连接
  85.       +----------------------------------------------------------
  86.      * @access public 
  87.       +----------------------------------------------------------
  88.      */
  89.     public function close() {
  90.         $this->connectId = null;
  91.     }
  92.  
  93.     /**
  94.       +----------------------------------------------------------
  95.      * 释放查询结果
  96.       +----------------------------------------------------------
  97.      * @access public
  98.       +----------------------------------------------------------
  99.      */
  100.     private function free() {
  101.         $this->PDOStatement = null;
  102.     }
  103.  
  104.     /**
  105.       +----------------------------------------------------------
  106.      * 执行语句 针对 INSERT, UPDATE 以及DELETE
  107.       +----------------------------------------------------------
  108.      * @access public 
  109.       +----------------------------------------------------------
  110.      * @param string $sql  sql指令
  111.       +----------------------------------------------------------
  112.      * @return boolean
  113.       +----------------------------------------------------------
  114.      */
  115.     public function query($sql) {
  116.         if ($this->connectId == null) {
  117.             $this->connect();
  118.         }
  119.         $this->affectedRows = $this->connectId->exec($sql);
  120.         return $this->affectedRows >= 0 ? true : false;
  121.     }
  122.  
  123.     /**
  124.       +----------------------------------------------------------
  125.      * 返回操作所影响的行数(INSERT、UPDATE 或 DELETE)
  126.       +----------------------------------------------------------
  127.      * @access public 
  128.       +----------------------------------------------------------
  129.      * @return integer
  130.       +----------------------------------------------------------
  131.      */
  132.     public function getAffected() {
  133.         if ($this->connectId == null) {
  134.             return 0;
  135.         }
  136.         return $this->affectedRows;
  137.     }
  138.  
  139.     /**
  140.       +----------------------------------------------------------
  141.      * 获得一条查询记录
  142.       +----------------------------------------------------------
  143.      * @access public 
  144.       +----------------------------------------------------------
  145.      * @param string  $sql  SQL指令
  146.       +----------------------------------------------------------
  147.      * @return array
  148.       +----------------------------------------------------------
  149.      */
  150.     public function getRow($sql) {
  151.         if ($this->connectId == null) {
  152.             $this->connect();
  153.         }
  154.         $result = array();   //返回数据集
  155.         $this->PDOStatement = $this->connectId->prepare($sql);
  156.         $this->PDOStatement->execute();
  157.  
  158.         if (empty($this->PDOStatement)) {
  159.             $this->error($sql);
  160.             return $result;
  161.         }
  162.  
  163.         $result = $this->PDOStatement->fetch(constant('PDO::FETCH_ASSOC'));
  164.         $this->free();
  165.  
  166.         return $result;
  167.     }
  168.  
  169.     /**
  170.       +----------------------------------------------------------
  171.      * 获得多条查询记录
  172.       +----------------------------------------------------------
  173.      * @access public 
  174.       +----------------------------------------------------------
  175.      * @param string  $sql  SQL指令
  176.       +----------------------------------------------------------
  177.      * @return array
  178.       +----------------------------------------------------------
  179.      */
  180.     public function getRows($sql) {
  181.         if ($this->connectId == null) {
  182.             $this->connect();
  183.         }
  184.         $result = array();   //返回数据集
  185.         $this->PDOStatement = $this->connectId->prepare($sql);
  186.         $this->PDOStatement->execute();
  187.  
  188.         if (empty($this->PDOStatement)) {
  189.             $this->error($sql);
  190.             return $result;
  191.         }
  192.  
  193.         $result = $this->PDOStatement->fetchAll(constant('PDO::FETCH_ASSOC'));
  194.         $this->free();
  195.  
  196.         return $result;
  197.     }
  198.  
  199.     /**
  200.       +----------------------------------------------------------
  201.      * 获得多条查询数据(带分页条)
  202.       +----------------------------------------------------------
  203.      * @access public 
  204.       +----------------------------------------------------------
  205.      * @param  string $query    SQL指令
  206.       +----------------------------------------------------------
  207.      * @param  int    $pageRows 每页显示的记录条数
  208.       +----------------------------------------------------------
  209.      * @return array
  210.       +----------------------------------------------------------
  211.      */
  212.     public function getPageRows($query, $pageRows = 20) {
  213.         if (!is_object($this->page))
  214.             return array();
  215.         $page = isset($_GET['page']) ? (int) $_GET['page'] : 0;
  216.         if ((int) $pageRows > 0){
  217.             $this->page->pageRows = $pageRows;
  218.         }
  219.         $sqlCount = preg_replace("|SELECT.*?FROM([\s])|i", "SELECT COUNT(*) as total FROM$1", $query, 1);
  220.         $row = $this->getRow($sqlCount);
  221.         $total = isset($row['total']) ? $row['total'] : 0;
  222.  
  223.         // group count
  224.         if (preg_match('!(GROUP[[:space:]]+BY|HAVING|SELECT[[:space:]]+DISTINCT)[[:space:]]+!is', $sqlCount)) {
  225.             $sqlCount = preg_replace('!(order[[:space:]]+BY)[[:space:]]+.*!is', '', $query, 1);
  226.             $sqlCount = preg_replace("|SELECT.*?FROM([\s])|i", "SELECT COUNT(*) as total FROM$1", $sqlCount, 1);
  227.             $rows = $this->getRows($sqlCount);
  228.             $total = empty($rows) ? 0 : count($rows);
  229.         }
  230.  
  231.         //计算分页的偏移量
  232.         $pageId = isset($page) ? $page : 1;
  233.         $offset = ($pageId - 1) * $pageRows;
  234.         $offset = ($offset < 0) ? 0 : $offset;
  235.         $query .= ' LIMIT ' . $offset . ',' . $this->page->pageRows;
  236.         $data['pageBar'] = $this->page->get($total, $page);
  237.         $data['record'] = $this->getRows($query);
  238.         $data['total'] = $total;
  239.         return $data;
  240.     }
  241.  
  242.     /**
  243.       +----------------------------------------------------------
  244.      * 获得最后一次插入的id
  245.       +----------------------------------------------------------
  246.      * @access public
  247.       +----------------------------------------------------------
  248.      * @return int
  249.       +----------------------------------------------------------
  250.      */
  251.     public function getLastInsertId() {
  252.         if ($this->connectId != null) {
  253.             return $this->connectId->lastInsertId();
  254.         }
  255.         return 0;
  256.     }
  257.  
  258.     /**
  259.       +----------------------------------------------------------
  260.      * 添加数据(辅助方法)
  261.       +----------------------------------------------------------
  262.      * @access public
  263.       +----------------------------------------------------------
  264.      * @param string  $table  表名
  265.       +----------------------------------------------------------
  266.      * @param array   $arr    插入的数据(键值对)
  267.       +----------------------------------------------------------
  268.      * @return mixed
  269.       +----------------------------------------------------------
  270.      */
  271.     public function insert($table, $arr = array()) {
  272.         $field = $value = "";
  273.         if (!empty($arr) && is_array($arr)) {
  274.             foreach ($arr as $k => $v) {
  275.                 $v = preg_replace("/'/", "\\'", $v);
  276.                 $field .= "$k,";
  277.                 $value .= "'$v',";
  278.             }
  279.             $field = preg_replace("/,$/", "", $field);
  280.             $value = preg_replace("/,$/", "", $value);
  281.             $sql = "insert into $table($field) values($value)";
  282.             return $this->query($sql);
  283.         }
  284.     }
  285.  
  286.     /**
  287.       +----------------------------------------------------------
  288.      * 返回最后一次使用 INSERT 指令的 ID
  289.       +----------------------------------------------------------
  290.      * @access public 
  291.       +----------------------------------------------------------
  292.      * @return integer
  293.       +----------------------------------------------------------
  294.      */
  295.     public function getLastInsId() {
  296.         if ($this->connectId != null) {
  297.             return $this->connectId->lastInsertId();
  298.         }
  299.         return 0;
  300.     }
  301.  
  302.     /**
  303.       +----------------------------------------------------------
  304.      * 更新数据(辅助方法)
  305.       +----------------------------------------------------------
  306.      * @access public
  307.       +----------------------------------------------------------
  308.      * @param string  $table  表名
  309.       +----------------------------------------------------------
  310.      * @param array   $arr    更新的数据(键值对)
  311.       +----------------------------------------------------------
  312.      * @param mixed   $where  条件
  313.       +----------------------------------------------------------
  314.      * @return mixed
  315.       +----------------------------------------------------------
  316.      */
  317.     public function update($table, $arr = array(), $where = '') {
  318.         $field = "";
  319.         $loop = 1;
  320.         $len = count($arr);
  321.         $sql = "UPDATE {$table} SET ";
  322.         foreach ($arr as $k => $v) {
  323.             $v = preg_replace("/'/", "\\'", $v);
  324.             $field .= $k . "='" . $v . "',";
  325.         }
  326.         $sql .= trim($field, ',');
  327.  
  328.         if (!empty($where)) {
  329.             if (is_array($where)) {
  330.                 $sql .= " WHERE ";
  331.                 foreach ($where as $wFiled => $wValue){
  332.                     $sql .= $wFiled . " = " . "'$wValue'" . " AND ";
  333.                 }
  334.                 $sql = trim($sql, " AND ");
  335.             } else {
  336.                 $sql .= " WHERE $where";
  337.             }
  338.         }
  339.         return $this->query($sql);
  340.     }
  341.  
  342.     /**
  343.       +----------------------------------------------------------
  344.      * 删除数据(辅助方法)
  345.       +----------------------------------------------------------
  346.      * @access public
  347.       +----------------------------------------------------------
  348.      * @param string  $table  表名
  349.       +----------------------------------------------------------
  350.      * @param mixed   $where  条件
  351.       +----------------------------------------------------------
  352.      * @return mixed
  353.       +----------------------------------------------------------
  354.      */
  355.     public function delete($table, $where = '') {
  356.         $sql = "delete from {$table} ";
  357.         if (!empty($where)) {
  358.             if (is_array($where)) {
  359.                 $sql .= " where ";
  360.                 foreach ($where as $wFiled => $wValue)
  361.                     $sql .= $wFiled . " = " . $wValue . " AND ";
  362.                 $sql = trim($sql, " AND ");
  363.             } else {
  364.                 $sql .= " where $where";
  365.             }
  366.             return $this->query($sql);
  367.         }
  368.     }
  369.  
  370.     /**
  371.       +----------------------------------------------------------
  372.      * 开启事物(辅助方法)
  373.       +----------------------------------------------------------
  374.      * @access public
  375.       +----------------------------------------------------------
  376.      * @param  int  $isXA  是否开启分布式事务
  377.       +----------------------------------------------------------
  378.      * @return mixed
  379.       +----------------------------------------------------------
  380.      */
  381.     public function startTrans() {
  382.         $result = $this->commit();
  383.         if (!$result) {
  384.             $this->error("开启事务失败！");
  385.             return false;
  386.         }
  387.         $this->query('SET AUTOCOMMIT=0');
  388.         $this->query('START TRANSACTION');                                    //开启事务
  389.         return true;
  390.     }
  391.  
  392.     /**
  393.       +----------------------------------------------------------
  394.      * 分布式事物准备(辅助方法)
  395.       +----------------------------------------------------------
  396.      * @access public
  397.       +----------------------------------------------------------
  398.      * @return mixed
  399.       +----------------------------------------------------------
  400.      */
  401.     public function prepare($XID) {
  402.         $connectId = $this->XATransConnectId;
  403.         mysql_query("XA END '$XID'", $connectId);                                        //结束事务
  404.         mysql_query("XA PREPARE '$XID'", $connectId);                                    //消息提示
  405.         return;
  406.     }
  407.  
  408.     /**
  409.       +----------------------------------------------------------
  410.      * 事物提交(辅助方法)
  411.       +----------------------------------------------------------
  412.      * @access public
  413.       +----------------------------------------------------------
  414.      * @return mixed
  415.       +----------------------------------------------------------
  416.      */
  417.     public function commit() {
  418.         $result = $this->query('COMMIT');                                         //提交事务
  419.         if (!$result) {
  420.             return false;
  421.         }
  422.         $this->query('SET AUTOCOMMIT=1');
  423.         return true;
  424.     }
  425.  
  426.     /**
  427.       +----------------------------------------------------------
  428.      * 事物回滚(辅助方法)
  429.       +----------------------------------------------------------
  430.      * @access public
  431.       +----------------------------------------------------------
  432.      * @return mixed
  433.       +----------------------------------------------------------
  434.      */
  435.     public function rollback() {
  436.         $result = $this->query('ROLLBACK');                                         //回滚
  437.         if (!$result){
  438.             return false;
  439.         }
  440.         $this->query('SET AUTOCOMMIT=1');
  441.         return true;
  442.     }
  443.  
  444.     /**
  445.       +----------------------------------------------------------
  446.      * 数据库错误信息
  447.      * 并显示当前的SQL语句
  448.       +----------------------------------------------------------
  449.      * @access private 
  450.       +----------------------------------------------------------
  451.      */
  452.     private function error($sql) {
  453.         $error = $this->PDOStatement->errorInfo();
  454.         $str = $error[2];
  455.         if ($sql != ''){
  456.             $str .= "\n [ SQL语句 ] : " . $sql;
  457.         }
  458.         RunException::throwException($str);
  459.     }
  460.  
  461. }
  462.  
  463. ?>

 

使用方法：

创建配置文件：/config/user.config.php

  1. <?php
  2. $host = '127.0.0.1';
  3. $db = 'user';
  4. $user = 'root';
  5. $password = 'root';

 

执行方法：

  1. require './RunDbPdo.php';
  2. extract($_POST);
  3. $username = isset($username)?$username:'';
  4. $age = isset($age)?$age:0;
  5. $model = new RunDbPdo();
  6. $model->configFile = './config/user.config.php';
  7. $sql = "insert into mm_user(username,age) values('{$username}','{$age}')";
  8. $res = $model->query($sql);
  9. $insert_id = $model->getLastInsId();

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注

Go