Php-mysql encapsulation class-PHP source code

Php-mysql encapsulation class

 _ Host = $ conf ['host']; $ this-> _ port = $ conf ['port']; $ this-> _ user = $ conf ['user']; $ this-> _ pass = $ conf ['passwd']; $ this-> _ dbName = $ conf ['dbname']; // connect to the database if (is_null (self: $ _ dbh )) {$ this-> _ connect () ;}/ *** method for connecting to the database */protected function _ connect () {$ dsn = $ this-> _ dbType. ': host = '. $ this-> _ host. '; port = '. $ this-> _ port. '; dbname = '. $ this-> _ dbName; $ options = $ this-> _ pconnect? Array (PDO: ATTR_PERSISTENT => true): array (); try {$ dbh = new PDO ($ dsn, $ this-> _ user, $ this-> _ pass, $ options); $ dbh-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION); // sets an Exception thrown if an SQL statement execution error occurs, the transaction will automatically roll back $ dbh-> setAttribute (PDO: ATTR_EMULATE_PREPARES, false); // disable the simulation effect of prepared statements (anti-SQL injection)} catch (PDOException $ e) {die ('connection failed :'. $ e-> getMessage ();} $ dbh-> exec ('set NAMES utf8'); sel F: $ _ dbh = $ dbh ;} /*** add the 'symbol * to the field and table name to ensure that no keyword is used in the command. for mysql * @ param string $ value * @ return string */protected function _ addChar ($ value) {if ('*' = $ value | false! = Strpos ($ value, '(') | false! = Strpos ($ value, '.') | false! = Strpos ($ value, ''') {// if it contains * or uses an SQL method, it is not processed} elseif (false = strpos ($ value, ''') {$ value = '''. trim ($ value ). ''';} return $ value ;} /*** obtain the field information of the data table * @ param string $ tbName table name * @ return array */protected function _ tbFields ($ tbName) {$ SQL = 'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = "'. $ tbName. '"AND TABLE_SCHEMA = "'. $ this-> _ dbName. '"'; $ stmt = self: $ _ dbh-> prep Are ($ SQL); $ stmt-> execute (); $ result = $ stmt-> fetchAll (PDO: FETCH_ASSOC); $ ret = array (); foreach ($ result as $ key => $ value) {$ ret [$ value ['column _ name'] = 1;} return $ ret ;} /*** filter and format the data table field * @ param string $ tbName data table name * @ param array $ data POST submit data * @ return array $ newdata */protected function _ dataFormat ($ tbName, $ data) {if (! Is_array ($ data) return array (); $ table_column = $ this-> _ tbFields ($ tbName); $ ret = array (); foreach ($ data as $ key => $ val) {if (! Is_scalar ($ val) continue; // if the value is not a scalar, skip if (array_key_exists ($ key, $ table_column )) {$ key = $ this-> _ addChar ($ key); if (is_int ($ val) {$ val = intval ($ val );} elseif (is_float ($ val) {$ val = floatval ($ val );} elseif (preg_match ('/^ \ (\ w * (\ + | \-| \ * | \/)? \ W * \) $/I ', $ val) {// Other fields can be directly used in the field value, for example (score + 1) (name) must contain brackets $ val = $ val;} elseif (is_string ($ val) {$ val = '"'. addslashes ($ val ). '"';} $ ret [$ key] = $ val;} return $ ret;}/*** query execution is mainly for SELECT, SHOW and other commands * @ param string $ SQL command * @ return mixed */protected function _ doQuery ($ SQL = '') {$ this-> _ SQL = $ SQL; $ pdostmt = self: $ _ dbh-> prepare ($ this-> _ SQL); // prepare or query returns a PDOStatement $ Pdostmt-> execute (); $ result = $ pdostmt-> fetchAll (PDO: FETCH_ASSOC); return $ result;}/*** execution statement for INSERT, UPDATE and DELETE, exec results return the affected number of rows * @ param string $ SQL command * @ return integer */protected function _ doExec ($ SQL = '') {$ this-> _ SQL = $ SQL; return self: $ _ dbh-> exec ($ this-> _ SQL);}/*** execute an SQL statement, automatic Query or operation * @ param string $ SQL SQL command * @ return mixed */public function doSql ($ SQL = '') {$ que RyIps = 'Insert | UPDATE | DELETE | REPLACE | CREATE | DROP | load data | SELECT. * INTO | COPY | ALTER | GRANT | REVOKE | LOCK | unlock'; if (preg_match ('/^ \ s *"? ('. $ QueryIps. ') \ s +/I', $ SQL) {return $ this-> _ doExec ($ SQL );} else {// query operation return $ this-> _ doQuery ($ SQL );}} /*** obtain the last query SQL statement * @ return String executed SQL */public function getLastSql () {return $ this-> _ SQL ;} /*** insert method ** @ param string $ tbName name of the data table for the operation * @ param array $ data field-one-dimensional array of values * @ return int number of affected rows */public function insert ($ tbName, array $ data) {$ data = $ this-> _ dataFormat ($ tbName, $ data ); If (! $ Data) return; $ SQL = "insert ". $ tbName. "(". implode (',', array_keys ($ data )). ") values (". implode (',', array_values ($ data )). ")"; return $ this-> _ doExec ($ SQL );} /*** delete method ** @ param string $ tbName name of the data table operated * @ return int number of affected rows */public function delete ($ tbName) {// security considerations, prevent the entire table from deleting if (! Trim ($ this-> _ where) return false; $ SQL = "delete from ". $ tbName. "". $ this-> _ where; $ this-> _ clear = 1; $ this-> _ clear (); return $ this-> _ doExec ($ SQL );} /***** update function * @ param string $ tbName name of the data table for the operation * @ param array $ data parameter array * @ return int number of affected rows */public function update ($ tbName, array $ data) {// security considerations, prevent full table update if (! Trim ($ this-> _ where) return false; $ data = $ this-> _ dataFormat ($ tbName, $ data); if (! $ Data) return; $ valArr = ''; foreach ($ data as $ k => $ v) {$ valArr [] = $ k. '= '. $ v ;}$ valStr = implode (',', $ valArr); $ SQL = "update ". trim ($ tbName ). "set ". trim ($ valStr ). "". trim ($ this-> _ where); return $ this-> _ doExec ($ SQL );} /*** query function * @ param string $ tbName name of the data table for the operation * @ return array result set */public function select ($ tbName = '') {$ SQL = "select ". trim ($ this-> _ field ). "from ". $ tbName. "". trim ($ this-> _ where ). "". Trim ($ this-> _ order ). "". trim ($ this-> _ limit); $ this-> _ clear = 1; $ this-> _ clear (); return $ this-> _ doQuery (trim ($ SQL);}/*** @ param mixed $ option combines two-dimensional arrays of conditions, for example: $ option ['field1'] = array (1, '=>', 'or') * @ return $ this */public function where ($ option) {if ($ this-> _ clear> 0) $ this-> _ clear (); $ this-> _ where = 'where'; $ logic = 'and '; if (is_string ($ option) {$ this-> _ where. = $ option;} elseif (is _ Array ($ option) {foreach ($ option as $ k => $ v) {if (is_array ($ v )) {$ relative = isset ($ v [1])? $ V [1]: '='; $ logic = isset ($ v [2])? $ V [2]: 'and'; $ condition = '('. $ this-> _ addChar ($ k ). ''. $ relative. ''. $ v [0]. ')';} else {$ logic = 'and'; $ condition = '('. $ this-> _ addChar ($ k ). '= '. $ v. ')';} $ this-> _ where. = isset ($ mark )? $ Logic. $ condition: $ condition; $ mark = 1;} return $ this;}/*** set the sorting * @ param mixed $ option: array ('sort '=> 'desc') * @ return $ this */public function order ($ option) {if ($ this-> _ clear> 0) $ this-> _ clear (); $ this-> _ order = 'Order by'; if (is_string ($ option) {$ this-> _ order. = $ option;} elseif (is_array ($ option) {foreach ($ option as $ k => $ v) {$ order = $ this-> _ addChar ($ k ). ''. $ v; $ thi S-> _ order. = isset ($ mark )? ','. $ Order: $ order; $ mark = 1 ;}return $ this ;}/ *** set the number of queried rows and pages * @ param int $ page pageSize when it is not null, otherwise, the number of rows * @ param int $ pageSize is null, and the number of retrieved rows and pages * @ return $ this */public function limit ($ page, $ pageSize = null) {if ($ this-> _ clear> 0) $ this-> _ clear (); if ($ pageSize = null) {$ this-> _ limit = "limit ". $ page;} else {$ pageval = intval ($ page-1) * $ pageSize); $ this-> _ limit = "limit ". $ pag Eval. ",". $ pageSize;} return $ this;}/*** set the query field * @ param mixed $ field array * @ return $ this */public function field ($ field) {if ($ this-> _ clear> 0) $ this-> _ clear (); if (is_string ($ field) {$ field = explode (',', $ field) ;}$ nField = array_map (array ($ this, '_ addChar'), $ field); $ this-> _ field = implode (',', $ nField); return $ this;}/*** clear markup function */protected function _ clear () {$ this-> _ where = ''; $ This-> _ order = ''; $ this-> _ limit =''; $ this-> _ field = '*'; $ this-> _ clear = 0 ;} /*** manually clear the flag * @ return $ this */public function clearKey () {$ this-> _ clear (); return $ this ;} /*** start the transaction * @ return void */public function startTrans () {// data rollback supports if ($ this-> _ trans = 0) self :: $ _ dbh-> beginTransaction (); $ this-> _ trans ++; return ;} /*** used to submit queries in non-automatic submission status * @ return boolen */public function commit () {$ Result = true; if ($ this-> _ trans> 0) {$ result = self: $ _ dbh-> commit (); $ this-> _ trans = 0;} return $ result;}/*** transaction rollback * @ return boolen */public function rollback () {$ result = true; if ($ this-> _ trans> 0) {$ result = self ::: _ dbh-> rollback (); $ this-> _ trans = 0 ;} return $ result;}/*** close the connection * PHP will automatically close the connection when the script ends. */Public function close () {if (! Is_null (self ::$ _ dbh) self ::$ _ dbh = null ;}}

2. example

 101, 'AAA' => 123456, 'BBC '=> 'aaaaaaaaaaaaa',); $ mysql-> insert ('t_ table', $ data ); // query $ res = $ mysql-> field (array ('Sid ', 'AA', 'BBC ')) -> order (array ('Sid '=> 'desc', 'AA' => 'asc')-> where (array ('Sid '=> "101 ", 'A' => array ('20140901', '>', 'or')-> limit (123455)-> select ('t_ table '); $ res = $ mysql-> field ('Sid, aa, bbc ')-> order ('Sid desc, aa asc ') -> where ('Sid = 101 or aa> 100')-> limit (123455)-> select ('t_ table '); // Obtain the last executed SQL statement $ SQL = $ mysql-> getLastSql (); // directly execute the SQL statement $ SQL = "show tables "; $ res = $ mysql-> doSql ($ SQL); // transaction $ mysql-> startTrans (); $ mysql-> where (array ('Sid' => 102 )) -> update ('t_table', array ('a' => 666666); $ mysql-> where (array ('Sid '=> 103 )) -> update ('t_table', array ('BBC '=> 'Ha 8888 ha ')); $ mysql-> where (array ('Sid '=> 104)-> delete ('t _ table'); $ mysql-> commit ();