php encapsulated pdo database operation tool class and usage example
- 2021-12-09 08:32:54
- OfStack
This paper describes the pdo database operation tool class and its usage encapsulated by php. Share it for your reference, as follows:
<?php
header("Content-Type:text/html;charset=utf-8");
class PdoMysql{
public static $config = array();// Set connection parameters and configuration information
public static $link = null;// Save the connection identifier
public static $pconnect = false;// Do you want to open a long connection
public static $dbVersion = null;// Save the database version
public static $connected = false;// Determine whether the connection was successful
public static $PDOStatement = null;// Guarantee PDOStatement Object
public static $queryStr = null;// Save the last action performed
public static $error = null;// Save error messages
public static $lastInsertId = null;// Save on 1 Object saved by the step insert operation AUTO_INCREMANT
public static $numRows = null;// Number of records affected
/**
* Constructor to connect to the database
*
* @param array|string $dbConfig The database configuration
*
* @return boolean ( description_of_the_return_value )
*/
public function __construct($dbConfig=''){
if(!class_exists("PDO")){
self::throw_exception(" Not supported PDO, Please open it first ");
}
if(!is_array($dbConfig)){
$dbConfig = array(
'hostname' => 'localhost',
'username' => 'root',
'password' => '1234',
'database' => 'test',
'hostport' => '3306',
'dbms' => 'mysql',
'dsn' => 'mysql:host=localhost;dbname=test'
);
}
if(empty($dbConfig['hostname'])){
self::throw_exception(" No database configuration defined , Please define first ");
}
self::$config = $dbConfig;
if(empty(self::$config['params'])){
self::$config['params'] = array();
}
if(!isset(self::$link)){
$configs = self::$config;
if(self::$pconnect){
// Open long connection , Add to the configuration array
$configs['params'][constant("PDO::ATTR_PERSISTENT")] = true;
}
try {
self::$link = new PDO($configs['dsn'],$configs['username'],$configs['password'],$configs['params']);
} catch (PDOException $e) {
self::throw_exception($e->getMessage());
}
if(!self::$link){
self::throw_exception("PDO Connection error ");
return false;
}
self::$link->exec("set names utf8");
self::$dbVersion = self::$link->getAttribute(constant("PDO::ATTR_SERVER_VERSION"));
unset($configs);
}
}
/**
* Get all the records
*
* @param <type> $sql The sql
*
* @return <type> All.
*/
public static function getAll($sql=null){
if($sql!=null){
self::query($sql);
}
$result = self::$PDOStatement->fetchAll(constant("PDO::FETCH_ASSOC"));
return $result;
}
/**
* Get 1 A record
*
* @param <type> $sql The sql
*
* @return <type> The row.
*/
public static function getRow($sql=null){
if($sql!=null){
self::query($sql);
}
$result = self::$PDOStatement->fetch(constant("PDO::FETCH_ASSOC"));
return $result;
}
/**
* Perform add, delete and modify operations to return the number of affected records
*
* @param <type> $sql The sql
*
* @return boolean ( description_of_the_return_value )
*/
public static function execute($sql=null){
$link = self::$link;
if(!$link)return false;
if($sql!=null){
self::$queryStr = $sql;
}
if(!empty(self::$PDOStatement))self::free();
$result = $link->exec(self::$queryStr);
self::haveErrorThrowException();
if($result){
self::$lastInsertId = $link->lastInsertId();
self::$numRows = $result;
return $result;
}else{
return false;
}
}
/**
* Find records by primary key
*
* @param <type> $tabName The tab name
* @param <type> $priId The pri identifier
* @param string $fields The fields
*
* @return <type> ( description_of_the_return_value )
*/
public static function findById($tabName,$priId,$fields='*'){
$sql = 'SELECT %s FROM %s WHERE id=%d';
return self::getRow(sprintf($sql,self::parseFields($fields),$tabName,$priId));
}
/**
* Execute a common query
*
* @param <type> $tables The tables
* @param <type> $where The where
* @param string $fields The fields
* @param <type> $group The group
* @param <type> $having The having
* @param <type> $order The order
* @param <type> $limit The limit
*
* @return <type> ( description_of_the_return_value )
*/
public static function find($tables,$where=null,$fields='*',$group=null,$having=null,$order=null,$limit
=null){
$sql = 'SELECT '.self::parseFields($fields).' FROM '.$tables
.self::parseWhere($where)
.self::parseGroup($group)
.self::parseHaving($having)
.self::parseOrder($order)
.self::parseLimit($limit);
$data = self::getAll($sql);
return $data;
}
/**
* Add a record
*
* @param <type> $data The data
* @param <type> $table The table
*
* @return <type> ( description_of_the_return_value )
*/
public static function add($data,$table){
$keys = array_keys($data);
array_walk($keys, array('PdoMySQL','addSpecialChar'));
$fieldsStr = join(',',$keys);
$values = "'".join("','",array_values($data))."'";
$sql = "INSERT {$table}({$fieldsStr}) VALUES({$values})";
return self::execute($sql);
}
/**
* Update data
*
* @param <type> $data The data
* @param <type> $table The table
* @param <type> $where The where
* @param <type> $order The order
* @param <type> $limit The limit
*/
public static function update($data,$table,$where=null,$order=null,$limit=null){
$sets = '';
foreach ($data as $key => $value) {
$sets .= $key."='".$value."',";
}
$sets = rtrim($sets,',');
$sql = "UPDATE {$table} SET {$sets}".self::parseWhere($where).self::parseOrder($order).self::parseLimit($limit);
echo $sql;
}
/**
* Delete data
*
* @param <type> $data The data
* @param <type> $table The table
* @param <type> $where The where
* @param <type> $order The order
* @param <type> $limit The limit
*
* @return <type> ( description_of_the_return_value )
*/
public static function delete($table,$where=null,$order=null,$limit=null){
$sql = "DELETE FROM {$table} ".self::parseWhere($where).self::parseOrder($order).self::parseLimit($limit);
return self::execute($sql);
}
/**
* Execute a query
*
* @param string $sql The sql
*
* @return boolean ( description_of_the_return_value )
*/
public static function query($sql=''){
$link = self::$link;
if(!$link)return false;
// Determine whether there is a result set before, and if so, release the result set
if(!empty(self::$PDOStatement))self::free();
self::$queryStr = $sql;
self::$PDOStatement = $link->prepare(self::$queryStr);
$res = self::$PDOStatement->execute();
self::haveErrorThrowException();
return $res;
}
/**
* Gets the last executed sql
*
* @return boolean The last sql.
*/
public static function getLastSql(){
$link = self::$link;
if(!$link){
return false;
}
return self::$queryStr;
}
/**
* Gets the last inserted ID
*
* @return boolean The last insert identifier.
*/
public static function getLastInsertId(){
$link = self::$link;
if(!$link){
return false;
}
return self::$lastInsertId;
}
/**
* Get the version of the database
*
* @return boolean The database version.
*/
public static function getDbVersion(){
$link = self::$link;
if(!$link){
return false;
}
return self::$dbVersion;
}
/**
* Get the table in the database
*
* @return array ( description_of_the_return_value )
*/
public static function showTables(){
$tables = array();
if(self::query("show tables")){
$result = self::getAll();
foreach ($result as $key => $value) {
$tables[$key] = current($value);
}
}
return $tables;
}
/**
* Analyse where Condition
*
* @param <type> $where The where
*
* @return <type> ( description_of_the_return_value )
*/
public static function parseWhere($where){
$whereStr = '';
if(is_string($where)&&!empty($where)){
$whereStr = $where;
}
return empty($whereStr) ? '' : ' WHERE '.$whereStr;
}
/**
* Analyse group
*
* @param <type> $group The group
*
* @return <type> ( description_of_the_return_value )
*/
public static function parseGroup($group){
$groupStr = '';
if(is_array($group)){
$groupStr = implode(',', $group);
}elseif(is_string($group)&&!empty($group)){
$groupStr = $group;
}
return empty($groupStr) ? '' : ' GROUP BY '.$groupStr;
}
/**
* Analyse having
*
* @param <type> $having The having
*
* @return <type> ( description_of_the_return_value )
*/
public static function parseHaving($having){
$havingStr = '';
if(is_string($having)&&!empty($having)){
$havingStr = $having;
}
return empty($havingStr) ? '' : ' HAVING '.$havingStr;
}
/**
* Analyse order
*
* @param <type> $order The order
*
* @return <type> ( description_of_the_return_value )
*/
public static function parseOrder($order){
$orderStr = '';
if(is_string($order)&&!empty($order)){
$orderStr = $order;
}
return empty($orderStr) ? '' : ' ORDER BY '.$orderStr;
}
/**
* Analyse limit
*
* @param <type> $limit The limit
*
* @return <type> ( description_of_the_return_value )
*/
public static function parseLimit($limit){
$limitStr = '';
if(is_array($limit)){
$limitStr = implode(',', $limit);
}elseif(is_string($limit)&&!empty($limit)){
$limitStr = $limit;
}
return empty($limitStr) ? '' : ' LIMIT '.$limitStr;
}
/**
* Parse field
*
* @param <type> $fields The fields
*
* @return string ( description_of_the_return_value )
*/
public static function parseFields($fields){
if(is_array($fields)){
array_walk($fields, array('PdoMySQL','addSpecialChar'));
$fieldsStr = implode(',', $fields);
}elseif (is_string($fields)&&!(empty($fields))) {
if(strpos($fields, '`')===false){
$fields = explode(',', $fields);
array_walk($fields, array('PdoMySQL','addSpecialChar'));
$fieldsStr = implode(',', $fields);
}else{
$fieldsStr = $fields;
}
}else{
$fieldsStr = "*";
}
return $fieldsStr;
}
/**
* Reference to a word field by reverse quotation marks
*
* @param string $value The value
*
* @return string ( description_of_the_return_value )
*/
public static function addSpecialChar(&$value){
if($value==="*"||strpos($value,'.')!==false||strpos($value,'`')!==false){
// No need to deal with it
}elseif(strpos($value, '`')===false){
$value = '`'.trim($value).'`';
}
return $value;
}
/**
* Release the result set
*/
public static function free(){
self::$PDOStatement = null;
}
/**
* Throw an error message
*
* @return boolean ( description_of_the_return_value )
*/
public static function haveErrorThrowException(){
$obj = empty(self::$PDOStatement) ? self::$link : self::$PDOStatement;
$arrError = $obj->errorInfo();
if($arrError[0]!='00000'){
self::$error = 'SQLSTATE=>'.$arrError[0].'<br/>SQL Error=>'.$arrError[2].'<br/>Error SQL=>'.self::$queryStr;
self::throw_exception(self::$error);
return false;
}
if(self::$queryStr==''){
self::throw_exception(' Not implemented SQL Statement ');
return false;
}
}
/**
* Custom error handling
*
* @param <type> $errMsg The error message
*/
public static function throw_exception($errMsg){
echo $errMsg;
}
/**
* Destroy the connection object and close the database
*/
public static function close(){
self::$link = null;
}
}
$pdo = new PdoMysql();
var_dump($pdo->showTables());
For more readers interested in PHP related contents, please check the topics on this site: "Summary of PHP Database Operation Skills Based on pdo", "Summary of php+mysqli Database Programming Skills", "Introduction to php Object-Oriented Programming", "Summary of php String (string) Usage", "Introduction to php+mysql Database Operation Skills" and "Summary of php Common Database Operation Skills"
I hope this article is helpful to everyone's PHP programming.