Brand new PDO database operation class php version

Copy code The code is as follows:

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 into 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);
}

/*Database close*/
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 to enable debugging, if enabled, the sql statement will be output
* 0 Not enabled
* 1 Enable
* 2 Enable and terminate the program
* int $mode Return type
* 0 Return multiple records
* 1 Return a single record
* 2 Return the number of 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 Database fields to be queried, allowed to be empty, default is to find all, two value-passing modes
* Normal mode:
* 'username, password'
* Array mode:
* array('username', 'password')
* string/array $sqlwhere query conditions, empty allowed, two value-passing modes
* Normal mode:
* 'and type = 1 and username like "%os%"'
* Array mode:
* array('type = 1', 'username like "%os%"')
* string $orderby sorting, the default is id reverse order
*/
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 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
* int $debug Whether to enable debugging, if enabled, the sql statement will be output
* 0 Not enabled
* 1 Enable
* 2 Enable and terminate the program
* int $mode return type
* 0 No return information
* 1 Returns the number of execution entries
* 2 Returns 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 Fields and content to be inserted, two value-passing modes
* 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 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
* int $debug Whether to enable debugging, if it is enabled, the sql statement will be output
* 0 Not enabled
* 1 Enable
* 2 Enable and terminate the program
* int $mode Return type
* 0 No return information
* 1 Returns the number of execution entries
* 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 that need to be updated, two value transfer modes
* Normal mode:
* 'username = "test", type = 1, dt = now()'
* Array mode:
* array('username = "test"', 'type = 1', 'dt = now()')
* string/array $sqlwhere Modify the conditions, allow empty, two value-passing modes
* 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
* int $debug Whether to enable debugging, if it is enabled, the sql statement will be output
* 0 Not enabled
* 1 Enable
* 2 Enable and terminate the program
* int $mode Return type
* 0 No return information
* 1 Return the number of execution entries
* string $table database table
* string/array $sqlwhere Delete condition, allowed to be empty, two value-passing modes
* 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 use is not much different from the previous one. The purpose is to facilitate transplantation.

This rewrite has dealt with several issues:

　　① The insert statement is too complex, and errors are prone to occur in the correspondence between fields and values ​​

　Let’s take a look at the most common SQL insert statement

Copy code The code is as follows: insert into tb_member (username , type, dt) values ​​('test', 1, now())
　In the traditional mode, the fields and values ​​parameters are passed in separately, but it is necessary to ensure that the two parameters are passed in in the same order. This can easily lead to disordered ordering or missing parameters.

This time the problem has been modified, using the unique insert syntax of MySQL. The same function as above can be changed to this way of writing

Copy the code The code is as follows: insert into tb_member set username = "test", type = 1, lastlogindt = now()
　Just like update, it is clear at a glance.

　　② Some parameters can be replaced by arrays

　For example, this sql

Copy code The code is as follows: delete from tb_member where 1=1 and tbid = 1 and username = "hooray"
　When the method is originally called, it needs to be assembled manually Good where condition, the cost of this operation is very high, now you can completely use this form
Copy the code The code is as follows:
$where = array(
'tbid = 1',
'username = "hooray"'
);
$ db->delete(1, 0, 'tb_member', $where);

　 No matter how many conditions you have, it will not disrupt your thinking. Similarly, not only the where parameter, the set in update can also be in this form (see the complete source code for details)

Copy the code The 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 is too complex, making it impossible to use the methods provided in the class to assemble the sql statement. At this time, a function is needed, which is to directly pass in the sql statement I have assembled, execute it, and return information. Now, this function is also available

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

Isn’t it very similar? What is the original way to write pdo?

　④Support the creation of multiple database connections

　 The original one is just a database operation method, so it does not support multiple database connections. In implementation, you need to copy 2 identical files and modify some variables. The operation is really complicated. This problem is now solved.

Copy code The 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=hoorayos2',
'name'=> 'root',
'password'=>'hooray'
);
$db2 = new HRDB($db_hoorayos_config2);

In this way, 2 database connections can be created at the same time, which facilitates the interaction between the database and the database.

That’s about all the new features, the entire code is not much, welcome to read and understand. The following is the test code I wrote when writing, and it is also provided for everyone to learn.

Copy code The 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, pass the string directly in
';
$rs = $db->select(1, 0, 'tb_member', 'username, password', 'and type = 1 and username like "%os%"');
echo '
Array mode, you can pass in 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, directly pass the string in
';
$db->insert(1, 0, 'tb_member', 'username = "test", type = 1, lastlogindt = now()');
echo '
Array mode, can be passed in Array
';
$set = array('username = "test"', 'type = 1', 'lastlogindt = now()');
$db->insert(1, 0, 'tb_member', $set);

echo '

---

update test

---

';
echo 'Normal mode, directly pass the string in
';
$db->update(1, 0, 'tb_member', 'username = "123", type = 1, lastlogindt = now()', 'and tbid = 7');
echo '
Array mode, you can pass in 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 characters Pass the string in
';
$db->delete(1, 0, 'tb_member', 'and tbid = 1 and username = "hooray"');
echo '
Array mode, available Pass in the 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();