Mysql database processing class, continuous Update (Update:)-PHP source code

Mysql database processing class, continuous Update (Update:) a simple imitation of TP as a database processing class, it is convenient to process database statements at ordinary times
Select, PrePaer, Row_Count, Delete, Update, and Insert methods should be put at the end, Where, OrderBy, Limit, and Field methods do not require order, and ClearKey methods should be put at the beginning if they are used, I have not deleted the code for compatibility purposes of the past and it is not recommended to use

 _ DbName = $ DbName; if (class_exists ('pdo') {$ Db = new PDO ($ this-> _ SQL. ': host = '. $ this-> _ Host. '; dbname = '. $ this-> _ DbName, $ this-> _ User, $ this-> _ Pass) or die ("PDO object initialization failed "); $ Db-> exec ("set names utf8"); $ this-> Db = $ Db; // because the PDO method does not have a method similar to the mysql_real_escape_string () function, therefore, if it is a PDO connection, create a process connection mysql_connect ($ this-> _ Host. ":". $ this-> _ Port, $ this-> _ User, $ this-> _ Pass);} else {$ Db = new MySQLi ($ this -> _ Host, $ this-> _ User, $ this-> _ Pass, $ this-> _ DbName, $ this-> _ Port) or die ("MySQLi object initialization failed"); $ Db-> query ("set names utf8"); $ this-> Db = $ Db; $ this-> _ LinkClass = "SQL ";}} /*** _ set method implementation * @ param $ Key * @ param $ Val */public function _ set ($ Key, $ Val) {if (isset ($ this-> $ Key) {$ this-> $ Key = $ Val ;}} /*** insert method ** @ param string $ name of the data table for TableName operation * @ param array $ Options field-value array * @ retur N int affected rows */public function Insert ($ TableName, array $ Options) {if (! Is_array ($ Options) {$ this-> Error = True; $ this-> ErrorMsg = "condition Error: the insert statement must be passed in to insert data"; return False ;} foreach ($ Options as $ K =>$ V) {if (! Is_scalar ($ K) continue; $ KeyArr [] = ""'. trim ($ K ). "" '; $ ValArr [] = "'". $ this-> _ Real_ValString (trim ($ V )). "'";} $ Query = "Insert Into {$ TableName }(". implode (',', $ KeyArr ). ") values (". implode (',', $ ValArr ). ")"; $ this-> _ LastSQL = $ Query; // return $ Query; $ this-> _ Clear = 1; $ this-> Clear (); if ($ this-> _ LinkClass = "PDO") {return $ this-> Db-> exec (trim ($ Query ));} else {$ this-> Db-> query (trim ($ Query); return $ this-> Db-> affected_rows ;}} /*** Delete method ** @ param string $ name of the data table in the TableName operation * @ return int number of affected rows */public function Delete ($ TableName) {if (! $ This-> _ Where & $ this-> _ NoAllAction) {$ this-> Error = True; $ this-> ErrorMsg = "parameter Error: No update condition is specified, disable full table update "; return False ;}; if ($ this-> Error) {die ($ this-> ErrorMsg );} $ Query = "Delete from {$ TableName }". trim ($ this-> _ Where); $ this-> _ LastSQL = $ Query; $ this-> _ Clear = 1; $ this-> Clear (); if ($ this-> _ LinkClass = "PDO") {return $ this-> Db-> exec (trim ($ Query ));} else {$ this-> Db-> query (Trim ($ Query); return $ this-> Db-> affected_rows ;}} /*** Update function ** @ param string $ name of the data table for TableName operation * @ param array $ Options parameter array * @ return int number of affected rows */public function Update ($ TableName, array $ Options) {if (! $ This-> _ Where & $ this-> _ NoAllAction) {$ this-> Error = True; $ this-> ErrorMsg = "parameter Error: No update condition is specified, disable full table update "; return False ;}; if (! Is_array ($ Options) {$ this-> Error = True; $ this-> ErrorMsg = "condition Error: update statement must be passed in to update data"; return False ;} if ($ this-> Error) {die ($ this-> ErrorMsg);} $ SQL = "Update {$ TableName} Set "; foreach ($ Options as $ K =>$ V) {if (! Is_scalar ($ K) continue; if (strpos ($ V ,''')! = False) {$ SQL. = ""'. trim ($ K ). "'= ". $ this-> _ Real_ValString (trim ($ V )). ",";} else {$ SQL. = ""'. trim ($ K ). "'= '". $ this-> _ Real_ValString (trim ($ V )). "'," ;}}$ SQL = substr ($ SQL, 0, strlen ($ SQL)-1); $ Query = $ SQL. "". trim ($ this-> _ Where); $ this-> _ LastSQL = $ Query; $ this-> _ Clear = 1; $ this-> Clear (); if ($ this-> _ LinkClass = "PDO") {return $ this-> Db-> exec ($ Query);} else {$ this-> Db-> q Uery (trim ($ Query); return $ this-> Db-> affected_rows ;}} /*** create a query statement * @ param $ TableName * @ return string */protected function _ Create_Query ($ TableName) {return trim ("Select ". trim ($ this-> _ Field ). "from {$ TableName }". trim ($ this-> _ Where ). "". trim ($ this-> _ OrderBy ). "". trim ($ this-> _ Limit ));} /*** query function ** @ param string $ TableName name of the data table for the operation * @ return array all row arrays queried */public function Sel Ect ($ TableName) {if ($ this-> Error) {die ($ this-> ErrorMsg);} $ Query = $ this-> _ Create_Query ($ TableName ); $ this-> _ LastSQL = $ Query; // return $ Query; $ this-> _ Clear = 1; $ this-> Clear (); $ SqlObject = $ this-> Db-> query ($ Query); if ($ this-> _ LinkClass = 'pdo') {return $ SqlObject-> fetchAll (PDO:: FETCH_ASSOC);} else {return $ SqlObject-> fetch_all (MYSQL_ASSOC);}/*** count query records * @ param $ Table Name * @ return int */public function Row_Count ($ TableName) {if ($ this-> Error) {die ($ this-> ErrorMsg );} $ Query = $ this-> _ Create_Query ($ TableName); $ this-> _ LastSQL = $ Query; $ this-> _ Clear = 1; $ this-> Clear (); $ SqlObject = $ this-> Db-> query ($ Query); if ($ this-> _ LinkClass = 'pdo ') {return $ SqlObject-> rowCount ();} else {return $ SqlObject-> num_rows;}/*** PrePare statement of PDO. if the PDO class library is not enabled Return FALSE * @ param string $ TableName name of the data table to be operated * @ return object | bool PrePare query object. you need to call the related method externally */public function PrePare ($ TableName) {if ($ this-> Error) {die ($ this-> ErrorMsg);} $ this-> _ Where = str_replace ("'? '",'? ', $ This-> _ Where); $ Query = "Select ". trim ($ this-> _ Field ). "from {$ TableName }". trim ($ this-> _ Where ). "". trim ($ this-> _ OrderBy ). "". trim ($ this-> _ Limit); $ this-> _ LastSQL = $ Query; $ this-> _ Clear = 1; $ this-> Clear (); if ($ this-> _ LinkClass = 'pdo') {return $ this-> Db-> prepare (trim ($ Query ));} else {$ this-> Error = True; $ this-> ErrorMsg = "parameter Error: you have not enabled the PDO class library and cannot use the PrePare statement ";}}/*** Transaction */public function BeginTransaction () {if ($ this-> _ LinkClass = 'pdo') {$ this-> Db-> beginTransaction ();} else {$ this-> Db-> autocommit (False) ;}/ *** submit transaction */public function Commit () {if ($ this-> _ LinkClass = 'pdo') {$ this-> Db-> commit ();} else {$ this-> Db-> commit (); $ this-> Db-> autocommit (True );}} /*** RollBack transaction */public function RollBack () {if ($ this-> _ LinkClass = 'pdo '){ $ This-> Db-> rollback ();} else {$ this-> Db-> rollback (); $ this-> Db-> autocommit (True );}} /*** return the last executed SQL statement * @ return null | string */public function GetLastSQL () {return $ this-> _ LastSQL ;} /*** string escape method to prevent data from being inserted into the database * @ param string $ Val escape string * @ return string */protected function _ Real_ValString ($ Val) {if ($ this-> _ LinkClass = "PDO") {// if the connection mode is PDO, use mysql_real_escape_string () in the process method () Function return mysql_real_escape_string ($ Val);} else {return $ this-> Db-> real_escape_string ($ Val);}/*** create in, not in statement * @ param $ Field * @ param array $ Options * @ param $ Conditions1 * @ param $ Conditions2 * @ return string */protected function _ Create_In_ SQL ($ Field, array $ Options, $ Conditions1, $ Conditions2) {$ SQL = ''; $ SQL. = ""'. trim ($ Field ). "'". trim ($ Conditions1 ). "("; foreach ($ Options as $ vo) {$ SQL. = "'". trim ($ vo ). "'," ;}$ SQL = substr ($ SQL, 0, strlen ($ SQL)-1); $ SQL. = ")". trim ($ Conditions2); return $ SQL;}/*** create, not between Statement * @ param $ Field * @ param array $ Options * @ param $ Conditions1 * @ param $ Conditions2 * @ return string */protected function _ Create_Between_ SQL ($ Field, array $ Options, $ Conditions1, $ Conditions2) {return ""'. trim ($ Field ). "'". trim ($ Conditions1 ). "". trim ($ Options [0]). "and ". trim ($ Options [1]). "". $ Conditions2;}/*** supported conditions: =,>, <, >=, <=, in, not in, between, not between, like, not like, multi * supports combination: and or * example: $ Options ['field1'] = array ('test', '=', 'and'); * comment: if the second field in the two-dimensional array is in, not in, between, not between, the first field must be an array, as shown in * Example 2: $ Options ['field1'] = array (2, 4), 'beten', 'and'); * Note: to operate on multiple fields with the same name, the second field must be multi, and the first field must be an array, as shown in * Example 3: $ Options ['field1'] = array (Array ('10', '<', 'or'), array ('30', '>', 'and'), 'multi ', 'and'); * @ param array | string $ Option combination condition array * @ return $ this */public function Where ($ Option) {if ($ this-> _ Clear> 0) $ this-> Clear (); if (is_string ($ Option )) {$ this-> _ Where = trim ($ Option);} elseif (is_array ($ Option) {$ this-> _ Where = 'where '; $ ContConditions = 'and'; // The array of starting cyclic conditions foreach ($ Option as $ K => $ V) {if (is_scalar ($ V )) {// if the value is not an array Directly use it as the value of the query condition. the default values of other conditions are =, and $ this-> _ Where. = ""'. $ K. "'= '". $ V. "'and";} else {if (! Empty ($ V [1]) & strtolower ($ V [1] = 'multi ') {// process multi-condition query if (! Is_array ($ V [0]) {$ this-> Error = True; $ this-> ErrorMsg = "condition Error: The first Field of item {$ K} must be an array "; return False;} for ($ I = 0; $ I <count ($ V [0]); $ I ++) {// traverse the first condition array if (in_array (strtolower ($ V [0] [$ I] [2]), array ('in ', 'not in') {$ this-> _ Where. = $ this-> _ Create_In_ SQL ($ K, $ V [0] [$ I] [0], $ V [0] [$ I] [1], $ V [0] [$ I] [2]);} elseif (in_array (strtolower ($ V [0] [$ I] [2]), array ('between', 'not between') {$ this-> _ Where. = $ this-> _ Create_Between_ SQL ($ K, $ V [0] [$ I] [0], $ V [0] [$ I] [1], $ V [0] [$ I] [2]);} else {$ this-> _ Where. = ""'. trim ($ K ). "'". trim ($ V [0] [$ I] [1]). "'". trim ($ V [0] [$ I] [0]). "'". trim ($ V [0] [$ I] [2]);} $ ContConditions = $ V [0] [$ I] [2];} elseif (! Empty ($ V [1]) & in_array (strtolower (trim ($ V [1]), array ('in', 'not in', 'between ', 'Not between') {if (! Is_array ($ V [0]) {$ this-> Error = True; $ this-> ErrorMsg = "condition Error: The first Field of item {$ K} must be an array "; return False;} $ Conditions = isset ($ V [2])? $ V [2]: 'and'; $ ContConditions = $ Conditions; if (in_array (strtolower ($ V [1]), array ('in ', 'not in') {$ this-> _ Where. = $ this-> _ Create_In_ SQL ($ K, $ V [0], $ V [1], $ Conditions);} else {$ this-> _ Where. = $ this-> _ Create_Between_ SQL ($ K, $ V [0], $ V [1], $ Conditions );}} else {$ Conditions1 = isset ($ V [1])? $ V [1]: '='; $ Conditions2 = isset ($ V [2])? $ V [2]: 'and'; $ ContConditions = $ Conditions2; $ this-> _ Where. = ""'. $ K. "'". $ Conditions1 ."'". $ V [0]. "'". $ Conditions2 ;}}$ this-> _ Where = trim (substr ($ this-> _ Where, 0, strlen ($ this-> _ Where) -strlen ($ ContConditions); // return $ this-> _ Where;} else {$ this-> Error = True; $ this-> ErrorMsg = "condition Error: the WHERE statement only allows the use of strings or arrays "; return False;} return $ this;}/*** set the number of sorting conditions * @ param array $ Val Group example: array ('sort '=> 'desc') * @ return $ this */public function OrderBy (array $ Val) {if ($ this-> _ Clear> 0) $ this-> Clear (); $ this-> _ OrderBy = "order "; foreach ($ Val as $ K =>$ V) {$ this-> _ OrderBy. = trim ($ K ). "". trim ($ V ). "," ;}$ this-> _ OrderBy = trim (substr ($ this-> _ OrderBy, 0, strlen ($ this-> _ OrderBy)-1 )); return $ this;}/*** set the number of rows and pages to be queried * @ param $ Page $ the number of pages when PageSize is not null; otherwise, the number of rows * @ param null $ If PageSize is empty, the function sets the number of rows to be retrieved. if it is not empty, the function sets the number of rows and pages to be retrieved. * @ return $ this */public function Limit ($ Page, $ PageSize = null) {if ($ this-> _ Clear> 0) $ this-> Clear (); if ($ PageSize = null) {$ this-> _ Limit = "limit ". $ Page;} else {$ SelStart = ($ Page-1) * $ PageSize; $ this-> _ Limit = "limit ". $ SelStart. ",". $ PageSize;} return $ this;}/*** set the query Field * @ param array $ Field array * @ return $ this */public function Field (arr Ay $ Field) {if ($ this-> _ Clear> 0) $ this-> Clear (); $ this-> _ Field = ''; foreach ($ Field as $ K =>$ V) {$ this-> _ Field. = trim ($ V ). "," ;}$ this-> _ Field = trim (substr ($ this-> _ Field, 0, strlen ($ this-> _ Field)-1 )); return $ this;}/*** Clear flag function */protected function Clear () {$ this-> _ Where = ''; $ this-> _ OrderBy = ''; $ this-> _ Limit = ''; $ this-> _ Clear = 0; $ this-> _ Field = '*';}/*** manually Clear Mark * @ return $ this */public function ClearKey () {$ this-> _ Where = ''; $ this-> _ OrderBy = ''; $ this-> _ Limit = ''; $ this-> _ Clear = 0; $ this-> _ Field = '*'; return $ this ;} /*** @ param string $ Address backup type. the default value is SERVER, used to return and determine the action * @ return mixed | string to return the JSON format data of the BackUp information */public function BackUp ($ Address = 'server ') {// connect to the database $ DB = new MySqli ($ this-> _ Host, $ this-> _ User, $ this-> _ Pass, $ this-> _ DbName); $ DB-> query ("set names utf8"); // Check and create the BackUp directory and name $ FilePath = '/BackUp/'; if (! File_exists (_ WEBROOT __. $ FilePath) {mkdir (_ WEBROOT __. $ FilePath, 0777, True);} $ FileName = 'back '. date ('ymdhis '). '. SQL '; $ File = fopen (_ WEBROOT __. $ FilePath. $ FileName, 'A'); // opens the file handle in append mode // creates the header information $ SQL = Null; $ SQL. = "-- Server Type: MySql \ r \ n"; $ SQL. = "-- Create User: \ r \ n"; $ SQL. = "-- Create Time :". date ('Y-m-d H: I: s '). "\ r \ n"; fwrite ($ File, $ SQL ); // write header information $ Databases = $ DB-> query ("show tables"); // query all data tables while ($ vo = $ Databases-> fetch_array (MYSQL_NUM )) {// traverse the data Table $ SQL = Null; $ table = $ DB-> query ("show create Table ". $ vo [0]); // query the creation statement of the current Table if ($ vo = $ Table-> fetch_array (MYSQL_NUM) {$ SQL = Null; // create a table statement $ SQL. = "-- Create Table ". $ VO 2 [0]. "\ r \ n"; $ SQL. = $ VO 2 [1]. "; \ r \ n"; // create a data Statement $ SQL. = "-- Insert Table ". $ VO 2 [0]. "\ r \ n"; $ Insert = $ DB-> query ("select * from ". $ VO 2 [0]); while ($ vo3 = $ Insert-> fetch_array (MYSQL_ASSOC) {$ SQL. = "Insert ". $ VO 2 [0]. "Values ("; foreach ($ vo3 as $ Key => $ Val) {$ SQL. = "'". $ DB-> real_escape_string ($ Val ). "'," ;}$ SQL = substr ($ SQL, 0, strlen ($ SQL)-1); $ SQL. = "); \ r \ n" ;}} else {$ BackUpArr ['address'] = strtoupper ($ address); $ BackUpArr ['state'] = 1; $ BackUpArr ['MSG '] =' failed: the table statement cannot be read. please try again or contact the administrator '; $ BackUpArr ['filepath'] = ''; @ unlink (_ WEBROOT __. $ FilePath. $ FileName); return json_encode ($ BackUpArr);} $ SQL. = "\ r \ n"; fwrite ($ File, $ SQL); // Insert each database table once} fclose ($ File ); $ BackUpArr ['address'] = strtoupper ($ address); $ BackUpArr ['state'] = 200; $ BackUpArr ['MSG '] = 'backup successful '; $ BackUpArr ['filepath'] = $ filepath. $ FileName; return json_encode ($ BackUpArr );}}

Example

$ DbRes = new NewSql (); $ ConditionsTempArr ['id'] = array (1, 10), 'between '); $ ConditionsTempArr ['title'] = array ('test data', '=', 'or '); $ ConditionsTempArr ['description'] = array ('% Test Data %', 'like', 'or '); $ ConditionsTempArr ['sort '] = array (3, 5), 'in', 'OR'), array (10,'> ', 'and'), 'multi ', 'and'); $ OptionsInsertArr ['description'] = 3; $ OptionsUpdateArr ['Number'] = "'number' + 3"; $ DbRes-> Where ($ ConditionsTempArr)-> Select ('IMG '); $ DbRes-> Insert ('IMG ', $ OptionsInsertArr); $ DbRes-> Where ($ ConditionsTempArr)-> Update ('IMG', $ OptionsUpdateArr ); $ DbRes-> Where ($ ConditionsTempArr)-> Delete ('IMG ');