search

Home >Backend Development >PHP Tutorial >PHP PDO operation MYSQL encapsulation class

PHP PDO operation MYSQL encapsulation class

WBOY
WBOYOriginal
2016-07-25 08:42:281083browse
  1. /**
  2. * author soulence
  3. * Call data class file
  4. * modify 2015/06/12
  5. */
  6. class DBConnect
  7. {
  8. private $dbname = null;
  9. private $pdo = null;
  10. private $persistent = false;
  11. private $statement = null;
  12. private $lastInsID = null;
  13. private static $_instance = [];
  15. private function __construct($dbname,$attr)
  16. {
  17. $this->dbname = $dbname;
  18. $this->persistent = $attr;
  19. }
  21. public static function db($flag='r',$persistent=false)
  22. {
  23. if(!isset($flag)){
  24. $flag = 'r';
  25. }
  27. if (!class_exists('PDO'))
  28. {
  29. throw new Exception('not found PDO');
  30. return false;
  31. }
  32. $mysql_server = Yaf_Registry::get('mysql');
  33. if(!isset($mysql_server[$flag])){
  34. return false;
  35. }
  37. $options_arr = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES '.$mysql_server[$flag]['charset'],PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC);
  38. if($persistent === true){
  39. $options_arr[PDO::ATTR_PERSISTENT] = true;
  40. }
  42. try {
  43. $pdo = new PDO($mysql_server[$flag]['connectionString'],$mysql_server[$flag]['username'],$mysql_server[$flag]['password'],$options_arr);
  44. } catch (PDOException $e) {
  45. throw new Exception($e->getMessage());
  46. //exit('连接失败:'.$e->getMessage());
  47. return false;
  48. }
  50. if(!$pdo) {
  51. throw new Exception('PDO CONNECT ERROR');
  52. return false;
  53. }
  55. return $pdo;
  56. }
  58. /**
  59. * Get the operation database object
  60. * @param string $dbname Who is the corresponding database?
  61. * @param bool $attr Whether the connection is long
  62. * Return false indicates that the given database does not exist
  63. */
  64. public static function getInstance($dbname = 'r',$attr = false)
  65. {
  66. $mysql_server = Yaf_Registry::get('mysql');
  67. if(!isset($mysql_server[$dbname])){
  68. return false;
  69. }
  70. $key = md5(md5($dbname.$attr,true));
  71. if (!isset(self::$_instance[$key]) || !is_object(self::$_instance[$key]))
  72. self::$_instance[$key] = new self($dbname,$attr);
  73. return self::$_instance[$key];
  74. }
  76. private function getConnect(){
  77. $this->pdo = self::db($this->dbname,$this->persistent);
  78. }
  80. /**
  81. * Query operation
  82. * @param string $sql SQL statement to execute the query
  83. * @param array $data The conditional format of the query is [':id'=>$id,':name'=>$name]( Recommended) or [1=>$id,2=>$name]
  84. * @param bool $one Whether to return a piece of content, the default is no
  85. */
  86. public function query($sql, $data = [], $one = false)
  87. {
  88. if (!is_array($data) || empty($sql) || !is_string($sql))
  89. return false;
  91. $this->free();
  93. return $this->queryCommon($data,$sql,$one);
  94. }
  96. /**
  97. * Common methods for internal queries
  98. */
  99. private function queryCommon($data,$sql,$one)
  100. {
  101. $this->pdoExec($data,$sql);
  103. if ($one){
  104. return $this->statement->fetch(PDO::FETCH_ASSOC);
  105. }else{
  106. return $this->statement->fetchAll(PDO::FETCH_ASSOC);
  107. }
  108. }
  110. /**
  111. * Query operation of multiple SQL statements
  112. * @param array $arr_sql The sql statement array format for executing the query is [$sql1,$sql2]
  113. * @param array $arr_data The conditional format corresponding to the query $arr_sql is [[' :id'=>$id,':name'=>$name],[':id'=>$id,':name'=>$name]] (recommended) or [[1 =>$id,2=>$name],[1=>$id,2=>$name]]
  114. * @param bool $one Whether to return a piece of content. The default is no. If set to true, then Each sql only returns one piece of data
  115. */
  116. public function queryes($arr_sql, $arr_data = [], $one = false)
  117. {
  118. if(!is_array($arr_sql) || empty($arr_sql) || !is_array($arr_data))
  119. return false;
  121. $this->free();
  123. $res = [];$i = 0;
  124. foreach ($arr_sql as $val) {
  125. if(!isset($arr_data[$i]))
  126. $arr_data[$i] = [];
  127. elseif(!is_array($arr_data[$i]))
  128. throw new Exception('Error where queryes sql:'.$val.' where:'.$arr_data[$i]);
  130. $res[] = $this->queryCommon($arr_data[$i],$val,$one);
  131. $i++;
  132. }
  133. return $res;
  134. }
  136. /**
  137. * Paging encapsulation
  138. *
  139. * @param string $sql
  140. * @param int $page indicates which page to start from
  141. * @param int $pageSize indicates how many items per page
  142. * @param array $data query conditions
  143. */
  144. public function limitQuery($sql, $page=0, $pageSize=20, $data = [])
  145. {
  146. $page = intval($page);
  147. if ($page < 0) {
  148. return [];
  149. }
  150. $pageSize = intval($pageSize);
  151. if ($pageSize > 0) { // pageSize 为0时表示取所有数据
  152. $sql .= ' LIMIT ' . $pageSize;
  153. if ($page > 0) {
  154. $start_limit = ($page - 1) * $pageSize;
  155. $sql .= ' OFFSET ' . $start_limit;
  156. }
  157. }
  158. return $this->query($sql, $data);
  159. }
  161. /**
  162. * This is used for adding, deleting and modifying operations using transaction operations
  163. * @param string $sql The sql statement to execute the query
  164. * @param array $data The conditional format of the query is [':id'=>$id,' :name'=>$name] (recommended) or [1=>$id,2=>$name]
  165. * @param bool $Transaction Whether the transaction operation defaults to No
  166. */
  167. public function executeDDL($sql, $data = [],$Transaction = false){
  168. if (!is_array($data) || !is_string($sql))
  169. return false;
  171. $this->free();
  173. if($Transaction)
  174. $this->pdo->beginTransaction();//开启事务
  175. try{
  176. $this->execRes($data,$sql);
  177. if($Transaction)
  178. $this->pdo->commit();//事务提交
  179. return $this->lastInsID;
  180. } catch (Exception $e) {
  181. if($Transaction)
  182. $this->pdo->rollBack();//事务回滚
  183. throw new Exception('Error DDLExecute <=====>'.$e->getMessage());
  184. return false;
  185. }
  186. }
  188. /**
  189. * This is used to perform add, delete and modify operations using transaction operations
  190. * It executes multiple items
  191. * @param array $arr_sql The array of SQL statements that need to be executed
  192. * @param array $arr_data The conditions of the SQL statement corresponding to the array
  193. * @param bool $Transaction Whether the transaction operation defaults to No
  194. */
  195. public function executeDDLes($arr_sql, $arr_data = [],$Transaction = false){
  197. if(!is_array($arr_sql) || empty($arr_sql) || !is_array($arr_data))
  198. return false;
  200. $res = [];
  202. $this->free();
  204. if($Transaction)
  205. $this->pdo->beginTransaction();//开启事务
  206. try{
  207. $i = 0;
  208. foreach($arr_sql as $val){
  209. if(!isset($arr_data[$i]))
  210. $arr_data[$i] = [];
  211. elseif(!is_array($arr_data[$i])){
  212. if($Transaction)
  213. $this->pdo->rollBack();//事务回滚
  214. throw new Exception('Error where DDLExecutees sql:'.$val.' where:'.$arr_data[$i]);
  215. }
  217. $this->execRes($arr_data[$i],$val);
  218. $res[] = $this->lastInsID;
  219. $i++;
  220. }
  222. if($Transaction)
  223. $this->pdo->commit();//事务提交
  225. return $res;
  226. } catch (Exception $e) {
  227. if($Transaction)
  228. $this->pdo->rollBack();//事务回滚
  229. throw new Exception('Error DDLExecutees array_sql:'.json_encode($arr_sql).' <=====>'.$e->getMessage());
  230. return false;
  231. }
  232. return $res;
  233. }
  235. /**
  236. * This method is used to calculate the number of items returned by the query. Note that it only supports SELECT COUNT(*) FROM TABLE... or SELECT COUNT(0) FROM TABLE...
  237. * @param string $sql The sql statement of the query
  238. * @param array $data Condition of SQL statement
  239. */
  240. public function countRows($sql,$data = []){
  241. if (!is_array($data) || empty($sql) || !is_string($sql))
  242. return false;
  243. $this->free();
  245. $res = $this->pdoExec($data,$sql);
  247. if($res == false)
  248. return false;
  250. return $this->statement->fetchColumn();
  251. }
  253. /**
  254. * This method is used to calculate the number of items returned by the query. It is used to execute multiple SQL statements.
  255. * @param string $sql The sql statement of the query
  256. * @param array $data The conditions of the SQL statement
  257. */
  258. public function countRowses($arr_sql,$arr_data = []){
  260. if(!is_array($arr_sql) || empty($arr_sql) || !is_array($arr_data))
  261. return false;
  263. $res = [];
  265. $this->free();
  266. $i = 0;
  267. foreach ($arr_sql as $val) {
  268. if(!isset($arr_data[$i]))
  269. $arr_data[$i] = [];
  270. elseif(!is_array($arr_data[$i]))
  271. throw new Exception('Error where CountRowses sql:'.$val.' where:'.$arr_data[$i]);
  273. $res1 = $this->pdoExec($arr_data[$i],$val);
  275. if($res1 == false)
  276. $res[] = false;
  277. else
  278. $res[] = $this->statement->fetchColumn();
  279. }
  281. return $res;
  282. }
  284. /**
  285. * Here is another method because there will be many needs in the project to open transactions and then perform operations and finally submit
  286. * @param bool $Transaction Whether to perform transaction operations, the default is no
  287. */
  288. public function getDB($Transaction=false)
  289. {
  290. $this->Transaction = $Transaction;
  291. $this->getConnect();
  292. if($Transaction === true)
  293. $this->pdo->beginTransaction();//开启事务
  294. return $this;
  295. }
  297. /**
  298. * This method can be executed multiple times. It is used to execute DDL statements.
  299. * Note that it needs to be used together with getDB and sQCommit and cannot be used alone.
  300. * If the transaction is not enabled, the sQCommit method does not need to be called.
  301. * @param string $sql query sql statement
  302. * @param array $data Conditions of SQL statement
  303. */
  304. public function execSq($sql,$data = [])
  305. {
  306. if($this->checkParams($sql,$data) === false)
  307. return false;
  309. try{
  310. $this->execRes($data,$sql);
  311. return $this->lastInsID;
  312. } catch (Exception $e) {
  313. if(isset($this->Transaction) && $this->Transaction === true)
  314. $this->pdo->rollBack();//事务回滚
  315. throw new Exception('Error execSq<=====>'.$e->getMessage());
  316. return false;
  317. } finally {
  318. if (!empty($this->statement))
  319. {
  320. $this->statement->closeCursor();
  321. unset($this->statement);
  322. }
  323. }
  324. }
  326. /**
  327. * The method of executing the query requires passing a connection database object
  328. * @param string $sql The sql statement to execute the query
  329. * @param array $data The conditional format of the query is [':id'=>$id,': name'=>$name] (recommended) or [1=>$id,2=>$name]
  330. * @param bool $one Whether to return a piece of content, the default is no
  331. */
  332. public function querySq($sql,$data = [],$one = false)
  333. {
  334. if($this->checkParams($sql,$data) === false)
  335. return false;
  337. return $this->pdoExecSq($sql,$data,[1,$one]);
  338. }
  340. /**
  341. * Paging encapsulation
  342. *
  343. * @param string $sql
  344. * @param int $page indicates which page to start from
  345. * @param int $pageSize indicates how many items per page
  346. * @param array $data query conditions
  347. */
  348. public function limitQuerySq($sql, $page=0, $pageSize=20, $data = [])
  349. {
  350. $page = intval($page);
  351. if ($page < 0) {
  352. return [];
  353. }
  354. $pageSize = intval($pageSize);
  355. if ($pageSize > 0) { // pageSize 为0时表示取所有数据
  356. $sql .= ' LIMIT ' . $pageSize;
  357. if ($page > 0) {
  358. $start_limit = ($page - 1) * $pageSize;
  359. $sql .= ' OFFSET ' . $start_limit;
  360. }
  361. }
  362. return $this->querySq($sql, $data);
  363. }
  365. /**
  366. * This method is used to calculate the number of items returned by the query. Note that it only supports SELECT COUNT(*) FROM TABLE... or SELECT COUNT(0) FROM TABLE...
  367. * @param string $sql The sql statement of the query
  368. * @param array $data Condition of SQL statement
  369. */
  370. public function countRowsSq($sql,$data = []){
  371. if($this->checkParams($sql,$data) === false)
  372. return false;
  373. return $this->pdoExecSq($sql,$data,[2]);
  374. }
  376. /**
  377. * Here is another method. This is the final commit operation. If the transaction is not started, this method does not need to be called at the end
  378. */
  379. public function sQCommit()
  380. {
  381. if(empty($this->pdo) || !is_object($this->pdo))
  382. return false;
  383. if(isset($this->Transaction) && $this->Transaction === true)
  384. $this->pdo->commit();//提交事务
  385. unset($this->pdo);
  386. }
  388. /**
  389. * Internal calling method
  390. */
  391. public function checkParams($sql,$data)
  392. {
  393. if (empty($this->pdo) || !is_object($this->pdo) || !is_array($data) || empty($sql) || !is_string($sql))
  394. return false;
  396. return true;
  397. }
  399. /**
  400. * Internal calling method
  401. */
  402. private function pdoExecSq($sql,$data,$select = []){
  403. try{
  404. $res = $this->pdoExec($data,$sql);
  405. if(empty($select))
  406. return $res;
  407. else{
  408. if($select[0] === 1){
  409. if($select[1] === true)
  410. return $this->statement->fetch(PDO::FETCH_ASSOC);
  411. else
  412. return $this->statement->fetchAll(PDO::FETCH_ASSOC);
  413. }elseif($select[0] === 2)
  414. return $this->statement->fetchColumn();
  415. else
  416. return false;
  417. }
  418. } catch (Exception $e) {
  419. throw new Exception($e->getMessage());
  420. return false;
  421. } finally {
  422. if (!empty($this->statement))
  423. {
  424. $this->statement->closeCursor();
  425. unset($this->statement);
  426. }
  427. }
  428. }
  430. /**
  431. * Internal calling method
  432. */
  433. private function execRes($data,$sql){
  435. $res = $this->pdoExec($data,$sql);
  437. $in_id = $this->pdo->lastInsertId();
  439. if (preg_match("/^s*(INSERTs+INTO|REPLACEs+INTO)s+/i", $sql) && !empty($in_id))
  440. $this->lastInsID = $in_id;
  441. else
  442. $this->lastInsID = $res;
  443. }
  445. /**
  446. * Internal calling method used to directly execute SQL statements
  447. */
  448. private function pdoExec($data,$sql){
  449. $this->statement = $this->pdo->prepare($sql);
  450. if (false === $this->statement)
  451. return false;
  452. if (!empty($data))
  453. {
  454. foreach ($data as $k => $v)
  455. {
  456. $this->statement->bindValue($k, $v);
  457. }
  458. }
  459. $res = $this->statement->execute();
  460. if (!$res)
  461. {
  462. throw new Exception('sql:'.$sql.'<====>where:'.json_encode($data).'<====>error:'.json_encode($this->statement->errorInfo()));
  463. }else{
  464. return $res;
  465. }
  466. }
  468. /**
  469. * Internal calling method used to release
  470. */
  471. private function free()
  472. {
  473. if (is_null($this->pdo))
  474. $this->getConnect();
  476. if (!empty($this->statement))
  477. {
  478. $this->statement->closeCursor();
  479. $this->statement = null;
  480. }
  481. }
  482. }
  483. ?>
复制代码

PHP, PDO, MYSQL


Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：PHP unlimited classification [enhanced version]Next article：PHP unlimited classification [enhanced version]

Related articles

See more