Brand new PDO database operation php version (only applicable to Mysql)

Copy code The code is as follows:

 
/**
* Author: Hu Rui 
* Date: 2012/07/21 
* Email: hooray0905@foxmail.com 
*/ 

class HRDB{ 
protected $pdo; 
protected $res; 
protected $config; 

/*构造函数*/ 
function __construct($config){ 
$this->Config = $config; 
$this->connect(); 
} 

/*数据库连接*/ 
public function connect(){ 
$this->pdo = new PDO($this->Config['dsn'], $this->Config['name'], $this->Config['password']); 
$this->pdo->query('set names utf8;'); 
//把结果序列化成stdClass 
//$this->pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ); 
//自己写代码捕获Exception 
$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
} 

/*数据库关闭*/ 
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 it is enabled, the sql statement will be output 
* 0 Disable 
* 1 Enable 
* 2 Enable and terminate the program 
* int $ mode return type 
* 0 returns multiple records 
* 1 returns a single record 
* 2 returns the number of rows 
* string/array $table database table, two value-passing modes 
* normal Pattern: 
* 'tb_member, tb_money' 
* Array pattern: 
* array('tb_member', 'tb_money') 
* string/array $fields Database fields to be queried, allowed to be empty , the default is to search 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"){ 
//参数处理 
if(is_array($table)){ 
$table = implode(', ', $table); 
} 
if(is_array($fields)){ 
$fields = implode(', ', $fields); 
} 
if(is_array($sqlwhere)){ 
$sqlwhere = ' and '.implode(' and ', $sqlwhere); 
} 
//数据库操作 
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 it is enabled, the sql statement will be output 
* 0 Disable 
* 1 Enable 
* 2 Enable and terminate the program 
* int $ mode return type 
* 0 No return information 
* 1 Return the number of execution entries 
* 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 fields to be inserted and Content, 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){ 
//参数处理 
if(is_array($table)){ 
$table = implode(', ', $table); 
} 
if(is_array($set)){ 
$set = implode(', ', $set); 
} 
//数据库操作 
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 Disable 
* 1 Enable 
* 2 Enable and terminate the program 
* int $ mode return type 
* 0 No return information 
* 1 Return 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 contents that need to be updated, two value-passing modes 
* Normal mode: 
* 'username = "test", type = 1, dt = now()' 
* Array mode: 
* array('username = "test"', 'type = 1', 'dt = now()') 
* string/array $sqlwhere Modify 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=""){ 
//参数处理 
if(is_array($table)){ 
$table = implode(', ', $table); 
} 
if(is_array($set)){ 
$set = implode(', ', $set); 
} 
if(is_array($sqlwhere)){ 
$sqlwhere = ' and '.implode(' and ', $sqlwhere); 
} 
//数据库操作 
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 Disable 
* 1 Enable 
* 2 Enable and terminate the program 
* int $ mode return type 
* 0 no return information 
* 1 return number of execution entries 
* string $table database table 
* string/array $sqlwhere deletion condition, empty allowed, 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=""){ 
//参数处理 
if(is_array($sqlwhere)){ 
$sqlwhere = ' and '.implode(' and ', $sqlwhere); 
} 
//数据库操作 
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; 
} 
} 
} 
} 

其实使用上，和之前的相差不大，目的就是为了方便移植。

　　本次重写着重处理了几个问题：

　　① insert语句太复杂，fields与values对应容易出现误差

　　我们看下最常见的一句sql插入语句

复制代码代码如下:

insert into tb_member (username, type, dt) values ('test', 1, now())

　　在传统模式下，fields和values参数是分开传入的，但却要保证两者参数传入的顺序一致。这很容易导致顺序错乱或者漏传某个参数。

　　这次已经把问题修改了，采用了mysql独有的insert语法，同样是上面那功能，就可以换成这样的写法

复制代码代码如下:

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

　　就像update一样，一目了然。

　　② 部分参数可以用数组代替

　　比如这样一句sql

复制代码代码如下:

delete from tb_member where 1=1 and tbid = 1 and username = "hooray"

　　在原先调用方法的时候，需要手动拼装好where条件，这样操作的成本很高，现在完全可以用这种形式

复制代码代码如下:

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

Yes Sometimes, sql is too complex, making it impossible to use the methods provided in the class to assemble sql statements. At this time, a function is needed, which is to directly pass in the sql statement I have assembled to execute 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(); 

Is it very similar to the original writing method of pdo?

　④ Support the creation of multiple database connections

The original method does not support multiple database connections because it is just a database operation method. 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 for easy processing Database-to-database interaction.

There are roughly so many 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< ;hr>'; 
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, direct string transfer Enter
'; 
$db->insert(1, 0, 'tb_member', 'username = "test", type = 1, lastlogindt = now()'); 
echo '< ;br>Array mode, you can pass in an array
'; 
$set = array('username = "test"', 'type = 1', 'lastlogindt = now()'); 
 $db->insert(1, 0, 'tb_member', $set); 

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

Brand new PDO database operation php version (only applicable to Mysql)_PHP tutorial