PHP PDO operation MySQL Package class

/** * Auther soulence * Call Data class file * Modify 2015/06/12 */ Class Dbconnect { Private $dbname = null; Private $pdo = null; Private $persistent = false; Private $statement = null; Private $lastInsID = null; private static $_instance = []; Private Function __construct ($dbname, $attr) { $this->dbname = $dbname; $this->persistent = $attr; } public static function db ($flag = ' R ', $persistent =false) { if (!isset ($flag)) { $flag = ' R '; } if (!class_exists (' PDO ')) { throw new Exception (' not found PDO '); return false; } $mysql _server = yaf_registry::get (' mysql '); if (!isset ($mysql _server[$flag])) { return false; } $options _arr = Array (Pdo::mysql_attr_init_command = ' SET NAMES '. $mysql _server[$flag] [' CharSet '],pdo::attr_ DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC); if ($persistent = = = True) { $options _arr[pdo::attr_persistent] = true; } try { $pdo = new PDO ($mysql _server[$flag [' connectionString '], $mysql _server[$flag] [' username '], $mysql _server[$flag] [' Password '], $options _arr); } catch (Pdoexception $e) { throw new Exception ($e->getmessage ()); Exit (' Connection failed: '. $e->getmessage ()); return false; } if (! $pdo) { throw new Exception (' PDO CONNECT ERROR '); return false; } return $pdo; } /** * Get Operational database objects * @param string $dbname The corresponding database is who * @param bool $attr is long connected * Return FALSE to indicate that a given database does not exist */ public static function getinstance ($dbname = ' r ', $attr = False) { $mysql _server = yaf_registry::get (' mysql '); if (!isset ($mysql _server[$dbname])) { return false; } $key = MD5 (MD5 ($dbname. $attr, True)); if (!isset (self::$_instance[$key]) | |!is_object (self::$_instance[$key])) self::$_instance[$key] = new self ($dbname, $attr); return self::$_instance[$key]; } Private Function GetConnect () { $this->pdo = self::d b ($this->dbname, $this->persistent); } /** * Query operation * @param string $sql SQL statement that executes the query * @param array $data The conditional format for the query is [': id ' = ' + $id, ': Name ' = ' $name] (recommended) or for [1=> $id,2=> $name] * @param bool $one whether to return a content default to No */ Public Function query ($sql, $data = [], $one = False) { if (!is_array ($data) | | | empty ($sql) | |!is_string ($SQL)) return false; $this->free (); return $this->querycommon ($data, $sql, $one); } /** * Common method of internal query */ Private Function Querycommon ($data, $sql, $one) { $this->pdoexec ($data, $sql); if ($one) { return $this->statement->fetch (PDO::FETCH_ASSOC); }else{ return $this->statement->fetchall (PDO::FETCH_ASSOC); } } /** * Query operations for multiple SQL statements * @param array $arr _sql The SQL statement that executes the query is formatted as [$sql 1, $sql 2] * @param array $arr _data query with $arr_sql corresponding conditional format for [[': id ' = ' = $id, ': name ' = ' = ' $name],[': id ' + $id, ': Name ' = ' $name]] ( Recommended) or for [[1=> $id,2=> $name],[1=> $id,2=> $name]] * @param bool $one whether to return a content default to no here if set to True then each SQL returns only one piece of data */ Public Function Queryes ($arr _sql, $arr _data = [], $one = False) { if (!is_array ($arr _sql) | | empty ($arr _sql) | |!is_array ($arr _data)) return false; $this->free (); $res = []; $i = 0; foreach ($arr _sql as $val) { if (!isset ($arr _data[$i])) $arr _data[$i] = []; ElseIf (!is_array ($arr _data[$i])) throw new Exception (' Error where Queryes sql: '. $val. ' Where: '. $arr _data[$i]); $res [] = $this->querycommon ($arr _data[$i], $val, $one); $i + +; } return $res; } /** * Paging Package * * @param string $sql * @param int $page Indicates the start of the first page * @param int $pageSize Indicates how many pages per page * @param the criteria for the array $data query */ Public Function Limitquery ($sql, $page =0, $pageSize =20, $data = []) { $page = Intval ($page); if ($page < 0) { return []; } $pageSize = Intval ($pageSize); if ($pageSize > 0) {//PageSize is 0, all data is taken $sql. = ' LIMIT '. $pageSize; if ($page > 0) { $start _limit = ($page-1) * $pageSize; $sql. = ' OFFSET '. $start _limit; } } return $this->query ($sql, $data); } /** * This is used to add a delete modify operation using a transactional operation * @param string $sql SQL statement that executes the query * @param array $data The conditional format for the query is [': id ' = ' + $id, ': Name ' = ' $name] (recommended) or for [1=> $id,2=> $name] * @param bool $Transaction Whether the transaction operation defaults to No */ Public Function Executeddl ($sql, $data = [], $Transaction = False) { if (!is_array ($data) | |!is_string ($SQL)) return false; $this->free (); if ($Transaction) $this->pdo->begintransaction ();//Open transaction try{ $this->execres ($data, $sql); if ($Transaction) $this->pdo->commit ();//Transaction commit return $this->lastinsid; } catch (Exception $e) { if ($Transaction) $this->pdo->rollback ();//Transaction rollback throw new Exception (' Error ddlexecute <=====> '. $e->getmessage ()); return false; } } /** * This is used to add a delete modify operation using a transactional operation * It's a multi-line execution. * @param array $arr _sql SQL statement that needs to be executed * @param array $arr _data The conditions of the SQL statement corresponding to the array * @param bool $Transaction Whether the transaction operation defaults to No */ Public Function Executeddles ($arr _sql, $arr _data = [], $Transaction = False) { if (!is_array ($arr _sql) | | empty ($arr _sql) | |!is_array ($arr _data)) return false; $res = []; $this->free (); if ($Transaction) $this->pdo->begintransaction ();//Open transaction try{ $i = 0; foreach ($arr _sql as $val) { if (!isset ($arr _data[$i])) $arr _data[$i] = []; ElseIf (!is_array ($arr _data[$i])) { if ($Transaction) $this->pdo->rollback ();//Transaction rollback throw new Exception (' Error where ddlexecutees sql: '. $val. ' Where: '. $arr _data[$i]); } $this->execres ($arr _data[$i], $val); $res [] = $this->lastinsid; $i + +; } if ($Transaction) $this->pdo->commit ();//Transaction commit return $res; } catch (Exception $e) { if ($Transaction) $this->pdo->rollback ();//Transaction rollback throw new Exception (' Error ddlexecutees array_sql: '. Json_encode ($arr _sql). ' <=====> '. $e->getmessage ()); return false; } return $res; } /** * This method is used to calculate the number of bars returned by the query note that it only supports select COUNT (*) from TABLE ... or select COUNT (0) from TABLE ... Way * SQL statement @param string $sql query * @param array $data The conditions of the SQL statement */ Public Function CountRows ($sql, $data = []) { if (!is_array ($data) | | | empty ($sql) | |!is_string ($SQL)) return false; $this->free (); $res = $this->pdoexec ($data, $sql); if ($res = = False) return false; return $this->statement->fetchcolumn (); } /** * This method is used to calculate the number of bars returned by the query it is to execute multiple SQL * SQL statement @param string $sql query * @param array $data The conditions of the SQL statement */ Public Function countrowses ($arr _sql, $arr _data = []) { if (!is_array ($arr _sql) | | empty ($arr _sql) | |!is_array ($arr _data)) return false; $res = []; $this->free (); $i = 0; foreach ($arr _sql as $val) { if (!isset ($arr _data[$i])) $arr _data[$i] = []; ElseIf (!is_array ($arr _data[$i])) throw new Exception (' Error where countrowses sql: '. $val. ' Where: '. $arr _data[$i]); $res 1 = $this->pdoexec ($arr _data[$i], $val); if ($res 1 = = False) $res [] = false; Else $res [] = $this->statement->fetchcolumn (); } return $res; } /** * Here is another method because there will be a lot of things in the project that need to be opened and then the final commit * @param bool $Transaction Whether the transaction operation defaults to No */ Public Function Getdb ($Transaction =false) { $this->transaction = $Transaction; $this->getconnect (); if ($Transaction = = = True) $this->pdo->begintransaction ();//Open transaction return $this; } /** * This method can be executed more than once it is executed by the DDL statement * Note that it is necessary to use together with GETDB and sqcommit can not be used alone OH * If the transaction is not turned on Sqcommit method can not call * SQL statement @param string $sql query * @param array $data The conditions of the SQL statement */ Public Function execsq ($sql, $data = []) { if ($this->checkparams ($sql, $data) = = = = False) return false; try{ $this->execres ($data, $sql); return $this->lastinsid; } catch (Exception $e) { if (Isset ($this->transaction) && $this->transaction = = = True) $this->pdo->rollback ();//Transaction rollback throw new Exception (' Error execsq<=====> '. $e->getmessage ()); return false; } finally { if (!empty ($this->statement)) { $this->statement->closecursor (); unset ($this->statement); } } } /** * Method of executing the query it needs to pass a connection database object * @param string $sql SQL statement that executes the query * @param array $data The conditional format for the query is [': id ' = ' + $id, ': Name ' = ' $name] (recommended) or for [1=> $id,2=> $name] * @param bool $one whether to return a content default to No */ Public Function querysq ($sql, $data = [], $one = False) { if ($this->checkparams ($sql, $data) = = = = False) return false; return $this->pdoexecsq ($sql, $data, [1, $one]); } /** * Paging Package * * @param string $sql * @param int $page Indicates the start of the first page * @param int $pageSize Indicates how many pages per page * @param the criteria for the array $data query */ Public Function limitquerysq ($sql, $page =0, $pageSize =20, $data = []) { $page = Intval ($page); if ($page < 0) { return []; } $pageSize = Intval ($pageSize); if ($pageSize > 0) {//PageSize is 0, all data is taken $sql. = ' LIMIT '. $pageSize; if ($page > 0) { $start _limit = ($page-1) * $pageSize; $sql. = ' OFFSET '. $start _limit; } } return $this->querysq ($sql, $data); } /** * This method is used to calculate the number of bars returned by the query note that it only supports select COUNT (*) from TABLE ... or select COUNT (0) from TABLE ... Way * SQL statement @param string $sql query * @param array $data The conditions of the SQL statement */ Public Function countrowssq ($sql, $data = []) { if ($this->checkparams ($sql, $data) = = = = False) return false; return $this->pdoexecsq ($sql, $data, [2]); } /** * Here's a way to provide this is the last commit operation if the transaction is not turned on this method can not be called at the end of the */ Public Function Sqcommit () { if (Empty ($this->pdo) | |!is_object ($this->pdo)) return false; if (Isset ($this->transaction) && $this->transaction = = = True) $this->pdo->commit ();//Commit a transaction unset ($this->pdo); } /** * Internal Call method */ Public Function Checkparams ($sql, $data) { if (Empty ($this->pdo) | |!is_object ($this->pdo) | |!is_array ($data) | | empty ($sql) | |!is_string ($SQL)) return false; return true; } /** * Internal Call method */ Private Function pdoexecsq ($sql, $data, $select = []) { try{ $res = $this->pdoexec ($data, $sql); if (empty ($select)) return $res; else{ if ($select [0] = = = 1) { if ($select [1] = = = True) return $this->statement->fetch (PDO::FETCH_ASSOC); Else return $this->statement->fetchall (PDO::FETCH_ASSOC); }elseif ($select [0] = = = 2) return $this->statement->fetchcolumn (); Else return false; } } catch (Exception $e) { throw new Exception ($e->getmessage ()); return false; } finally { if (!empty ($this->statement)) { $this->statement->closecursor (); unset ($this->statement); } } } /** * Internal Call method */ Private Function Execres ($data, $sql) { $res = $this->pdoexec ($data, $sql); $in _id = $this->pdo->lastinsertid (); if (Preg_match ("/^\s* (insert\s+into| Replace\s+into) \s+/i ", $sql) &&!empty ($in _id)) $this->lastinsid = $in _id; Else $this->lastinsid = $res; } /** * methods used by the internal call method to execute SQL statements directly */ Private Function Pdoexec ($data, $sql) { $this->statement = $this->pdo->prepare ($sql); if (false = = = $this->statement) return false; if (!empty ($data)) { foreach ($data as $k = $v) { $this->statement->bindvalue ($k, $v); } } $res = $this->statement->execute (); if (! $res) { throw new Exception (' sql: '. $sql. ' <====>where: '. Json_encode ($data). ' <====>error: '. Json_encode ($this->statement->errorinfo ())); }else{ return $res; } } /** * The internal call method is used to release the */ Private Function Free () { if (Is_null ($this->pdo)) $this->getconnect (); if (!empty ($this->statement)) { $this->statement->closecursor (); $this->statement = null; } } } ?> Copy Code

PHP, PDO, MYSQL

