Home > Developer > PHP

PHPPDO for MYSQL encapsulation

Source: Internet
Author: User
Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞
PHPPDO for MYSQL encapsulation

  1. /**
  2. * Auther soulence
  3. * Call a data 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: Signature => PDO: FETCH_ASSOC );
  38. If ($ persistent === true ){
  39. $ Options_arr [PDO: ATTR_PERSISTENT] = true;
  40. }
  42. Try {
  43. $ Pdo = new PDO ($ mysql_server [$ flag] ['ononstring'], $ mysql_server [$ flag] ['username'], $ mysql_server [$ flag] ['password'], $ options_arr );
  44. } Catch (PDOException $ e ){
  45. Throw new Exception ($ e-> getMessage ());
  46. // Exit ('connection failed: '. $ 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 $ Who is the database corresponding to dbname?
  61. * @ Param bool $ whether the attr is a persistent connection
  62. * Return false indicates that the specified 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 operations
  82. * @ Param string $ SQL statement used to execute the query
  83. * @ Param array $ the condition format of data query is [': ID' => $ id,': name' => $ name] (recommended) or [1 => $ id, 2 => $ name]
  84. * @ Param bool $ one: whether to return a message. the default value 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. * Shared 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 operations for multiple SQL statements
  112. * @ Param array $ arr_ SQL the array format of the SQL statement for query execution is [$ sql1, $ sql2]
  113. * @ Param array $ arr_data the condition format corresponding to $ 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; default value: No. if it is set to true, only one data record is returned for each SQL statement.
  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 the number of pages
  141. * @ Param int $ pageSize indicates the number of entries per page.
  142. * @ Param array $ conditions for data query
  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) {// if pageSize is 0, all data is retrieved.
  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 operation is used to add, delete, modify, and use transaction operations.
  163. * @ Param string $ SQL statement used to execute the query
  164. * @ Param array $ the condition format of data query is [': ID' => $ id,': name' => $ name] (recommended) or [1 => $ id, 2 => $ name]
  165. * @ Param bool $ 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 (); // start the transaction
  175. Try {
  176. $ This-> execRes ($ data, $ SQL );
  177. If ($ Transaction)
  178. $ This-> pdo-> commit (); // transaction commit
  179. Return $ this-> lastInsID;
  180. } Catch (Exception $ e ){
  181. If ($ Transaction)
  182. $ This-> pdo-> rollBack (); // transaction rollBack
  183. Throw new Exception ('error DDLExecute <===> '. $ e-> getMessage ());
  184. Return false;
  185. }
  186. }
  188. /**
  189. * This operation is used to add, delete, modify, and use transaction operations.
  190. * It executes multiple entries.
  191. * @ Param array $ array of SQL statements to be operated by arr_ SQL
  192. * @ Param array $ conditions of the SQL statement corresponding to the array and arr_data
  193. * @ Param bool $ 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 (); // start the transaction
  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 (); // transaction 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 (); // transaction commit
  225. Return $ res;
  226. } Catch (Exception $ e ){
  227. If ($ Transaction)
  228. $ This-> pdo-> rollBack (); // transaction 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 the select count (*) from table... or select count (0) from table... method.
  237. * @ Param string $ SQL query SQL statement
  238. * @ Param array $ conditions of data SQL statements
  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 a query. it executes multiple SQL statements.
  255. * @ Param string $ SQL query SQL statement
  256. * @ Param array $ conditions of data SQL statements
  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 we provide another method. because there are many projects that need to be provided to start transactions and then perform the final commit operation.
  286. * @ Param bool $ whether the Transaction operation defaults to no
  287. */
  288. Public function getDB ($ Transaction = false)
  289. {
  290. $ This-> Transaction = $ Transaction;
  291. $ This-> getConnect ();
  292. If ($ Transaction = true)
  293. $ This-> pdo-> beginTransaction (); // start the transaction
  294. Return $ this;
  295. }
  297. /**
  298. * This method can be executed multiple times. it executes DDL statements.
  299. * Note that it must be used together with getDB and sQCommit and cannot be used separately.
  300. * If the transaction sQCommit method is not enabled, you can not call it.
  301. * @ Param string $ SQL query SQL statement
  302. * @ Param array $ conditions of data SQL statements
  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 (); // transaction 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. * To execute the query method, you need to upload a database connection object.
  328. * @ Param string $ SQL statement used to execute the query
  329. * @ Param array $ the condition format of data query is [': ID' => $ id,': name' => $ name] (recommended) or [1 => $ id, 2 => $ name]
  330. * @ Param bool $ one: whether to return a message. the default value 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 the number of pages
  345. * @ Param int $ pageSize indicates the number of entries per page.
  346. * @ Param array $ conditions for data query
  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) {// if pageSize is 0, all data is retrieved.
  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 the select count (*) from table... or select count (0) from table... method.
  367. * @ Param string $ SQL query SQL statement
  368. * @ Param array $ conditions of data SQL statements
  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 we provide another method. this is the final commit operation. if the transaction is not enabled, this method can not 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 (); // submit the transaction
  385. Unset ($ this-> pdo );
  386. }
  388. /**
  389. * Internal call 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 call 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 call 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 * (INSERT \ s + INTO | REPLACE \ s + INTO) \ s +/I", $ SQL )&&! Empty ($ in_id ))
  440. $ This-> lastInsID = $ in_id;
  441. Else
  442. $ This-> lastInsID = $ res;
  443. }
  445. /**
  446. * The internal call method is 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 call methods are 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

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

Related Keywords:
encapsulation in net what encapsulation in net encapsulation in php php encapsulation example small item net encapsulation polymorphism vs encapsulation encapsulation in net with example

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

What's Trending
Top 10 Tags
datastax versions naming convention zookeeper client class definition md5 microsoft sql server 2005 data structures exception handling error handling
Top 10 Keywords
microsoft download center down wordpress address url site address url wordpress address url windows installer 4 0 download 302 not found web address url definition site address url wordpress db2 integer mac os installation step by step pdf abbreviation for return

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

  • Sales Support

    1 on 1 presale consultation

    Chat Contact Sales

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    Open a Ticket

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

    Learn More