Brand new PDO database operation php version (for Mysql only)

It also took one year in the company. Today, the company is growing and its products are becoming more and more perfect. although there was no major problem with the database operation function, it took one day to rewrite this function to become more professional. now, it is indeed a class. Copy codeThe code is as follows:
/**
* Author: Hu Rui
* Date:
* Email: hooray0905@foxmail.com
*/

Class HRDB {
Protected $ pdo;
Protected $ res;
Protected $ config;

/* 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 to stdClass
// $ This-> pdo-> setAttribute (PDO: ATTR_DEFAULT_FETCH_MODE, PDO: FETCH_OBJ );
// Write your own code to catch Exception
$ This-> pdo-> setAttribute (PDO: ATTR_ERRMODE, PDO: ERRMODE_EXCEPTION );
}

/* Shut down the database */
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 ();
}

/**
* Parameter description
* Int $ debug: whether debugging is enabled. if debugging is enabled, an SQL statement is output.
* 0: Disabled
* 1. Enable
* 2 enable and terminate the program
* Int $ mode return type
* Multiple records are returned at 0.
* 1 return a single record
* 2 number of returned rows
* String/array $ table database table, two value passing modes
* Normal mode:
* 'TB _ member, tb_money'
* Array mode:
* Array ('TB _ member ', 'TB _ money ')
* String/array $ fields of the database to be queried, which can be null. the default value is to search for all fields. two value passing modes are available.
* Normal mode:
* 'Username, password'
* Array mode:
* Array ('username', 'password ')
* String/array $ sqlwhere query condition, which can be null. two value passing modes are supported.
* Normal mode:
* 'And type = 1 and username like "% OS % "'
* Array mode:
* Array ('Type = 1', 'username like "% OS % "')
* String $ orderby sorting, id reverse by default
*/
Public function select ($ debug, $ mode, $ table, $ fields = "*", $ sqlwhere = "", $ orderby = "tbid desc "){
// Parameter processing
If (is_array ($ table )){
$ Table = implode (',', $ table );
}
If (is_array ($ fields )){
$ Fields = implode (',', $ fields );
}
If (is_array ($ sqlwhere )){
$ Sqlwhere = 'AND'. implode ('and', $ sqlwhere );
}
// Database operations
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;
}
}
}

/**
* Parameter description
* Int $ debug: whether debugging is enabled. if debugging is enabled, an SQL statement is output.
* 0: Disabled
* 1. Enable
* 2 enable and terminate the program
* Int $ mode return type
* 0 No returned information
* 1. number of execution entries returned
* 2 Return the id of the last inserted record
* String/array $ table database table, two value passing modes
* Normal mode:
* 'TB _ member, tb_money'
* Array mode:
* Array ('TB _ member ', 'TB _ money ')
* String/array $ set: The fields and content to be inserted. two value transmission modes are available.
* Normal mode:
* 'Username = "test", type = 1, dt = now ()'
* Array mode:
* Array ('username = "test" ', 'type = 1', 'DT = now ()')
*/
Public function insert ($ debug, $ mode, $ table, $ set ){
// Parameter processing
If (is_array ($ table )){
$ Table = implode (',', $ table );
}
If (is_array ($ set )){
$ Set = implode (',', $ set );
}
// Database operations
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;
}
}
}

/**
* Parameter description
* Int $ debug: whether debugging is enabled. if debugging is enabled, an SQL statement is output.
* 0: Disabled
* 1. Enable
* 2 enable and terminate the program
* Int $ mode return type
* 0 No returned information
* 1. number of execution entries returned
* String $ table database table, two value passing modes
* Normal mode:
* 'TB _ member, tb_money'
* Array mode:
* Array ('TB _ member ', 'TB _ money ')
* String/array $ set fields and content to be updated. two value transmission modes are available.
* Normal mode:
* 'Username = "test", type = 1, dt = now ()'
* Array mode:
* Array ('username = "test" ', 'type = 1', 'DT = now ()')
* String/array $ sqlwhere condition for modification, which can be null. two value passing modes are supported.
* Normal mode:
* 'And type = 1 and username like "% OS % "'
* Array mode:
* Array ('Type = 1', 'username like "% OS % "')
*/
Public function update ($ debug, $ mode, $ table, $ set, $ sqlwhere = ""){
// Parameter processing
If (is_array ($ table )){
$ Table = implode (',', $ table );
}
If (is_array ($ set )){
$ Set = implode (',', $ set );
}
If (is_array ($ sqlwhere )){
$ Sqlwhere = 'AND'. implode ('and', $ sqlwhere );
}
// Database operations
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;
}
}
}

