Home >Backend Development >PHP Tutorial >parallels desktop 9 encapsulates a PDO database operation class code

parallels desktop 9 encapsulates a PDO database operation class code

WBOY
WBOYOriginal
2016-07-29 08:40:441087browse

Copy the code The code is as follows:


/**
 * 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 Pdo class, database connection resource
* @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;
// 事务指令数
public static $transTimes = 0;
/**
* Constructor,
* @param $dbconfig database connection related information, array('ServerName', 'UserName', 'Password', 'DefaultDb', 'DB_Port', 'DB_TYPE')
*/
public function __construct($dbC => 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("没有定义数据库配置");
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('连接失败:'.$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"));
// 标记连接成功
self::$connected = true;
// 注销数据库连接配置信息
unset($configs);
}
return self::$link;
}
/**
* Release query results
* @access function
*/
static function free() {
self::$PDOStatement = null;
}
/*********************************************************************************************************/
/* 数据库操作 */
/*********************************************************************************************************/
/**
* Get all query data
* @access function
* @return array
*/
static function getAll($sql=null) {
self::query($sql);
//返回数据集
$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);
// 返回数组集
$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);
}
}
/**
* Find 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 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 pieces of data to take
* @param string $group Grouping
* @param string $having
* @param boolean $lock Whether to lock or not
* @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 Save condition
* @param boolean $asString Whether the field value is a string
* @return void
*/
static function setField($field, $value, $table, $c $asString=false) {
// 如果有'(' 视为 SQL指令更新 否则 更新字段内容为纯字符串
if(false === strpos($value,'(') || $asString) $value = '"'.$value.'"';
$sql = 'UPDATE '.$table.' SET '.$field.'='.$value.self::parseWhere($condition);
return self::execute($sql);
}
/**
* Delete records
* @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 is modified, if there is no primary key ID, it is added
* Modification 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 exception is thrown if there is an error
* @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;
}
/**
* Adding ` to field and table names complies with
* to ensure that there are no errors when using keywords in instructions. 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 instruction
* @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 statements 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;
}
}
?>

以上就介绍了parallels desktop 9 封装一个PDO数据库操作类代码,包括了parallels desktop 9方面的内容,希望对PHP教程有兴趣的朋友有所帮助。

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn