PHP MySQL操作PDO封装类

Jackey MySQL, PHP 3,971 次浏览 , 没有评论
  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