PHP PDO MySQL database operation class

Original: PHP PDO MySQL Database operation class

FindAll (Array( ' Field ' = 'id,albumname,albumimage, Mainactor,directors,tags,info,area, keywords,wflag,year,mod_version,totalduration ', ' where ' = =$where, ' order ' = ' flag desc,hit_count desc ', ' limit ' = ' = '$limit[' Offset ']},{$limit[' PageSize ']} " )); $res=$db->field ('id,stitle,jisu,liuchang,gaoqing,chaoqing, Totalduration,btime,etime‘) ->where ($where) ->limit ("{$limit[' Offset ']},{$limit[' PageSize ']} ") -findAll ();/Final classMyDb {Private $pdo;  Public $tableName;//Table namePrivate $sql;//the currently executing SQL statement Public $error= ";//error message for current execution of SQL Public $prefix= ";//prefix Public $charset= ' UTF8 ';  Public $sqlQuery=Array( ' field ' + = ', ' where ' + ' 1 ', ' join ' = ', ' group ' = ', ' have ' + ', ' order ' = ', ' ' limit ' = ', ' Union ' = ', ' params ' =Array() );  Public $fields=Array();//get the field name of the current table

private static $_instance=array ();p rotected $dbName;//user name protected $username;p rivate $k;//password protected $password;// hostname protected $host;//port number protected $port;//a timestamp that indicates when the current link expires, and an object is rebuilt after it expires. protected $expireTime;//pdo link attribute array protected $attr =array (//This timeout parameter, in fact the configuration on the MySQL server is accurate.    This is used here when to reconstruct the object//description if this parameter is set, if the PDO is not explicitly set to NULL, the connection resource may not be freed on MySQL. \pdo::attr_timeout=>30, \pdo::attr_errmode=>\pdo::errmode_silent, \pdo::attr_oracle_nulls=>\pdo::null_ NATURAL,//If this parameter is set to True,inndob, manual commit//\pdo::attr_autocommit=>false is required, \pdo::attr_default_fetch_mode= >\PDO::FETCH_ASSOC, \pdo::attr_persistent=>false);/** * Construction method * @param array $config config file * @param array $attr arrays     Item */private function __construct ($config, $attr) {//indexed array merge cannot use the merge foreach ($this->attr as $key = $row) {    if (Isset ($attr [$key]) $this->attr[$key]= $attr [$key]; } $this->pdo=new \pdo ($config [' DSN '], $config [' username '], $config [' Password '], $this->attr)if (Isset ($config [' prefix ']) && $config [' prefix ']!= ') $this->prefix= $config [' prefix '];if (isset ($ config[' charset ') && $config [' CharSet ']!= ') $this->charset= $config [' CharSet ']; $this->pdo->exec ("Set names {$this->charset}");//Save the current database name, host, Port. Preg_match ('/dbname= (\w+)/', $config [' DSN '], $ma);p reg_match ('/host= (. *?);/', $config [' DSN '], $ma 1);p reg_match ('/ Port= (\w+)/', $config [' DSN '], $ma 2), $this->dbname= $ma [1]; $this->host= $ma 1[1]; $this->port= $ma 2[1]? $ma 2[1 ]:3306, $this->username= $config [' username ']; $this->password= $config [' Password '];//set the link duration $timeout= $this- >attr[\PDO::ATTR_TIMEOUT]; $this->expiretime=time () + $timeout;}    Private Function __clone () {}/** * @param $config * @param array $attr * @return \iphp\core\mydb */ static public Function getinstance ($config, $attr =array ()) {if (!is_array ($config)) $config =app::getapp ()->getconf    IG ($config);            $k =md5 (Implode (", $config)); If the connection is not created, or if the connection has expired if (!) ( Static::$_instance[$k] instanceof self) {static::$_instance[$k]=new self ($config, $attr); static::$_instance[$k]->k= $k;}        If the connection times out.            ElseIf (Time () >static::$_instance[$k]->expiretime) {static::$_instance[$k]->close ();            static::$_instance[$k]=new Self ($config, $attr);        static::$_instance[$k]->k= $k; }return static::$_instance[$k];} /** * * @param unknown_type $tableName * @return $this */public function TableName ($tableName) {$this->tablename= $this-    >prefix. $tableName;//$this->setfield (); return $this;} /** * @return \pdo */Public Function Getpdo () {return $this->pdo;}    /** * Gets the current SQL statement and completes the parameter substitution * @return string */public function GetSQL () {return $this->sql;} /** * @param string $sql * @return BOOL */Public Function findAll ($sql = ') {$stmt = $this->query ($sql); if (    ! $stmt) return False;return $stmt->fetchall ();} /** * @param string $sql * @return BOOL */Public FUNCTIon FindOne ($sql = ") {$this->sqlquery[' limit ']=1; $stmt = $this->query ($sql); if ($stmt ===false) return false; return $stmt->fetch ();} /** * Find records based on primary key * @param mixed $ids * @return return a one-dimensional or two-dimensional array */public function find ($ids) {$num =count (explode (', ', $ids)); $this- >setfield (), if ($num ==1) {$res = $this->where ("{$this->fields[$this->tablename][' pk ']}= ' {$ids} ') FindOne ();} else{//if it is a string primary key, enclose the quotation mark $tmp=explode (', ', $ids), $tmp =array_map (function ($item) {return "'". $item. "'";}, $tmp); $ids = Implode (', ', $tmp); $res = $this->where ("{$this->fields[$this->tablename][' PK '} in ({$ids})")->findall ( );} return $res;} /** * method of inserting data, auto-complete parameter binding * @param array $data one-dimensional array (field=>value) * @return Boolean | int */public function Insert ($data) {$this->setfield (); $params =array (); $field =array (); $placeholder =array (); foreach ($data as $key = + $row) {//Remove illegal field information if (!in_array ($key, $this->fields[$this->tablename]) continue;$ params[': '. $key]= $row $field []= $key; $placeholder []= ': '. $key;} Insert current record$sql = "INSERT INTO {$this->tablename} (". Implode (', ', $field). ') VALUES ('. Implode (', ', $placeholder). ') '; $this->sqlquery[' params ']= $params $this->sql= $sql; return $this->exec ($sql, $this->sqlquery[' params ') );}  /** * Delete Record * @param string $where WHERE Condition * @param array $params binding parameter * @return bool */public function Delete ($where = ", $params = Array ()) {if ($where! =") $this->sqlquery[' where ']= $where; if ($params! = ") $this->sqlquery[' params ']= $params; $sql = "Delete from {$this->tablename} where {$this->sqlquery[' where '}"; $this->sql= $sql; return $ This->exec ($sql, $this->sqlquery[' params ');} /** * Simplified Delete () method, based on the deletion of the primary key */public function del ($ids) {$this->setfield (), $tmp =explode (', ', $ids); $tmp =array_map ( function ($item) {return "'". $item. "'";}, $tmp); $ids =implode (', ', $tmp); $sql = "Delete from {$this->tablename} where { $this->fields[$this->tablename][' PK ']} "in ($ids)"; $this->sql= $sql; return $this->exec ($sql);} /** * Get the last ID number inserted */public function lAstid () {return $this->pdo->lastinsertid ();} /** * Modify Data Update support parameter binding only supports the WHERE parameter * @param array $data The value of the column to be changed array (column name = value) * @param string $where WHERE Condition * @para M array $params Binding parameter * @return Boolean | int the number of rows affected */public function Update ($data, $where = ", $params = Array ()) {$this-&gt ; SetField (); if (!is_array ($data)) return false;if ($where! = ") $this->sqlquery[' where ']= $where; if ($params! =") $ this->sqlquery[' params ']= $params; $updateField =array (); foreach ($data as $key + $value) {//Illegal fields do not have if (!in_ Array ($key, $this->fields[$this->tablename])) continue; $updateField []= "{$key}=:{$key}"; $this->sqlquery[ ' Params ' [": {$key}"]= $value;} $sql = "Update {$this->tablename} set". Implode (', ', $updateField). "Where {$this->sqlquery[' where ']}"; $this- >sql= $sql; return $this->exec ($sql, $this->sqlquery[' params ');} /** * Get all field information of the data table */public function SetField () {if (Is_array ($this->fields[$this->tablename])) return; $sql = " desc {$this->tablename} "; $res = $this->findall ($SQL), foreach ($res as $row) {if ($row [' Key ']== ' PRI ') $this->fields[$this->tablename][' PK ']= $row [' Field '];$ this->fields[$this->tablename][]= $row [' Field '];}  Gets the field information for the current action table public function GetField () {if (! $this->fields[$this->tablename]) $this->setfield (); return $this->fields[$this->tablename];} Get the total number of records public function count ($sql = ') {$this->sqlquery[' field ']= ' count (*) as C '; $stmt = $this->query ($sql); if ( $STMT) return false; $res = $stmt->fetch ();//Reset the query field after execution to return $res [' C '];} Get SQL Execution error public function GetError () {return $this->error;} Public Function SetError ($error) {$this->error= $error;} /** * Sweep rows with result set query, support parameter binding * If you need to traverse the database, use the Query method, and then the Stmt object returned by foreach is available. * @param mixed $sql * @return boolean| Pdostatement */public function query ($sql = ") {$sql = $this->joinsql ($sql); $stmt = $this->pdo->prepare ($sql); $errorInfo = $stmt->errorinfo (); $stmt->setfetchmode (\PDO::FETCH_ASSOC); $stmt->execute ($this sqlquery[' params ');//clear the SQL condition value, the Desc class executes the SThe QL statement does not have to be clearly cached if (Strpos ($sql, ' desc ')!==0) $this->clearsqlquery (); $errorInfo = $stmt->errorinfo (); if ($errorInfo [0]!= ' 00000 ') {$this->seterror ($errorInfo [2]); return false;} return $stmt;} /** * Executes queries with no result set, supports parameter binding * @param string $sql * @param array $params * @return Returns the number of rows affected or false */public function exec ($sql, $p Arams = Array ()) {$stmt = $this->pdo->prepare ($sql), if ($params! = ") $this->sqlquery[' params ']= $params; Stmt->execute ($this->sqlquery[' params '); $this->clearsqlquery (); $errorInfo = $stmt->errorinfo (); if ($ errorinfo[0]!= ' 00000 ') {$this->seterror ($errorInfo [2]); return false;} return $stmt->rowcount ();}    Set binding parameters Public function params ($params) {$this->sqlquery[' params ']=empty ($params)? ': $params; return $this;} /** * Automatic binding parameters * @param $params * @return $this */Public Function Autoparams ($params) {$this-        >setfield ();       foreach ($params as $key = + $row) {if (In_array ($key, $this->fields[$this->tablename])) {         $this->sqlquery[' params ' [": {$key}"] = $row;    }} return $this; }/** * Combined SQL statement * @param mixed $sql * @return returns the combined SQL statement */public function Joinsql ($sql) {if (is_string ($sql) && $sql ! = ") {$this->sql= $sql; return $sql;} ElseIf (Is_array ($sql) && $sql! = ") {foreach ($sql as $key + $row) {if (!array_key_exists ($key, $this, SQLQuery) continue; $this->sqlquery[$key]= $row;}} else {} $this->sql= "Select {$this->sqlquery[' field ']} from {$this->tablename}\n"; if ($this->sqlquery[') Join ']!= ') $this->sql.= "{$this->sqlquery[' join '}"; $this->sql.= "where {$this->sqlquery[' where ']}\n" if ($this->sqlquery[' group ']!= ') $this->sql.= "group by {$this->sqlquery[' group ']}\n"; if ($this sqlquery[' having ']!= ') $this->sql.= "have {$this->sqlquery[' having ']}\n '; if ($this->sqlquery[' order ')! = ') $this->sql.= "ORDER by {$this->sqlquery[' order ']}\n"; if ($this->sqlquery[' limit ']!= ') $this->sql.= "Limit {$this->sqlquery[' Limit ']}\n, if ($this->sqlquery[' Union ']!= ') $this->sql.= "union {$this->sqlquery[' union ']}\n"; return $this->sql;} Method for setting a field public function field ($field) {$this->sqlquery[' field ']=empty ($field)? ' * ': $field; return $this;} /** * * @param unknown_type $where * @return \iphp\core\mydb */public function where ($where) {$this->sqlquery[' where ']= Empty ($where)? '    1 ': $where; return $this;} /** * @param $tableName * @param $condition * @return $this */Public Function join ($tableName, $conditi    On) {$this->sqlquery[' join '].= "join {$tableName} on {$condition}\n"; return $this;} /** * @param $tableName * @param $condition * @return $this */Public Function Leftjoin ($tableName, $con    dition) {$this->sqlquery[' join '].= "left join {$tableName} on {$condition}\n"; return $this;} /** * @param $tableName * @param $condition * @return $this */Public Function Rightjoin ($tableName, $co ndition) {$this->sqlquery[' join '].= "right JoIn {$tableName} on {$condition}\n "; return $this;} /** * @param $group * @return $this */Public Function Group ($group) {$this->sqlquery[' group ']=empty ($gro    UP)? ": $group; return $this;} /** * @param $having * @return $this */Public function has ($having) {$this->sqlquery[' having ']=empty (    $having)? ": $having; return $this;} /** * @param $order * @return $this */Public Function order ($order) {$this->sqlquery[' order ']=empty ($ord    er)? ": $order; return $this;} /** * @param $limit * @return $this */Public Function limit ($limit) {$this->sqlquery[' limit ']=empty ($lim    It)? ": $limit; return $this;} /** * @param $union * @return $this */Public Function Union ($union) {$this->sqlquery[' Union ']=empty ($uni ON)? ': $union; return $this;} /** * Clear the SQL cache */public function clearsqlquery () {//before clearing the cache, save the current SQL statement first. if (!empty ($this->sqlquery[' params ')) {foreach ($this->sqlquery[' params '] as $key = + $param) $thisSql=str_replace ($key, ' "'. $param. '" ', $this->sql);} $this->sql=nl2br ($this->sql), foreach ($this->sqlquery as $key = $row) {if ($key = = ' where ') $this sqlquery[$key]= ' 1 '; ElseIf ($key = = ' field ') $this->sqlquery[$key]= ' * '; ElseIf ($key = = ' params ') $this->sqlquery[ $key]=array (); else $this->sqlquery[$key]= ";}} Before executing the FindOne FindAll method, get the current SQL statement to execute, public function Getsqlcache () {$sql = $this->joinsql ("); if (!empty ($this- >sqlquery[' params ']) {foreach ($this->sqlquery[' params ') as $key = + $param) $sql =str_replace ($key, ' "'. $ param. ' "', $sql);} return $sql;} /** * Gets the current database name */public function Getdbname () {return $this->dbname;} /** * Get user name */public function getUser () {return $this->username;} /** * Get password */public function Getpass () {return $this->password;} Public Function GetHost () {return $this->host;} Public Function Getport () {return $this->port;} /** * Get connection-related details. */public function Getconninfo () {return array (' host ' = = $this->host, ' port ' = = $this->poRT, ' username ' = $this->username, ' Password ' and '->password ', ' dbname ' + $this->dbname,);} /** * Open transaction and set error mode to exception * Use try cacth to roll back or submit the * BeginTransaction () method will turn off autocommit (autocommit) mode, * until the transaction commits or rolls back Mode for PDO set */Public Function BeginTransaction () {$this->pdo->setattribute (\pdo::attr_errmode, \PD        O::errmode_exception);    return $this->pdo->begintransaction ();    }/** * commits * @return BOOL */Public Function commit () {return $this->pdo->commit (); }/** * ROLLBACK TRANSACTION * @return BOOL */Public Function RollBack () {return $this->pdo->rollback    (); }/** * Close connection */public function close () {$this->pdo=null;} /** * Close all connections */public static function CloseAll () {foreach (static::$_instance as $o) {if ($o instanceof self) $o-&G T;close ();}} /** * Gets the next self-growth ID of the current table */public function getnextautoincrement ($tableName) {$sql = "Show Table status where name = ' {$tAblename} ' ";    $res = $this->findone ($sql);    return $res [' auto_increment ']; /** * Add a timestamp field to a table * @param $tableName table name * @param $name field name * @return Bool|int */Publ                 IC function Addtimestamp ($tableName, $name = ' utime ') {$ADDSQL = "ALTER TABLE {$tableName} add {$name} TIMESTAMP        Not NULL DEFAULT current_timestamp on UPDATE current_timestamp; ";        $addSql. = "ALTER TABLE {$tableName} ADD index {$name} ($name)";    return $this->exec ($ADDSQL); }}

PHP PDO MySQL database operation class