New PDO database operation class php edition (Mysql only)
- 2020-05-19 04:21:42
- OfStack
/**
* Author: hu rui
* Date: 2012/07/21
* Email: hooray0905@foxmail.com
*/
class HRDB{
protected $pdo;
protected $res;
protected $config;
/* The constructor */
function __construct($config){
$this->Config = $config;
$this->connect();
}
/* Database connection */
public function connect(){
$this->pdo = new PDO($this->Config['dsn'], $this->Config['name'], $this->Config['password']);
$this->pdo->query('set names utf8;');
// Serialize the result into stdClass
//$this->pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
// Write your own code to capture Exception
$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
/* Database shutdown */
public function close(){
$this->pdo = null;
}
public function query($sql){
$res = $this->pdo->query($sql);
if($res){
$this->res = $res;
}
}
public function exec($sql){
$res = $this->pdo->exec($sql);
if($res){
$this->res = $res;
}
}
public function fetchAll(){
return $this->res->fetchAll();
}
public function fetch(){
return $this->res->fetch();
}
public function fetchColumn(){
return $this->res->fetchColumn();
}
public function lastInsertId(){
return $this->res->lastInsertId();
}
/**
* Parameters that
* int $debug Whether to turn on debugging, turn on output sql statements
* 0 Don't open
* 1 open
* 2 Start and terminate the program
* int $mode The return type
* 0 Return multiple records
* 1 Returns a single record
* 2 Returns the number of rows
* string/array $table Database tables, two transfer modes
* General mode:
* 'tb_member, tb_money'
* Array mode:
* array('tb_member', 'tb_money')
* string/array $fields Need to query the database field, allowed to be empty, the default is to find all, two transfer value mode
* General mode:
* 'username, password'
* Array mode:
* array('username', 'password')
* string/array $sqlwhere Query condition, allowed to be empty, two modes of value transfer
* General mode:
* 'and type = 1 and username like "%os%"'
* Array mode:
* array('type = 1', 'username like "%os%"')
* string $orderby Sort, by default id Reverse order
*/
public function select($debug, $mode, $table, $fields="*", $sqlwhere="", $orderby="tbid desc"){
// Processing parameters
if(is_array($table)){
$table = implode(', ', $table);
}
if(is_array($fields)){
$fields = implode(', ', $fields);
}
if(is_array($sqlwhere)){
$sqlwhere = ' and '.implode(' and ', $sqlwhere);
}
// Database operation
if($debug === 0){
if($mode === 2){
$this->query("select count(tbid) from $table where 1=1 $sqlwhere");
$return = $this->fetchColumn();
}else if($mode === 1){
$this->query("select $fields from $table where 1=1 $sqlwhere order by $orderby");
$return = $this->fetch();
}else{
$this->query("select $fields from $table where 1=1 $sqlwhere order by $orderby");
$return = $this->fetchAll();
}
return $return;
}else{
if($mode === 2){
echo "select count(tbid) from $table where 1=1 $sqlwhere";
}else if($mode === 1){
echo "select $fields from $table where 1=1 $sqlwhere order by $orderby";
}
else{
echo "select $fields from $table where 1=1 $sqlwhere order by $orderby";
}
if($debug === 2){
exit;
}
}
}
/**
* Parameters that
* int $debug Whether to turn on debugging, turn on output sql statements
* 0 Don't open
* 1 open
* 2 Start and terminate the program
* int $mode The return type
* 0 No return message
* 1 Returns the number of execution entries
* 2 Returns the last 1 Subinsert record id
* string/array $table Database tables, two transfer modes
* General mode:
* 'tb_member, tb_money'
* Array mode:
* array('tb_member', 'tb_money')
* string/array $set Need to insert the field and content, two transfer value mode
* General mode:
* 'username = "test", type = 1, dt = now()'
* Array mode:
* array('username = "test"', 'type = 1', 'dt = now()')
*/
public function insert($debug, $mode, $table, $set){
// Processing parameters
if(is_array($table)){
$table = implode(', ', $table);
}
if(is_array($set)){
$set = implode(', ', $set);
}
// Database operation
if($debug === 0){
if($mode === 2){
$this->query("insert into $table set $set");
$return = $this->lastInsertId();
}else if($mode === 1){
$this->exec("insert into $table set $set");
$return = $this->res;
}else{
$this->query("insert into $table set $set");
$return = NULL;
}
return $return;
}else{
echo "insert into $table set $set";
if($debug === 2){
exit;
}
}
}
/**
* Parameters that
* int $debug Whether to turn on debugging, turn on output sql statements
* 0 Don't open
* 1 open
* 2 Start and terminate the program
* int $mode The return type
* 0 No return message
* 1 Returns the number of execution entries
* string $table Database tables, two transfer modes
* General mode:
* 'tb_member, tb_money'
* Array mode:
* array('tb_member', 'tb_money')
* string/array $set Need to update the field and content, two transfer value mode
* General mode:
* 'username = "test", type = 1, dt = now()'
* Array mode:
* array('username = "test"', 'type = 1', 'dt = now()')
* string/array $sqlwhere Modify the condition to allow null, two transfer modes
* General mode:
* 'and type = 1 and username like "%os%"'
* Array mode:
* array('type = 1', 'username like "%os%"')
*/
public function update($debug, $mode, $table, $set, $sqlwhere=""){
// Processing parameters
if(is_array($table)){
$table = implode(', ', $table);
}
if(is_array($set)){
$set = implode(', ', $set);
}
if(is_array($sqlwhere)){
$sqlwhere = ' and '.implode(' and ', $sqlwhere);
}
// Database operation
if($debug === 0){
if($mode === 1){
$this->exec("update $table set $set where 1=1 $sqlwhere");
$return = $this->res;
}else{
$this->query("update $table set $set where 1=1 $sqlwhere");
$return = NULL;
}
return $return;
}else{
echo "update $table set $set where 1=1 $sqlwhere";
if($debug === 2){
exit;
}
}
}
/**
* Parameters that
* int $debug Whether to turn on debugging, turn on output sql statements
* 0 Don't open
* 1 open
* 2 Start and terminate the program
* int $mode The return type
* 0 No return message
* 1 Returns the number of execution entries
* string $table The database table
* string/array $sqlwhere Delete condition, allow null, two transfer modes
* General mode:
* 'and type = 1 and username like "%os%"'
* Array mode:
* array('type = 1', 'username like "%os%"')
*/
public function delete($debug, $mode, $table, $sqlwhere=""){
// Processing parameters
if(is_array($sqlwhere)){
$sqlwhere = ' and '.implode(' and ', $sqlwhere);
}
// Database operation
if($debug === 0){
if($mode === 1){
$this->exec("delete from $table where 1=1 $sqlwhere");
$return = $this->res;
}else{
$this->query("delete from $table where 1=1 $sqlwhere");
$return = NULL;
}
return $return;
}else{
echo "delete from $table where 1=1 $sqlwhere";
if($debug === 2){
exit;
}
}
}
}
In fact, on the use, and before the difference is not big, the purpose is for the convenience of transplantation.
This rewrite focuses on several issues:
insert statement is too complex, fields and values are prone to errors
Let's look at the most common sql insert statement
insert into tb_member (username, type, dt) values ('test', 1, now()) In traditional mode, the fields and values parameters are passed in separately, but the order in which they are passed is 1. This can easily lead to order confusion or missing a parameter.
This time, the problem has been modified to use mysql's unique insert syntax, which is the same as the above function, so it can be written in this way
insert into tb_member set username = "test", type = 1, lastlogindt = now()Just like update1, 1 is clear.
Part of the parameters can be replaced by an array
Like this one: sql
delete from tb_member where 1=1 and tbid = 1 and username = "hooray" The where condition needed to be manually assembled when the method was originally called, which would have been expensive, and is now perfectly acceptable
$where = array(
'tbid = 1',
'username = "hooray"'
);
$db->delete(1, 0, 'tb_member', $where);
No amount of conditions will upset your thinking. Also, not only the where parameter, set in update can be in this form (see full source code for details)
$set = array('username = "123"', 'type = 1', 'lastlogindt = now()');
$where = array('tbid = 1');
$db->update(1, 0, 'tb_member', $set, $where);
Can be customized sql statement
Sometimes, when sql is too complex to assemble sql statements using the methods provided in the class, you need the ability to pass directly into the sql statement execution that I have assembled and return the information. Now, we have this feature
$db->query('select username, password from tb_member');
$rs = $db->fetchAll();
Is it like the original pdo?
Support the creation of multiple database connections
Because the original database operation method, so does not support multiple database connection, in the implementation needs to copy out two identical files, modify part of the variables, the operation is really complex. Now the problem has been solved.
$db_hoorayos_config = array(
'dsn'=>'mysql:host=localhost;dbname=hoorayos',
'name'=>'root',
'password'=>'hooray'
);
$db = new HRDB($db_hoorayos_config);
$db_hoorayos_config2 = array(
'dsn'=>'mysql:host=localhost;dbname=hoorayos2',
'name'=>'root',
'password'=>'hooray'
);
$db2 = new HRDB($db_hoorayos_config2);
This allows you to create two database connections at the same time, making it easy to handle database interactions.
There are so many new functions in general, the whole code is not much, welcome to read. Below is the test code I wrote when writing, also 1 and provide up, convenient for everyone to learn.
require_once('global.php');
require_once('inc/setting.inc.php');
$db = new HRDB($db_hoorayos_config);
echo '<hr><b>select test </b><hr>';
echo ' Normal mode, direct string passing <br>';
$rs = $db->select(1, 0, 'tb_member', 'username, password', 'and type = 1 and username like "%os%"');
echo '<br> Array mode, passable array <br>';
$fields = array('username', 'password');
$where = array('type = 1', 'username like "%os%"');
$rs = $db->select(1, 0, 'tb_member', $fields, $where);
echo '<hr><b>insert test </b><hr>';
echo ' Normal mode, direct string passing <br>';
$db->insert(1, 0, 'tb_member', 'username = "test", type = 1, lastlogindt = now()');
echo '<br> Array mode, passable array <br>';
$set = array('username = "test"', 'type = 1', 'lastlogindt = now()');
$db->insert(1, 0, 'tb_member', $set);
echo '<hr><b>update test </b><hr>';
echo ' Normal mode, direct string passing <br>';
$db->update(1, 0, 'tb_member', 'username = "123", type = 1, lastlogindt = now()', 'and tbid = 7');
echo '<br> Array mode, passable array <br>';
$set = array('username = "123"', 'type = 1', 'lastlogindt = now()');
$where = array('tbid = 1');
$db->update(1, 0, 'tb_member', $set, $where);
echo '<hr><b>delete test </b><hr>';
echo ' Normal mode, direct string passing <br>';
$db->delete(1, 0, 'tb_member', 'and tbid = 1 and username = "hooray"');
echo '<br> Array mode, passable array <br>';
$where = array(
'tbid = 1',
'username = "hooray"'
);
$db->delete(1, 0, 'tb_member', $where);
echo '<hr><b> The custom sql</b><hr>';
$db->query('select username, password from tb_member');
$rs = $db->fetchAll();
var_dump($rs);
$db->close();
By Eric hu