Brand new PDO database operation class (for Mysql only)

1 years ago, almost just opened the Bo that will share a pdo database operation class (can see: http://www.cnblogs.com/hooray/archive/2011/06/30/2094743.html), not so much is the class, in fact, just a few encapsulated functions, the overall slightly immature, but it also took a 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.

/*** Author: Hu Rui * Date: * Email: hooray0905@foxmail.com */class HRDB {protected $ pdo; protected $ res; protected $ config; /* 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);}/* close the database */public function () {$ 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 it is enabled, the output SQL statement * 0 is not enabled * 1 is enabled * 2 The Program * int $ mode returns the type * 0 multiple records * 1 returns a single record * 2 returns the number of rows * string/array $ table database table, two value transfer modes: * Normal Mode: * 'tb _ member, tb_money '* array mode: * array ('tb _ member', 'tb _ money ') * string/array $ fields: Specifies the database field to be queried. The value can be blank. The default value is to search for all fields. Two value passing modes: * Common Mode: * 'username, password' * array mode: * array ('username', 'Password') * string/array $ sqlwhere query condition, which can be null. Two value passing modes * Common Mode: * 'and type = 1 and username like "% OS %"' * array mode: * array ('Type = 1', 'username like "% OS % "') * string $ orderby sorting. The default value is id Reverse Order */public function select ($ debug, $ mode, $ table, $ fields = "*", $ sqlwhere = "", $ orderby = "tbid desc") {// process the parameter 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 ;}}} /*** parameter description * Whether int $ debug is enabled, if the SQL statement is enabled, * 0 is not enabled. * 1 is enabled. * 2. The program is enabled. * int $ mode. The returned type is * 0. No information is returned. * 1. The number of execution items is returned. * 2. The last time is returned. insert Record id * string/array $ table database table, two value transfer 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: * 'username = "test", type = 1, dt = now () '* array mode: * array ('username = "test"', 'Type = 1', 'dt = now () ') */public function insert ($ debug, $ mode, $ table, $ set) {// process the parameter 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 ;}}} /*** parameter description * Whether int $ debug is enabled, when the SQL statement is enabled, the output SQL statement * 0 is disabled. * 1 is enabled. * 2 is enabled and the Program * int $ mode is terminated. The returned type is * 0. No information is returned. * 1. The number of execution items is returned. * string $ table database. table, two value transfer 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: * Common Mode: * 'username = "test", type = 1, dt = now () '* array mode: * array ('username = "test"', 'Type = 1', 'dt = now () ') * string/array $ sqlwhere condition modification, null is allowed. Two value transmission modes are available: * 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 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 ;}}} /*** parameter description * Whether int $ debug is enabled, when the SQL statement is enabled, the output SQL statement * 0 is disabled. * 1 is enabled. * 2 is enabled and the Program * int $ mode is terminated. The returned type is * 0. No information is returned. * 1. The number of execution items is returned. * string $ table database. table * string/array $ sqlwhere deletion condition, null is allowed. Two value transmission modes are available: * 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 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, 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.

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.

insert into tb_member set username = "test", type = 1, lastlogindt = now()

Just like update.

　　② Some parameters can be replaced by arrays.

For example

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.

$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)

$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.

$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.

$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);

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.

Require_once ('Global. php '); require_once ('inc/setting. inc. php '); $ db = new HRDB ($ db_hoorayos_config); echo'