/**
* Parameter description
* Int $ debug: whether debugging is enabled. if debugging is enabled, an SQL statement is output.
* 0: Disabled
* 1. Enable
* 2 enable and terminate the program
* Int $ mode return type
* 0 No returned information
* 1. number of execution entries returned
* String $ table database table
* String/array $ sqlwhere deletion condition, which can be null. two value passing modes are supported.
* Normal mode:
* 'And type = 1 and username like "% OS % "'
* Array mode:
* Array ('Type = 1', 'username like "% OS % "')
*/
Public function delete ($ debug, $ mode, $ table, $ sqlwhere = ""){
// Parameter processing
If (is_array ($ sqlwhere )){
$ Sqlwhere = 'AND'. implode ('and', $ sqlwhere );
}
// Database operations
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, the usage is not much different from the previous one. The purpose is to facilitate the migration.

This rewrite focuses on several issues:

① The insert statement is too complex, and the correspondence between fields and values is prone to errors.

Let's take a look at the most common SQL INSERT statement.

Copy codeThe code is as follows: insert into tb_member (username, type, dt) values ('test', 1, now ())
In the traditional mode, fields and values parameters are passed in separately, but they must be consistent in the input order. This can easily lead to disordered order or missing a parameter.

The problem has been modified this time, and the insert syntax exclusive to mysql is used. it is also the above function, so you can replace it with this syntax.

Copy codeThe code is as follows: insert into tb_member set username = "test", type = 1, lastlogindt = now ()
Just like update.

② Some parameters can be replaced by arrays.

For example

Copy codeThe code is as follows: delete from tb_member where 1 = 1 and tbid = 1 and username = "hooray"
When calling a method, you need to manually assemble the where condition. this operation is costly and can be used now.
Copy codeThe code is as follows:
$ Where = array (
'Tbid = 1 ',
'Username = "hooray "'
);
$ Db-> delete (1, 0, 'TB _ member ', $ where );

If there are more conditions, your thinking will not be disrupted. Similarly, not only the where parameter, set in update can also be in this form (for details, see the complete source code)

Copy codeThe code is as follows:
$ Set = array ('username = "123" ', 'type = 1', 'lastlogindt = now ()');
$ Where = array ('tbid = 1 ');
$ Db-> update (1, 0, 'TB _ member ', $ set, $ where );

③ Customizable SQL statements

Sometimes, SQL statements are too complex to assemble SQL statements using the methods provided in the class. in this case, a function is required to directly import the assembled SQL statements for execution, and return information. Now, this feature is available.

Copy codeThe code is as follows:
$ Db-> query ('select username, password from tb_member ');
$ Rs = $ db-> fetchAll ();

Is it similar to the original pdo style?

④ Support creating multi-database connections

Because it was only a database operation method, it does not support multi-database connections. in implementation, we need to copy two identical files and modify some variables. The operation is complex. Now this problem has been solved.

Copy codeThe code is as follows:
$ 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 = hoorayoss ',
'Name' => 'root ',
'Password' => 'Hooray'
);
$ Db2 = new HRDB ($ db_hoorayos_config2 );

In this way, two database connections can be created at the same time to facilitate the interaction between the database and the database.

There are so many new features and there are not many codes. read more. The following is the test code that I wrote during writing, which is also provided for your convenience.

Copy codeThe code is as follows:
Require_once ('global. php ');
Require_once ('Inc/setting. inc. php ');

$ Db = new HRDB ($ db_hoorayos_config );

Echo' Select test';
Echo 'normal mode, direct string input
';
$ Rs = $ db-> select (1, 0, 'TB _ member', 'username, password ', 'and type = 1 and username like "% OS % "');
Echo'
Array mode, which can be passed into an array
';
$ Fields = array ('username', 'password ');
$ Where = array ('Type = 1', 'username like "% OS % "');
$ Rs = $ db-> select (1, 0, 'TB _ member ', $ fields, $ where );

Echo' Insert test';
Echo 'normal mode, direct string input
';
$ Db-> insert (1, 0, 'TB _ member', 'username = "test", type = 1, lastlogindt = now ()');
Echo'
Array mode, which can be passed into an array
';
$ Set = array ('username = "test" ', 'type = 1', 'lastlogindt = now ()');
$ Db-> insert (1, 0, 'TB _ member ', $ set );

Echo' Update test';
Echo 'normal mode, direct string input
';
$ Db-> update (1, 0, 'TB _ member', 'username = "123", type = 1, lastlogindt = now ()', 'and tbid = 7 ');
Echo'
Array mode, which can be passed into an array
';
$ Set = array ('username = "123" ', 'type = 1', 'lastlogindt = now ()');
$ Where = array ('tbid = 1 ');
$ Db-> update (1, 0, 'TB _ member ', $ set, $ where );

Echo' Delete test';
Echo 'normal mode, direct string input
';
$ Db-> delete (1, 0, 'TB _ member ',' and tbid = 1 and username = "hooray "');
Echo'
Array mode, which can be passed into an array
';
$ Where = array (
'Tbid = 1 ',
'Username = "hooray "'
);
$ Db-> delete (1, 0, 'TB _ member ', $ where );

Echo' Custom SQL';
$ Db-> query ('select username, password from tb_member ');
$ Rs = $ db-> fetchAll ();
Var_dump ($ rs );

$ Db-> close ();

Author: Hu ?? X