PHP uses PDO to operate the database class (applicable to Mysql)

/*** Author: Hu Rui * Date: 2012/07/21 * Email: hooray0905@foxmail.com */ class HRDB{ protected $pdo; protected $res; protected $config;

/*Constructor*/ 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;'); //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 Disable * 1 Enable * 2 Enable and terminate the program * int $mode Return type * 0 Return multiple records * 1 Returns a single record * 2 Returns 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 Open 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 and contents 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); } //数据库操作 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 enabled, the sql statement will be output * 0 Not enabled * 1 Enable * 2 Open 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 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 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=""){ //参数处理 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 enabled, the sql statement will be output * 0 Not enabled * 1 Enable * 2 Open and terminate the program * int $mode Return type * 0 No return information * 1 Returns the number of execution entries * string $table database table * string/array $sqlwhere deletion 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=""){ //参数处理 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); Copy code 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)

$set = array('username = "123"', 'type = 1', 'lastlogindt = now()'); $where = array('tbid = 1' ); $db->update(1, 0, 'tb_member', $set, $where); Copy code ③ Customizable sql statements 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.

$db->query('select username, password from tb_member'); $rs = $db->fetchAll(); Copy code Is it very similar to the original writing method of pdo?

④ Supports creating multiple database connections Because the original method is just a database operation method, it does not support multiple database connections. In implementation, it is necessary to copy two identical files and modify some variables, which is really complicated. This problem is now 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);

Copy the code This way you can create 2 database connections at the same time to facilitate the interaction between database and database.

Test code (call example):

require_once('global.php'); require_once('inc/setting.inc.php');

$ db = new HRDB($db_hoorayos_config);

echo ' select test '; echo 'Normal mode, direct characters Pass in the string '; $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, pass the string directly in '; $db->insert(1, 0, 'tb_member', 'username = "test", type = 1, lastlogindt = now()'); echo ' 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 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, pass the string directly '; $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(); ?>

Copy code