Encapsulates a PDO database operation class Code _php tips

Source: Internet
Author: User
Tags database join dsn getmessage mixed pconnect prepare rollback sprintf
Copy Code code as follows:

<?php
/**
* Database PDO operations
*/
Class Mysqlpdo {
public static $PDOStatement = null;
/**
* Database Connection parameter configuration
* @var Array
* @access Public
*/
public static $config = Array ();
/**
* Whether to use a permanent connection
* @var BOOL
* @access Public
*/
public static $pconnect = false;
/**
* Error message
* @var String
* @access Public
*/
public static $error = ';
/**
* Single-piece mode, Save PDO class unique instance, database connection resources
* @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 = ';
/**
* ID of the last inserted record
* @var Integer
* @access Public
*/
public static $lastInsertId = null;
/**
* Returns the number of impact records
* @var Integer
* @access Public
*/
public static $numRows = 0;
Number of transaction directives
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");
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 ']) 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) {
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 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) {
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) {
Self::query ($sql);
Returns the set of arrays
$result = self:: $PDOStatement->fetch (Constant (' pdo::fetch_assoc '), constant (' pdo::fetch_ori_next '));
return $result;
}
/**
* Execute SQL statements, automatically determine queries or perform actions
* @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 a table based on a specified ID (for a single table operation 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::p arsefields ($fields), $tabName, $priId));
}
/**
* Find Records
* @access function
* @param string $tables data table name
* @param mixed $where query criteria
* @param string $fields field name
* @param string $order sort
* @param string $limit How many data to take
* @param string $group grouping
* @param string $having
* @param boolean $lock lock
* @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
*/
static function Add ($data, $table) {
Filtering submit 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 Compound objects
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 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::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 table
* @param string $where Save conditions
* @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 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 a 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::p arsewhere ($where). Self::p arseorder ($order). Self::p arselimit ($limit);
Return Self::execute ($sql);
}
/**
+----------------------------------------------------------
* Modify or save data (for single table operations only)
* Have primary key ID is modified, no primary key ID is increased
* Modify Records:
+----------------------------------------------------------
* @access function
+----------------------------------------------------------
* @param $tabName Table name
* @param $aPost submit the form $_post
* @param $priId primary Key ID
* @param $aNot a field or array to exclude
* @param $aCustom An array of customizations, attached to the database to save
* @param $isExits is already present: True, does not exist: false
+----------------------------------------------------------
* @return Boolean Modify or Save success
+----------------------------------------------------------
*/
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) {//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 integer was last 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 the table information of 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 the field information of the data table
* @access function
* @return Array
*/
static function GetFields ($tableName) {
Getting a 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 the database
* @access function
*/
static function Close () {
Self:: $link = null;
}
/**
* SQL instruction 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 an exception
* @access function
* @return
*/
static function Haveerrorthrowexception () {
$obj = Empty (self:: $PDOStatement)? Self:: $link: Self:: $PDOStatement;
$arrError = $obj->errorinfo ();
if (count ($arrError) > 1) {//have error message
$this->rollback ();
Self:: $error = $arrError [2]. "<br/><br/> [SQL Statement]:". Self:: $queryStr;
Throw_exception ($this->error);
Throw_exception (self:: $error);
return false;
}
Throw an exception primarily against the Execute () method
if (self:: $queryStr = = ") throw_exception (' Query was empty<br/><br/>[SQL statement]: ');
}
/**
* Where analysis
* @access function
* @param mixed $where query criteria
* @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, which is called when the data is updated
* @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 the use of other fields directly in the value of a field
For example (score+1) (name) must contain parentheses
$value = $value;
}else if (is_string ($value)) {
$value = '. Self::escape_string ($value). ' \'';
}
return $value;
}
/**
* Field and table name add ' match
* Guarantee the use of keywords in the directive 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, ' ")) {
If * is included or SQL method is used, no processing is done
} 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 = ' ") {
Getting a database Join
$link = self:: $link;
if (! $link) return false;
Self:: $queryStr = $sql;
Release 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 statement
* @param boolean $lock is locked (default does not lock)
* @return void
Public Function Execute ($sql = ', $lock =false) {
if (empty ($sql)) $sql = $this->querystr;
return $this->_execute ($sql);
}*/
/**
* EXECUTE statement for INSERT, UPDATE, and delete
* @access function
* @param string $sql SQL instructions
* @return Integer
*/
static function Execute ($sql = ' ") {
Getting a database Join
$link = self:: $link;
if (! $link) return false;
Self:: $queryStr = $sql;
Release 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 operations for the 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 a 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 submission
* @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;
}
}
?>

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.