PHP PDO database Operation encapsulation Class code

/** * Database PDO operations */ Class Mysqlpdo { public static $PDOStatement = null; /** * Configuration of database connection parameters * @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 = '; /** * Single-piece mode, saving the unique instance of PDO class, database connection resources * @var Object * @access Public */ protected static $link; /** * Whether the database is already 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 = '; /** * ID of the last inserted record * @var Integer * @access Public */ public static $lastInsertId = null; /** * Returns the number of affected records * @var Integer * @access Public */ public static $numRows = 0; Number of transaction instructions public static $transTimes = 0; /** * Constructor function, * @param $dbconfig database connection related information, array (' ServerName ', ' UserName ', ' Password ', ' defaultdb ', ' db_port ', ' Db_type ') */ Public function __construct ($dbConfig = ") { if (!class_exists (' PDO ')) self::throw_exception ("Not supported: PDO"); If no parameters are transferred, the default data definition is used 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 '])) self::throw_exception ("No database configuration defined"); Self:: $config = $dbConfig; if (Empty (self:: $config [' params ']) "Self:: $config [' params '] = array (); /************************************* Gorgeous divider 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) { Self::throw_exception ($e->getmessage ()); } if (!self:: $link) { Self::throw_exception (' PDO CONNECT ERROR '); return false; } Self:: $link->exec (' SET NAMES '). Db_charset); Self:: $dbVersion = self:: $link->getattribute (Constant ("pdo::attr_server_info")); Token connection Succeeded Self:: $connected = true; Unregister Database Connection configuration information Unset ($configs); } Return self:: $link; } /** * Release Query Results * @access function */ static function free () { Self:: $PDOStatement = null; } /************************/ /* Database operation */ /************************/ /** * Get all the query data * @access function * @return Array */ static function GetAll ($sql =null) { if ($sql! = null) { Self::query ($sql); } Return Data set $result = self:: $PDOStatement->fetchall (Constant (' pdo::fetch_assoc ')); return $result; } /** * Get a query result * @access function * @param string $sql SQL instructions * @param integer $seek pointer position * @return Array */ static function GetRow ($sql =null) { if ($sql! = null) { Self::query ($sql); } Return array Set $result = self:: $PDOStatement->fetch (Constant (' pdo::fetch_assoc '), constant (' pdo::fetch_ori_next ')); return $result; } /** * Execute SQL statements, automatically determine queries or perform operations * @access function * @param string $sql SQL instructions * @return Mixed */ static function Dosql ($sql = ") { if (Self::ismainips ($sql)) { Return Self::execute ($sql); }else { Return Self::getall ($sql); } } /** * Find records in table based on specified ID (only for single table operation) * @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::p arsefields ($fields), $tabName, $priId)); } /** * Find Records * @access function * @param string $tables data table name * @param mixed $where query conditions * @param string $fields field name * @param string $order sort * @param string $limit How many data to fetch * @param string $group grouping * @param string $having * @param boolean $lock is locked * @return Arrayobject */ static function Find ($tables, $where = "", $fields = ' * ', $order =null, $limit =null, $group =null, $having =null) { $sql = ' SELECT '. Self::p arsefields ($fields) .' From '. $tables . Self::p arsewhere ($where) . Self::p arsegroup ($group) . Self::p arsehaving ($having) . Self::p arseorder ($order) . Self::p arselimit ($limit); $dataAll = Self::getall ($sql); if (count ($dataAll) ==1) {$rlt = $dataAll [0];} else{$rlt = $dataAll;} return $RLT; } /** * Insert (Single) record * @access function * @param mixed $data data * @param string $table data table name * @return False | Integer */ function Add ($data, $table) { Filter submission Data $data =self::filterpost ($table, $data); foreach ($data as $key = = $val) { if (Is_array ($val) && strtolower ($val [0]) = = = ' Exp ') { $val = $val [1]; Use an expression??? }elseif (Is_scalar ($val)) { $val = Self::fieldformat ($val); }else{ Remove a Compound 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 into ' $table. ' ('. $fieldsStr. ') VALUES ('. $valuesStr. ') '; Return Self::execute ($sql); } /** * Update record * @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::p arsesets ($sets). Self::p arsewhere ($where). Self::p arseorder ($order). Self::p arselimit ($limit); Return Self::execute ($sql); } /** * Save the value of a field * @access function * @param string $field The name of the field to be saved * @param string $value field value * @param string $table Data Sheet * @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) { Update field contents as plain string if there is ' (' as a SQL instruction update) if (false = = = Strpos ($value, ' (') | | $asString) $value = ' "'. $value. '"; $sql = ' UPDATE '. $table. ' SET '. $field. ' = '. $value. Self::p arsewhere ($condition); Return Self::execute ($sql); } /** * Delete Records * @access function * @param mixed $where as conditional 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::p arsewhere ($where). Self::p arseorder ($order). Self::p arselimit ($limit); Return Self::execute ($sql); } /** +---------------------------------------------------------- * Modify or save data (only for single-table operation) * There is a primary key ID is modified, no primary key ID is added * Change record: +---------------------------------------------------------- * @access function +---------------------------------------------------------- * @param $tabName Table name * @param $aPost submit a form $_post * @param $priId primary Key ID * @param $aNot a field or array to exclude * @param $aCustom A custom array that is attached to the database to save * @param whether $isExits already exists: True, does not exist: false +---------------------------------------------------------- * @return Boolean modified or saved successfully +---------------------------------------------------------- */ 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) {//New $aPost = Array_filter ($aPost, Array ($this, ' removeempty ')); Return Self::add ($aPost, $tabName); } else {//Modify Return Self::update ($aPost, $tabName, "id=". $priId); } } /** * Get the SQL statement for the most recent query * @access function * @param * SQL executed @return String */ static function Getlastsql () { $link = self:: $link; if (! $link) return false; Return self:: $queryStr; } /** * Get the last inserted ID * @access function * @param * @return The Data ID when the last integer is inserted */ static function Getlastinsid () { $link = self:: $link; if (! $link) return false; Return self:: $lastInsertId; } /** * Get DB version * @access function * @param * @return String */ static function Getdbversion () { $link = self:: $link; if (! $link) return false; Return self:: $dbVersion; } /** * Get table information from the database * @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; } /** * Get field information from the data table * @access function * @return Array */ static function GetFields ($tableName) { Get database Join $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); $sth = $link->prepare ($sql); $sth->bindparam (': TabName ', $tableName); $sth->execute (); $result = $sth->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 '] ); } Throws an exception if there is an error Self::haveerrorthrowexception (); return $info; } /** * Close Database * @access function */ static function Close () { Self:: $link = null; } /** * SQL command Security filtering * @access function * @param string $STR SQL instructions * @return String */ static function escape_string ($STR) { Return addslashes ($STR); } /************************/ /* Internal Operation method */ /************************/ /** * There is an error throwing exception * @access function * @return */ static function Haveerrorthrowexception () { $obj = Empty (self:: $PDOStatement)? Self:: $link: Self:: $PDOStatement; $arrError = $obj->errorinfo (); if ($arrError [0]!== ' 00000 ') {//error message Self:: $error = $arrError [0]. "|". $arrError [2]. " [SQL]: ". Self:: $queryStr." "; Self::throw_exception (self:: $error); return false; } Throwing exceptions primarily for the Execute () method if (self:: $queryStr = = ") self::throw_exception (' Query was empty [SQL statement]: '); } /** * Where analysis * @access function * @param mixed $where query conditions * @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 * Sort @param mixed $order * @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 */ 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)) { Supports direct use of other fields in field values For example (score+1) (name) must contain parentheses $value = $value; }else if (is_string ($value)) { $value = ' \ '. self::escape_string ($value). ' \''; } return $value; } /** * Fields and table names are added ' compliant * Guarantee the use of keywords in the instruction is not wrong for MySQL * @access function * @param mixed $value * @return Mixed */ static function Addspecialchar (& $value) { if (' * ' = = $value | | False!== strpos ($value, ' (') | | false!== strpos ($value, '. ') | | false!== strpos ($value, ")) { Do not process if include * or use SQL method } elseif (false = = = Strpos ($value, ")) { $value = ". Trim ($value). ' `'; } return $value; } /** +---------------------------------------------------------- * Remove empty elements +---------------------------------------------------------- * @access function +---------------------------------------------------------- * @param mixed $value +---------------------------------------------------------- * @return Mixed +---------------------------------------------------------- */ static function Removeempty ($value) { Return!empty ($value); } /** * Execute the query mainly for SELECT, SHOW and other instructions * @access function * @param string $sql SQL instructions * @return Mixed */ static function query ($sql = ") { Get database Join $link = self:: $link; if (! $link) return false; Self:: $queryStr = $sql; Release the previous query results if (!empty (self:: $PDOStatement)) Self::free (); Self:: $PDOStatement = $link->prepare (self:: $QUERYSTR); $bol = self:: $PDOStatement->execute (); Throws an exception if there is an error Self::haveerrorthrowexception (); return $bol; } /** * Database Operation method * @access function * @param string $sql Execute the statement * @param boolean $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 SQL instructions * @return Integer */ static function Execute ($sql = ") { Get database Join $link = self:: $link; if (! $link) return false; Self:: $queryStr = $sql; Release the previous query results if (!empty (self:: $PDOStatement)) Self::free (); $result = $link->exec (self:: $QUERYSTR); Throws an exception if there is an error Self::haveerrorthrowexception (); if (false = = = $result) { return false; } else { Self:: $numRows = $result; Self:: $lastInsertId = $link->lastinsertid (); Return self:: $numRows; } } /** * Whether to change operation for database * @access Private * @param string $query SQL instructions * @return Boolen If the query operation returns false */ 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 submission data * @access Private * Submit data @param mixed $data post * @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 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; } /** * For non-autocommit status, the following query is submitted * @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) { Self::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) { Self::throw_exception (self:: $error ()); return false; } } return true; } /** * Error Handling * @access function * @return void */ static function Throw_exception ($err) { Echo ' ERROR: '. $err. '; } } Copy Code

