Encapsulate a PDO database operation code

Database PDO operations. For more information, see. The code is as follows:
/**
* Database PDO operations
*/
Class MysqlPdo {
Public static $ PDOStatement = null;
/**
* Database connection parameter configuration
* @ Var array
* @ Access public
*/
Public static $ config = array ();
/**
* Whether to use permanent connection
* @ Var bool
* @ Access public
*/
Public static $ pconnect = false;
/**
* Error message
* @ Var string
* @ Access public
*/
Public static $ error = '';
/**
* In single-piece mode, the unique instance of the Pdo class is saved and the connection resource of the database is saved.
* @ Var object
* @ Access public
*/
Protected static $ link;
/**
* Whether the database has been connected
* @ Var bool
* @ Access public
*/
Public static $ connected = false;
/**
* Database version
* @ Var string
* @ Access public
*/
Public static $ dbVersion = null;
/**
* Current SQL statement
* @ Var string
* @ Access public
*/
Public static $ queryStr = '';
/**
* Last inserted record ID
* @ Var integer
* @ Access public
*/
Public static $ lastInsertId = null;
/**
* Number of affected records returned
* @ Var integer
* @ Access public
*/
Public static $ numRows = 0;
// Transaction instruction count
Public static $ transTimes = 0;
/**
* Constructor,
* @ Param $ dbconfig database connection information, array ('servername', 'username', 'password', 'defaultdb', 'DB _ port', 'DB _ type ')
*/
Public function _ construct ($ dbConfig = ''){
If (! Class_exists ('pdo') throw_exception ("not supported: PDO ");
// Use the default data definition if no parameters are transmitted
If (! Is_array ($ dbConfig )){
$ DbConfig = array (
'Hostname' => DB_HOST,
'Username' => DB_USER,
'Password' => DB_PWD,
'Database' => DB_NAME,
'Hostport' => DB_PORT,
'Dbms '=> DB_TYPE,
'Dsn '=> DB_TYPE. ": host =". DB_HOST. "; dbname =". DB_NAME
);
}
If (empty ($ dbConfig ['hostname']) throw_exception ("no database configuration defined ");
Self: $ config = $ dbConfig;
If (empty (self: $ config ['params']) self: $ config ['params'] = array ();
/************************************ Gorgeous separation line **************************************** ***/
If (! Isset (self: $ link )){
$ Configs = self: $ config;
If (self: $ pconnect ){
$ Configs ['params'] [constant ('pdo: ATTR_PERSISTENT ')] = true;
}
Try {
Self: $ link = new PDO ($ configs ['dsn '], $ configs ['username'], $ configs ['password'], $ configs ['params']);
} Catch (PDOException $ e ){
Throw_exception ($ e-> getMessage ());
// Exit ('connection failed: '. $ e-> getMessage ());
}
If (! Self: $ link ){
Throw_exception ('pdo CONNECT error ');
Return false;
}
Self: $ link-> exec ('set names'. DB_CHARSET );
Self: $ dbVersion = self: $ link-> getAttribute (constant ("PDO: ATTR_SERVER_INFO "));
// Mark the connection successful
Self: $ connected = true;
// Cancel database connection configuration
Unset ($ configs );
}
Return self: $ link;
}
/**
* Release query results
* @ Access function
*/
Static function free (){
Self: $ PDOStatement = null;
}
/*************************************** **************************************** **************************/
/* Database operations */
/*************************************** **************************************** **************************/
/**
* Obtain all query data
* @ Access function
* @ Return array
*/
Static function getAll ($ SQL = null ){
Self: query ($ SQL );
// Return the dataset
$ Result = self ::$ PDOStatement-> fetchAll (constant ('pdo: FETCH_ASSOC '));
Return $ result;
}
/**
* Obtain a query result.
* @ Access function
* @ Param string $ SQL SQL command
* @ Param integer $ seek pointer position
* @ Return array
*/
Static function getRow ($ SQL = null ){
Self: query ($ SQL );
// Returns the array set.
$ Result = self ::$ PDOStatement-> fetch (constant ('pdo: FETCH_ASSOC '), constant ('pdo: FETCH_ORI_NEXT '));
Return $ result;
}
/**
* Execute SQL statements to automatically query or execute operations.
* @ Access function
* @ Param string $ SQL SQL command
* @ Return mixed
*/
Static function doSql ($ SQL = ''){
If (self: isMainIps ($ SQL )){
Return self: execute ($ SQL );
} Else {
Return self: getAll ($ SQL );
}
}
/**
* Query table records based on the specified ID (for single table operations only)
* @ Access function
* @ Param integer $ priId primary key ID
* @ Param string $ tables data table name
* @ Param string $ fields field name
* @ Return ArrayObject table record
*/
Static function findById ($ tabName, $ priId, $ fields = '*'){
$ SQL = 'SELECT % s FROM % s WHERE id = % d ';
Return self: getRow (sprintf ($ SQL, self: parseFields ($ fields), $ tabName, $ priId ));
}
/**
* Query records
* @ Access function
* @ Param string $ tables data table name
* @ Param mixed $ where query condition
* @ Param string $ fields field name
* @ Param string $ order sorting
* @ Param string $ limit: how many data records are Retrieved?
* @ Param string $ group
* @ Param string $ having
* @ Param boolean $ whether lock is locked
* @ Return ArrayObject
*/
Static function find ($ tables, $ where = "", $ fields = '*', $ order = null, $ limit = null, $ group = null, $ having = null) {
$ SQL = 'select'. self: parseFields ($ fields)
. 'From'. $ tables
. Self: parseWhere ($ where)
. Self: parseGroup ($ group)
. Self: parseHaving ($ having)
. Self: parseOrder ($ order)
. Self: parseLimit ($ limit );
$ DataAll = self: getAll ($ SQL );
If (count ($ dataAll) = 1) {$ rlt = $ dataAll [0];} else {$ rlt = $ dataAll ;}
Return $ rlt;
}
/**
* Insert (single) records
* @ Access function
* @ Param mixed $ data
* @ Param string $ table data table name
* @ Return false | integer
*/
Static function add ($ data, $ table ){
// Filter submitted data
$ Data = self: filterPost ($ table, $ data );
Foreach ($ data as $ key => $ val ){
If (is_array ($ val) & strtolower ($ val [0]) = 'Exp '){
$ Val = $ val [1]; // use the expression ???
} Elseif (is_scalar ($ val )){
$ Val = self: fieldFormat ($ val );
} Else {
// Remove the composite object
Continue;
}
$ Data [$ key] = $ val;
}
$ Fields = array_keys ($ data );
Array_walk ($ fields, array ($ this, 'addspecialchar '));
$ FieldsStr = implode (',', $ fields );
$ Values = array_values ($ data );
$ ValuesStr = implode (',', $ values );
$ SQL = 'insert'. $ table. '('. $ fieldsStr. ') VALUES ('. $ valuesStr .')';
Return self: execute ($ SQL );
}
/**
* Update records
* @ Access function
* @ Param mixed $ sets Data
* @ Param string $ table data table name
* @ Param string $ where update condition
* @ Param string $ limit
* @ Param string $ order
* @ Return false | integer
*/
Static function update ($ sets, $ table, $ where, $ limit = 0, $ order = ''){
$ Sets = self: filterPost ($ table, $ sets );
$ SQL = 'update '. $ table. 'set '. self: parseSets ($ sets ). self: parseWhere ($ where ). self: parseOrder ($ order ). self: parseLimit ($ limit );
Return self: execute ($ SQL );
}
/**
* Save the value of a field
* @ Access function
* @ Param string $ field name of the field to be saved
* @ Param string $ value field value
* @ Param string $ table data table
* @ Param string $ where save condition
* @ Param boolean $ asString whether the field value is a string
* @ Return void
*/
Static function setField ($ field, $ value, $ table, $ condition = "", $ asString = false ){
// If '(' is regarded as an SQL command update; otherwise, the field content is updated as a pure string.
If (false = strpos ($ value, '(') | $ asString) $ value = '"'. $ value .'"';
$ SQL = 'update'. $ table. 'set'. $ field. '='. $ value. self: parseWhere ($ condition );
Return self: execute ($ SQL );
}
/**
* Delete a record
* @ Access function
* @ Param mixed $ where is the condition Map, Array, or String.
* @ Param string $ table data table name
* @ Param string $ limit
* @ Param string $ order
* @ Return false | integer
*/
Static function remove ($ where, $ table, $ limit = '', $ order = ''){
$ SQL = 'delete from'. $ table. self: parseWhere ($ where). self: parseOrder ($ order). self: parseLimit ($ limit );
Return self: execute ($ SQL );
}
/**
+ ----------------------------------------------------------
* Modify or save data (for single table operations only)
* If there is a primary key ID, it is modified. if there is no primary key ID, it is added.
* Modification record:
+ ----------------------------------------------------------
* @ Access function
+ ----------------------------------------------------------
* @ Param $ tabName table name
* @ Param $ _ POST of the form submitted by aPost
* @ Param $ priId primary key ID
* @ Param $ aNot the field or array to be excluded
* @ Param $ aCustom a custom array, which is appended to the database and saved
* @ Param $ whether isExits already exists: true, not exist: false
+ ----------------------------------------------------------
* @ Return Boolean whether modification or saving is successful
+ ----------------------------------------------------------
*/
Static function saveOrUpdate ($ tabName, $ aPost, $ priId = "", $ aNot = "", $ aCustom = "", $ isExits = false ){
If (empty ($ tabName) |! Is_array ($ aPost) | is_int ($ aNot) return false;
If (is_string ($ aNot )&&! Empty ($ aNot) $ aNot = array ($ aNot );
If (is_array ($ aNot) & is_int (key ($ aNot) $ aPost = array_diff_key ($ aPost, array_flip ($ aNot ));
If (is_array ($ aCustom) & is_string (key ($ aCustom) $ aPost = array_merge ($ aPost, $ aCustom );
If (empty ($ priId )&&! $ IsExits) {// add
$ APost = array_filter ($ aPost, array ($ this, 'removeempty '));
Return self: add ($ aPost, $ tabName );
} Else {// modify
Return self: update ($ aPost, $ tabName, "id =". $ priId );
}
}
/**
* Obtain the SQL statement of the last query
* @ Access function
* @ Param
* @ Return String the SQL statement executed
*/
Static function getLastSql (){
$ Link = self: $ link;
If (! $ Link) return false;
Return self: $ queryStr;
}
/**
* Obtain the last inserted ID.
* @ Access function
* @ Param
* @ Return the ID of the last inserted integer
*/
Static function getLastInsId (){
$ Link = self: $ link;
If (! $ Link) return false;
Return self: $ lastInsertId;
}
/**
* Obtain the database version.
* @ Access function
* @ Param
* @ Return string
*/
Static function getDbVersion (){
$ Link = self: $ link;
If (! $ Link) return false;
Return self: $ dbVersion;
}
/**
* Obtain database table information
* @ Access function
* @ Return array
*/
Static function getTables (){
$ Info = array ();
If (self: query ("show tables ")){
$ Result = self: getAll ();
Foreach ($ result as $ key => $ val ){
$ Info [$ key] = current ($ val );
}
}
Return $ info;
}
/**
* Obtain the field information of the data table.
* @ Access function
* @ Return array
*/
Static function getFields ($ tableName ){
// Obtain the database connection
$ Link = self: $ link;
$ SQL = "SELECT
ORDINAL_POSITION, COLUMN_NAME, COLUMN_TYPE, DATA_TYPE,
IF (ISNULL (CHARACTER_MAXIMUM_LENGTH), (NUMERIC_PRECISION + NUMERIC_SCALE), CHARACTER_MAXIMUM_LENGTH) as maxchar,
IS_NULLABLE, COLUMN_DEFAULT, COLUMN_KEY, EXTRA, COLUMN_COMMENT
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME =: tabName AND TABLE_SCHEMA = '". DB_NAME ."'";
Self: $ queryStr = sprintf ($ SQL, $ tableName );
$ Something = $ link-> prepare ($ SQL );
$ Something-> bindParam (': tabname', $ tableName );
$ Something-> execute ();
$ Result = $ Something-> fetchAll (constant ('pdo: FETCH_ASSOC '));
$ Info = array ();
Foreach ($ result as $ key => $ val ){
$ Info [$ val ['column _ name'] = array (
'Postion' => $ val ['ordinal _ position'],
'Name' => $ val ['column _ name'],
'Type' => $ val ['column _ type'],
'D _ type' => $ val ['data _ type'],
'Length' => $ val ['maxchar '],
'Notnull '=> (strtolower ($ val ['is _ nullable']) = "no "),
'Default' => $ val ['column _ default'],
'Primary' => (strtolower ($ val ['column _ key']) = 'pri '),
'Autoinc' => (strtolower ($ val ['Extra ']) = 'auto _ increment '),
'Comment' => $ val ['column _ comment']
);
}
// If an error exists, an exception is thrown.
Self: haveErrorThrowException ();
Return $ info;
}
/**
* Shut down the database.
* @ Access function
*/
Static function close (){
Self: $ link = null;
}
/**
* SQL Command Security filtering
* @ Access function
* @ Param string $ str SQL command
* @ Return string
*/
Static function escape_string ($ str ){
Return addslashes ($ str );
}
/*************************************** **************************************** **************************/
/* Internal operation method */
/*************************************** **************************************** **************************/
/**
* An error occurs and an exception is thrown.
* @ Access function
* @ Return
*/
Static function haveErrorThrowException (){
$ Obj = empty (self: $ PDOStatement )? Self ::$ link: self ::$ PDOStatement;
$ ArrError = $ obj-> errorInfo ();
If (count ($ arrError)> 1) {// error message
// $ This-> rollback ();
Self: $ error = $ arrError [2]."

[SQL statement]: ". self: $ queryStr;
// Throw_exception ($ this-> error );
Throw_exception (self ::$ error );
Return false;
}
// Throws an exception mainly for the execute () method
If (self: $ queryStr = '') throw_exception ('query was empty

[SQL statement]: ');
}
/**
* Where analysis
* @ Access function
* @ Param mixed $ where query condition
* @ Return string
*/
Static function parseWhere ($ where ){
$ WhereStr = '';
If (is_string ($ where) | is_null ($ where )){
$ WhereStr = $ where;
}
Return empty ($ whereStr )? '': 'Where'. $ whereStr;
}
/**
* Order analysis
* @ Access function
* @ Param mixed $ order sorting
* @ Return string
*/
Static function parseOrder ($ order ){
$ OrderStr = '';
If (is_array ($ order ))
$ OrderStr. = 'Order by'. implode (',', $ ORDER );
Else if (is_string ($ order )&&! Empty ($ order ))
$ OrderStr. = 'Order by'. $ ORDER;
Return $ orderStr;
}
/**
* Limit analysis
* @ Access function
* @ Param string $ limit
* @ Return string
*/
Static function parseLimit ($ limit ){
$ LimitStr = '';
If (is_array ($ limit )){
If (count ($ limit)> 1)
$ LimitStr. = 'limit'. $ LIMIT [0]. ','. $ limit [1]. '';
Else
$ LimitStr. = 'limit'. $ LIMIT [0]. '';
} Else if (is_string ($ limit )&&! Empty ($ limit )){
$ LimitStr. = 'limit'. $ LIMIT .'';
}
Return $ limitStr;
}
/**
* Group Analysis
* @ Access function
* @ Param mixed $ group
* @ Return string
*/
Static function parseGroup ($ group ){
$ GroupStr = '';
If (is_array ($ group ))
$ GroupStr. = 'group by'. implode (',', $ GROUP );
Else if (is_string ($ group )&&! Empty ($ group ))
$ GroupStr. = 'group by'. $ GROUP;
Return empty ($ groupStr )? '': $ GroupStr;
}
/**
* Having analysis
* @ Access function
* @ Param string $ having
* @ Return string
*/
Static function parseHaving ($ having ){
$ HavingStr = '';
If (is_string ($ having )&&! Empty ($ having ))
$ HavingStr. = 'having '. $ HAVING;
Return $ havingStr;
}
/**
* Fields analysis
* @ Access function
* @ Param mixed $ fields
* @ Return string
*/
Static function parseFields ($ fields ){
If (is_array ($ fields )){
Array_walk ($ fields, array ($ this, 'addspecialchar '));
$ FieldsStr = implode (',', $ fields );
} Else if (is_string ($ fields )&&! Empty ($ fields )){
If (false === strpos ($ fields ,''')){
$ Fields = explode (',', $ fields );
Array_walk ($ fields, array ($ this, 'addspecialchar '));
$ FieldsStr = implode (',', $ fields );
} Else {
$ FieldsStr = $ fields;
}
} Else $ fieldsStr = '*';
Return $ fieldsStr;
}
/**
* Sets analysis, called when updating data
* @ Access function
* @ Param mixed $ values
* @ Return string
*/
Private function parseSets ($ sets ){
$ SetsStr = '';
If (is_array ($ sets )){
Foreach ($ sets as $ key => $ val ){
$ Key = self: addSpecialChar ($ key );
$ Val = self: fieldFormat ($ val );
$ SetsStr. = "$ key =". $ val .",";
}
$ SetsStr = substr ($ setsStr, 0,-1 );
} Else if (is_string ($ sets )){
$ SetsStr = $ sets;
}
Return $ setsStr;
}
/**
* Field formatting
* @ Access function
* @ Param mixed $ value
* @ Return mixed
*/
Static function fieldFormat (& $ value ){
If (is_int ($ value )){
$ Value = intval ($ value );
} Else if (is_float ($ value )){
$ Value = floatval ($ value );
} Elseif (preg_match ('/^ \ (\ w * (\ + | \-| \ * | \/)? \ W * \) $/I ', $ value )){
// Other fields can be directly used in the field values
// For example, (score + 1) (name) must contain parentheses
$ Value = $ value;
} Else if (is_string ($ value )){
$ Value = '\ ''. self: escape_string ($ value ).'\'';
}
Return $ value;
}
/**
* The field and table name add' match
* Ensure that the keyword used in the command is correct for mysql
* @ Access function
* @ Param mixed $ value
* @ Return mixed
*/
Static function addSpecialChar (& $ value ){
If ('*' = $ value | false! = Strpos ($ value, '(') | false! = Strpos ($ value, '.') | false! = Strpos ($ value ,''')){
// If it contains * or uses the SQL method, it is not processed.
} Elseif (false === strpos ($ value ,''')){
$ Value = '''. trim ($ value ).''';
}
Return $ value;
}
/**
+ ----------------------------------------------------------
* Remove null elements
+ ----------------------------------------------------------
* @ Access function
+ ----------------------------------------------------------
* @ Param mixed $ value
+ ----------------------------------------------------------
* @ Return mixed
+ ----------------------------------------------------------
*/
Static function removeEmpty ($ value ){
Return! Empty ($ value );
}
/**
* Query execution mainly targets SELECT, SHOW, and other commands.
* @ Access function
* @ Param string $ SQL command
* @ Return mixed
*/
Static function query ($ SQL = ''){
// Obtain the database connection
$ Link = self: $ link;
If (! $ Link) return false;
Self: $ queryStr = $ SQL;
// Release the previous query result
If (! Empty (self ::$ PDOStatement) self: free ();
Self: $ PDOStatement = $ link-> prepare (self ::$ queryStr );
$ Bol = self: $ PDOStatement-> execute ();
// If an error exists, an exception is thrown.
Self: haveErrorThrowException ();
Return $ bol;
}
/**
* Database operation method
* @ Access function
* @ Param string $ SQL execution statement
* @ Param boolean $ whether lock is locked (not locked by default)
* @ Return void
Public function execute ($ SQL = '', $ lock = false ){
If (empty ($ SQL) $ SQL = $ this-> queryStr;
Return $ this-> _ execute ($ SQL );
}*/
/**
* Execute statements for INSERT, UPDATE, and DELETE
* @ Access function
* @ Param string $ SQL command
* @ Return integer
*/
Static function execute ($ SQL = ''){
// Obtain the database connection
$ Link = self: $ link;
If (! $ Link) return false;
Self: $ queryStr = $ SQL;
// Release the previous query result
If (! Empty (self ::$ PDOStatement) self: free ();
$ Result = $ link-> exec (self: $ queryStr );
// If an error exists, an exception is thrown.
Self: haveErrorThrowException ();
If (false ===$ result ){
Return false;
} Else {
Self: $ numRows = $ result;
Self: $ lastInsertId = $ link-> lastInsertId ();
Return self: $ numRows;
}
}
/**
* Whether it is a database change operation
* @ Access private
* @ Param string $ query SQL command
* @ Return boolen returns false if it is a query operation.
*/
Static function isMainIps ($ query ){
$ QueryIps = 'Insert | UPDATE | DELETE | REPLACE | CREATE | DROP | load data | SELECT. * INTO | COPY | ALTER | GRANT | REVOKE | LOCK | unlock ';
If (preg_match ('/^ \ s *"? ('. $ QueryIps.') \ s +/I ', $ query )){
Return true;
}
Return false;
}
/**
* Filter POST submitted data
* @ Access private
* @ Param mixed $ data POST submit data
* @ Param string $ table data table name
* @ Return mixed $ newdata
*/
Static function filterPost ($ table, $ data ){
$ Table_column = self: getFields ($ table );
$ Newdata = array ();
Foreach ($ table_column as $ key => $ val ){
If (array_key_exists ($ key, $ data) & ($ data [$ key])! = ''){
$ Newdata [$ key] = $ data [$ key];
}
}
Return $ newdata;
}
/**
* Start the transaction
* @ Access function
* @ Return void
*/
Static function startTrans (){
// Data rollback support
$ Link = self: $ link;
If (! $ Link) return false;
If (self: $ transTimes = 0 ){
$ Link-> beginTransaction ();
}
Self: $ transTimes ++;
Return;
}
/**
* Used for querying and submitting under the non-automatic submission status
* @ Access function
* @ Return boolen
*/
Static function commit (){
$ Link = self: $ link;
If (! $ Link) return false;
If (self ::$ transTimes> 0 ){
$ Result = $ link-> commit ();
Self: $ transTimes = 0;
If (! $ Result ){
Throw_exception (self: $ error ());
Return false;
}
}
Return true;
}
/**
* Transaction rollback
* @ Access function
* @ Return boolen
*/
Public function rollback (){
$ Link = self: $ link;
If (! $ Link) return false;
If (self ::$ transTimes> 0 ){
$ Result = $ link-> rollback ();
Self: $ transTimes = 0;
If (! $ Result ){
Throw_exception (self: $ error ());
Return false;
}
}
Return true;
}
}
?>