PHPPDO for MYSQL encapsulation-PHP source code

PHPPDO for MYSQL encapsulation php code

 Dbname = $ dbname; $ this-> persistent = $ attr;} public static function db ($ flag = 'R', $ persistent = false) {if (! Isset ($ flag) {$ flag = 'R';} if (! Class_exists ('pdo') {throw new Exception ('not found pdo'); return false;} $ mysql_server = Yaf_Registry: get ('mysql'); if (! Isset ($ mysql_server [$ flag]) {return false;} $ options_arr = array (PDO: MYSQL_ATTR_INIT_COMMAND => 'set names '. $ mysql_server [$ flag] ['charset'], PDO: ATTR_DEFAULT_FETCH_MODE => PDO: FETCH_ASSOC); if ($ persistent = true) {$ options_arr [PDO :: ATTR_PERSISTENT] = true;} try {$ pdo = new PDO ($ mysql_server [$ flag] ['ononstring'], $ mysql_server [$ flag] ['username'], $ mysql_server [$ flag] ['password'], $ options _ Arr);} catch (PDOException $ e) {throw new Exception ($ e-> getMessage (); // exit ('connection failed :'. $ e-> getMessage (); return false;} if (! $ Pdo) {throw new Exception ('pdo CONNECT error'); return false;} return $ PDO ;} /*** get the database object for Operation * @ param string $ dbname who corresponds to the database * @ param bool $ attr persistent connection * return false indicates that the given database does not exist */public static function getInstance ($ dbname = 'R ', $ attr = false) {$ mysql_server = Yaf_Registry: get ('mysql'); if (! Isset ($ mysql_server [$ dbname]) {return false;} $ key = md5 (md5 ($ dbname. $ attr, true); if (! Isset (self ::$ _ instance [$ key]) |! Is_object (self ::$ _ instance [$ key]) self ::$ _ instance [$ key] = new self ($ dbname, $ attr); return self :: $ _ instance [$ key];} private function getConnect () {$ this-> pdo = self: db ($ this-> dbname, $ this-> persistent );} /*** query operation ** @ param string $ SQL statement for executing the query * @ param array $ the condition format of the data query is [': ID' => $ id ,': name' => $ name] (recommended) or [1 => $ id, 2 => $ name] * @ param bool $ one: whether to return a message. the default value is no */public function query ($ SQL, $ data = [], $ One = false) {if (! Is_array ($ data) | empty ($ SQL) |! Is_string ($ SQL) return false; $ this-> free (); return $ this-> queryCommon ($ data, $ SQL, $ one );} /*** internal query sharing method */private function queryCommon ($ data, $ SQL, $ one) {$ this-> pdoExec ($ data, $ SQL ); if ($ one) {return $ this-> statement-> fetch (PDO: FETCH_ASSOC);} else {return $ this-> statement-> fetchAll (PDO :: FETCH_ASSOC) ;}}/*** query operations for multiple SQL statements * @ param array $ arr_ SQL the array format of the SQL statements for query execution is [$ sql1, $ sql2] * @ param array $ The condition format for arr_data query and $ arr_ SQL is [[': ID' => $ id,': name' => $ name], [': ID' => $ id, ': name' => $ name] (recommended) or [[1 => $ id, 2 => $ name], [1 => $ id, 2 => $ name] * @ param bool $ one: whether to return a piece of content. default value: No. If this parameter is set to true, only one data entry is returned for each SQL statement */public function queryes ($ arr_ SQL, $ arr_data = [], $ one = false) {if (! Is_array ($ arr_ SQL) | empty ($ arr_ SQL) |! Is_array ($ arr_data) return false; $ this-> free (); $ res = []; $ I = 0; foreach ($ arr_ SQL as $ val) {if (! Isset ($ arr_data [$ I]) $ arr_data [$ I] = []; elseif (! Is_array ($ arr_data [$ I]) throw new Exception ('error where queryes SQL :'. $ val. 'Where :'. $ arr_data [$ I]); $ res [] = $ this-> queryCommon ($ arr_data [$ I], $ val, $ one); $ I ++ ;} return $ res ;} /*** paging encapsulation ** @ param string $ SQL * @ param int $ page indicates the number of pages starting with * @ param int $ pageSize indicates the number of entries on each page * @ param array $ data Query condition */public function limitQuery ($ SQL, $ page = 0, $ pageSize = 20, $ data = []) {$ page = intval ($ page); if ($ Page <0) {return [] ;}$ pageSize = intval ($ pageSize); if ($ pageSize> 0) {// If pageSize is 0, all data is retrieved. $ SQL. = 'limit '. $ pageSize; if ($ page> 0) {$ start_limit = ($ page-1) * $ pageSize; $ SQL. = 'Offset '. $ start_limit; }}return $ this-> query ($ SQL, $ data );} /*** this operation is used to add, delete, modify, and use transaction operations * @ param string $ SQL to execute the query SQL statement * @ param array $ data the query condition format is [': ID' => $ id, ': name' => $ name] (recommended) or [1 => $ id, 2 => $ Name] * @ param bool $ whether Transaction operations are performed by default */public function executeDDL ($ SQL, $ data = [], $ Transaction = false) {if (! Is_array ($ data) |! Is_string ($ SQL) return false; $ this-> free (); if ($ Transaction) $ this-> pdo-> beginTransaction (); // enable the Transaction try {$ this-> execRes ($ data, $ SQL); if ($ Transaction) $ this-> pdo-> commit (); // Transaction commit return $ this-> lastInsID;} catch (Exception $ e) {if ($ Transaction) $ this-> pdo-> rollBack (); // transaction rollback throw new Exception ('error DDLExecute <====> '. $ e-> getMessage (); return false ;}/ *** this operation is used to add, delete, modify, and use transaction operations * it executes multiple * @ Param array $ array of SQL statements for which arr_ SQL requires Operations * @ param array $ conditions of the SQL statement corresponding to the array and arr_data * @ param bool $ whether the Transaction operation is no by default */ public function executeDDLes ($ arr_ SQL, $ arr_data = [], $ Transaction = false) {if (! Is_array ($ arr_ SQL) | empty ($ arr_ SQL) |! Is_array ($ arr_data) return false; $ res = []; $ this-> free (); if ($ Transaction) $ this-> pdo-> beginTransaction (); // enable the transaction try {$ I = 0; foreach ($ arr_ SQL as $ val) {if (! Isset ($ arr_data [$ I]) $ arr_data [$ I] = []; elseif (! Is_array ($ arr_data [$ I]) {if ($ Transaction) $ this-> pdo-> rollBack (); // transaction rollback throw new Exception ('error where DDLExecutees SQL :'. $ val. 'Where :'. $ arr_data [$ I]);} $ this-> execRes ($ arr_data [$ I], $ val); $ res [] = $ this-> lastInsID; $ I ++;} if ($ Transaction) $ this-> pdo-> commit (); // Transaction commit return $ res;} catch (Exception $ e) {if ($ Transaction) $ this-> pdo-> rollBack (); // Transaction rollBack throw new Exception ('error DDLExecutees ar Ray_ SQL :'. json_encode ($ arr_ SQL ). '<=> '. $ e-> getMessage (); return false;} return $ res;}/*** this method is used to calculate the number of returned results. Note that it only supports select count (*) from table... or select count (0) from table... METHOD * @ param string $ SQL query SQL statement * @ param array $ data SQL statement condition */public function countRows ($ SQL, $ data = []) {if (! Is_array ($ data) | empty ($ SQL) |! Is_string ($ SQL) return false; $ this-> free (); $ res = $ this-> pdoExec ($ data, $ SQL ); if ($ res = false) return false; return $ this-> statement-> fetchColumn ();} /*** this method is used to calculate the number of items returned by the query. it executes multiple SQL statements * @ param string $ SQL query SQL statement * @ param array $ data SQL statement condition */ public function countRowses ($ arr_ SQL, $ arr_data = []) {if (! Is_array ($ arr_ SQL) | empty ($ arr_ SQL) |! Is_array ($ arr_data) return false; $ res = []; $ this-> free (); $ I = 0; foreach ($ arr_ SQL as $ val) {if (! Isset ($ arr_data [$ I]) $ arr_data [$ I] = []; elseif (! Is_array ($ arr_data [$ I]) throw new Exception ('error where CountRowses SQL :'. $ val. 'Where :'. $ arr_data [$ I]); $ res1 = $ this-> pdoExec ($ arr_data [$ I], $ val); if ($ res1 = false) $ res [] = false; else $ res [] = $ this-> statement-> fetchColumn ();} return $ res ;} /*** here we provide another method. because there are many projects that need to enable transactions and then perform the operation and finally commit. * @ param bool $ Transaction indicates whether the Transaction operation defaults to no */public function. getDB ($ Transaction = false) {$ this-> Transaction = $ Transaction; $ this-> getConnect (); if ($ Transaction = true) $ this-> pdo-> beginTransaction (); // enable the transaction return $ this ;} /*** this method can be executed multiple times. it is used to execute DDL statements. * Note that it must be used together with getDB and sQCommit. it cannot be used independently. * If the sQCommit method is not enabled, do not call the * @ param string $ SQL query SQL statement * @ param array $ conditions of the data SQL statement */public function execSq ($ SQL, $ data = []) {if ($ this-> checkParams ($ SQL, $ data) === false) return false; try {$ this-> execRes ($ data, $ SQL); return $ this-> lastInsID;} catch (Exception $ e) {if (isset ($ this-> Transaction) & $ this-> Transaction === true) $ this-> pdo-> rollBack (); // transaction rollback throw new Exception ('error execSq <===> '. $ e-> getMessage (); return false;} finally {if (! Empty ($ this-> statement) {$ this-> statement-> closeCursor (); unset ($ this-> statement );}}} /*** the query execution method requires that a database object be connected * @ param string $ SQL statement used to execute the query * @ param array $ data the query condition format is [': ID' => $ id, ': name' => $ name] (recommended) or [1 => $ id, 2 => $ name] * @ param bool $ one: whether to return a message. the default value is no */public function querySq ($ SQL, $ data = [], $ one = false) {if ($ this-> checkParams ($ SQL, $ data) === false) return false; return $ this-> pdo ExecSq ($ SQL, $ data, [1, $ one]);} /*** paging encapsulation ** @ param string $ SQL * @ param int $ page indicates the number of pages starting with * @ param int $ pageSize indicates the number of entries on each page * @ param array $ data Query condition */public function limitQuerySq ($ SQL, $ page = 0, $ pageSize = 20, $ data = []) {$ page = intval ($ page); if ($ page <0) {return [];} $ pageSize = intval ($ pageSize); if ($ pageSize> 0) {// when pageSize is 0, all data is retrieved. $ SQL. = 'limit '. $ pageSize; if ($ page> 0) {$ Start_limit = ($ page-1) * $ pageSize; $ SQL. = 'Offset '. $ start_limit; }}return $ this-> querySq ($ SQL, $ data );} /*** 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... METHOD * @ param string $ SQL query SQL statement * @ param array $ data SQL statement condition */public function countRowsSq ($ SQL, $ data = []) {if ($ this-> checkParams ($ SQL, $ data) === false) return false; return $ this-> pdoExe CSq ($ SQL, $ data, [2]);} /*** here we provide another method. this is the final commit operation. if transaction is not enabled, this method can not be called at the end */public function sQCommit () {if (empty ($ this-> pdo) |! Is_object ($ this-> pdo) return false; if (isset ($ this-> Transaction) & $ this-> Transaction === true) $ this-> pdo-> commit (); // submit the transaction unset ($ this-> pdo );} /*** internal call method */public function checkParams ($ SQL, $ data) {if (empty ($ this-> pdo) |! Is_object ($ this-> pdo) |! Is_array ($ data) | empty ($ SQL) |! Is_string ($ SQL) return false; return true;}/*** internal call method */private function pdoExecSq ($ SQL, $ data, $ select = []) {try {$ res = $ this-> pdoExec ($ data, $ SQL); if (empty ($ select) return $ res; else {if ($ select [0] === 1) {if ($ select [1] === true) return $ this-> statement-> fetch (PDO :: FETCH_ASSOC); else return $ this-> statement-> fetchAll (PDO: FETCH_ASSOC);} elseif ($ select [0] = 2) return $ this-> statement-> fet ChColumn (); else return false ;}} catch (Exception $ e) {throw new Exception ($ e-> getMessage (); return false ;}finally {if (! Empty ($ this-> statement) {$ this-> statement-> closeCursor (); unset ($ this-> statement );}}} /*** internal call method */private function execRes ($ data, $ SQL) {$ res = $ this-> pdoExec ($ data, $ SQL ); $ in_id = $ this-> pdo-> lastInsertId (); if (preg_match ("/^ \ s * (INSERT \ s + INTO | REPLACE \ s +) \ s +/I ", $ SQL )&&! Empty ($ in_id) $ this-> lastInsID = $ in_id; else $ this-> lastInsID = $ res ;} /*** internal call method used to directly execute SQL statements */private function pdoExec ($ data, $ SQL) {$ this-> statement = $ this-> pdo-> prepare ($ SQL); if (false = $ this-> statement) return false; if (! Empty ($ data) {foreach ($ data as $ k = >$ v) {$ this-> statement-> bindValue ($ k, $ v );}} $ res = $ this-> statement-> execute (); if (! $ Res) {throw new Exception ('SQL :'. $ SQL. '<=> where :'. json_encode ($ data ). '<===> error :'. json_encode ($ this-> statement-> errorInfo ();} else {return $ res ;}} /*** internal call method used to release */private function free () {if (is_null ($ this-> pdo) $ this-> getConnect (); if (! Empty ($ this-> statement) {$ this-> statement-> closeCursor (); $ this-> statement = null ;}}?>