Encapsulate a PDO database operation class code_PHP tutorial

/**
* Database PDO operation
*/
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 = '';
/**
* Singleton mode, saves the only instance of the Pdo class and the connection resource of the database
* @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 = '';
/**
* The 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,
* @param $dbconfig database connection related information, array('ServerName', 'UserName', 'Password', 'DefaultDb', 'DB_Port', 'DB_TYPE')
*/
public function __construct($dbConfig=''){
if (!class_exists('PDO')) throw_exception("Not supported: PDO");
//if If no parameters are transmitted, 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'])) throw_exception ("No database configuration defined");
self::$config = $dbConfig;
if(empty(self::$config['params'])) self::$config['params'] = array();
/**************************************GORGEOUS DIVIDERS************ *****************************/
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 successfully
self::$connected = true;
// Log out of the database connection Configuration information
unset($configs);
}
return self::$link;
}
/**
* Release query results
* @access function
*/
static function free() {
self::$PDOStatement = null;
}
/*********************************************************************************************************/
/* Database operation*/
/*********************************************************************************************************/
/* *
* Get all query data
* @access function
* @return array
*/
static function getAll($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 command
* @param integer $seek pointer position
* @return array
*/
static function getRow($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 statement, automatically judge to query or perform 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);
}
}
/**
* Search for records in the table based on the specified ID (only for single table operations)
* @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));
}
/**
* Search record
* @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 pieces of data to take
* @param string $group grouping
* @param string $having
* @param boolean $lock whether to add Lock
* @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) record
* @access function
* @param mixed $data data
* @param string $table data table name
* @return false | integer
*/
static function add($data,$table) {
//过滤提交数据
$data=self::filterPost($table,$data);
foreach ($data as $key=>$val){
if(is_array($val) && strtolower($val[0]) == 'exp') {
$val = $val[1]; // 使用表达式 ???
}elseif (is_scalar($val)){
$val = self::fieldFormat($val);
}else{
// 去掉复合对象
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::parseSets($sets).self::parseWhere($where).self::parseOrder($order).self::parseLimit($limit);
return self::execute($sql);
}
/**
* Save the value of a certain field
* @access function
* @param string $field The name of the field to be saved
* @param string $value Field value
* @param string $ table data table
* @param string $where saving condition
* @param boolean $asString whether the field value is a string
* @return void
*/
static function setField($field, $value, $table, $condition="", $asString=false) {
// 如果有'(' 视为 SQL指令更新 否则 更新字段内容为纯字符串
if(false === strpos($value,'(') || $asString) $value = '"'.$value.'"';
$sql = 'UPDATE '.$table.' SET '.$field.'='.$value.self::parseWhere($condition);
return self::execute($sql);
}
/**
* Delete record
* @access function
* @param mixed $where is 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::parseWhere($where).self::parseOrder($order).self::parseLimit($limit);
return self::execute($sql);
}
/**
+------------------------------------------------ ------------
* Modify or save data (only for single table operation)
* If there is a primary key ID, it will be modified, if there is no primary key ID, it will be added
* Modify Record:
+-------------------------------------------------- ----------------
* @access function
+------------------------- ----------------------------------
* @param $tabName Table name
* @param $ aPost $_POST to submit the form
* @param $priId Primary key ID
* @param $aNot A field or array to be excluded
* @param $aCustom A customized array, appended to the database and saved
* @param $isExits Whether it already exists exists: true, does not exist: false
+-------------------------- -------------------------------
* @return Boolean Whether the modification or saving was 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) { //新增
$aPost = array_filter($aPost, array($this, 'removeEmpty'));
return self::add($aPost, $tabName);
} else { //修改
return self::update($aPost, $tabName, "id=".$priId);
}
}
/**
* Get the sql statement of the latest query
* @access function
* @param
* @return String The executed SQL
*/
static function getLastSql() {
$link = self::$link;
if ( !$link ) return false;
return self::$queryStr;
}
/**
* Get the last inserted ID
* @access function
* @param
* @return integer The last inserted data ID
*/
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 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;
}
/**
* Get the field information of the data table
* @access function
* @return array
*/
static function getFields($tableName) {
// 获取数据库联接
$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']
);
}
// 有错误则抛出异常
self::haveErrorThrowException();
return $info;
}
/**
* Close 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);
}
/*********************************************************************************************************/
/* 内部操作方法 */
/*********************************************************************************************************/
/**
* 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) { // 有错误信息
//$this->rollback();
self::$error = $arrError[2]. "

[ SQL语句 ] : ".self::$queryStr;
//throw_exception($this->error);
throw_exception(self::$error);
return false;
}
//主要针对execute()方法抛出异常
if(self::$queryStr=='')throw_exception('Query was empty

[ SQL语句 ] :');
}
/**
* 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
* @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分析
* @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分析
* @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分析
* @access function
* @param string $having
* @return string
*/
static function parseHaving($having) {
$havingStr = '';
if(is_string($having) && !empty($having))
$havingStr .= ' HAVING '.$having;
return $havingStr;
}
/**
* fields分析
* @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)){
// 支持在字段的值里面直接使用其它字段
// 例如 (score+1) (name) 必须包含括号
$value = $value;
}else if(is_string($value)) {
$value = '''.self::escape_string($value).''';
}
return $value;
}
/**
* Add ` to field and table names in line with
* Ensure that keywords used in instructions are 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,'`')) {
//如果包含* 或者 使用了sql方法 则不作处理
} 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 query, mainly for SELECT, SHOW and other instructions
* @access function
* @param string $sql sql command
* @return mixed
*/
static function query($sql='') {
// 获取数据库联接
$link = self::$link;
if ( !$link ) return false;
self::$queryStr = $sql;
//释放前次的查询结果
if ( !empty(self::$PDOStatement) ) self::free();
self::$PDOStatement = $link->prepare(self::$queryStr);
$bol = self::$PDOStatement->execute();
// 有错误则抛出异常
self::haveErrorThrowException();
return $bol;
}
/**
* Database operation method
* @access function
* @param string $sql execution statement
* @param boolean $lock whether to lock (default is not locked)
* @return void
public function execute($sql='',$lock=false) {
if(empty($sql)) $sql = $this->queryStr;
return $this->_execute($ sql);
}*/
/**
* Execution statement for INSERT, UPDATE and DELETE
* @access function
* @param string $sql sql command
* @return integer
*/
static function execute($sql='') {
// 获取数据库联接
$link = self::$link;
if ( !$link ) return false;
self::$queryStr = $sql;
//释放前次的查询结果
if ( !empty(self::$PDOStatement) ) self::free();
$result = $link->exec(self::$queryStr);
// 有错误则抛出异常
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 If it is a query operation, return 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
* @param mixed $data POST submission 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 transaction
* @access function
* @return void
*/
static function startTrans() {
//数据rollback 支持
$link = self::$link;
if ( !$link ) return false;
if (self::$transTimes == 0) {
$link->beginTransaction();
}
self::$transTimes++;
return ;
}
/**
* Used for query submission under 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;
}
}
?>