简单的PDO包装器 [英] Simple PDO wrapper
问题描述
我的网络应用程序目前已执行简单的查询:简单的CRUD操作,计数,...
几个月前,有人建议我在这里写一个简单的PDO包装器(为了避免在每次执行查询时写入try / catch,prepare(),execute()等)。显示了这个示例方法(我做了一些更改,所以我可以在我自己的项目中使用它):
public function execute (){
$ args = func_get_args();
$ query = array_shift($ args);
$ result = false;
try {
$ res = $ this-> pdo-> prepare($ query);
$ result = $ res-> execute($ args);
} catch(PDOException $ e){echo $ e-> getMessage(); }
return $ result;
}
由于我需要执行更多的操作(执行查询,检索1条记录,多个记录,计数结果)我为所有这些创建了一个方法:
public function getMultipleRecords(){
$ args = func_get_args();
$ query = array_shift($ args);
$ records = array();
try {
$ res = $ this-> pdo-> prepare($ query);
$ res-> execute($ args);
$ records = $ res-> fetchAll();
} catch(PDOException $ e){echo $ e-> getMessage(); }
return $ records;
}
public function getSingleRecord(){
$ args = func_get_args();
$ query = array_shift($ args);
$ record = array();
try {
$ res = $ this-> pdo-> prepare($ query);
$ res-> execute($ args);
$ record = $ res-> fetch();
} catch(PDOException $ e){echo $ e-> getMessage(); }
return $ record;
}
public function execute(){
$ args = func_get_args();
$ query = array_shift($ args);
$ result = false;
try {
$ res = $ this-> pdo-> prepare($ query);
$ result = $ res-> execute($ args);
} catch(PDOException $ e){echo $ e-> getMessage(); }
return $ result;
}
public function count(){
$ args = func_get_args();
$ query = array_shift($ args);
$ result = -1;
try {
$ res = $ this-> pdo-> prepare($ query);
$ res-> execute($ args);
$ result = $ res-> fetchColumn();
} catch(PDOException $ e){echo $ e-> getMessage(); }
return $ result;
}
正如你所看到的,大多数代码是一样的。只有两行代码对于每个方法是不同的:$ result(我总是希望返回一个值,即使查询失败)和获取的初始化。而不是使用4个方法,我可以只写一个,并传递一个额外的参数与动作的类型。这样,我可以使用一堆switch语句的if / else语句。但是,我认为代码可以弄乱。这是解决这个问题的好方法吗?
我有第二个问题(这就是为什么我现在正在处理这个类)是我想要的以使用准备的语句与LIMIT SQL语句。但是,这是不可能做到的:
$ res = $ pdo-> prepare(SELECT * FROM table LIMIT ?);
$ res-> execute(array($ int));
可变库将由于某种原因被引用(因此查询将失败),如下所述:
https://bugs.php.net/bug.php?id=40740
解决方案似乎使用bindValue()并使用int数据类型作为参数:
http://www.php.net/manual/de/pdostatement.bindvalue.php
我可以重写方法来支持这个,但我还需要使用一个额外的参数。我不能只使用 $ db-> execute($ sql,$ variable1,$ variable2);
不再需要知道数据类型。
感谢
如何使用您可以链接的方法创建类(为了清楚起见,我已删除错误检查):
class DB {
private $ dbh;
private $ stmt;
public function __construct($ user,$ pass,$ dbname){
$ this-> dbh = new PDO(
mysql:host = localhost; dbname = $ dbname,
$ user,
$ pass,
array(PDO :: ATTR_PERSISTENT => true)
);
}
公共函数查询($ query){
$ this-> stmt = $ this-> dbh-> prepare
return $ this;
}
public function bind($ pos,$ value,$ type = null){
if(is_null($ type)){
switch(true){
case is_int($ value):
$ type = PDO :: PARAM_INT;
break;
case is_bool($ value):
$ type = PDO :: PARAM_BOOL;
break;
case is_null($ value):
$ type = PDO :: PARAM_NULL;
break;
默认值:
$ type = PDO :: PARAM_STR;
}
}
$ this-> stmt-> bindValue($ pos,$ value,$ type);
return $ this;
}
public function execute(){
return $ this-> stmt-> execute();
}
public function resultet(){
$ this-> execute();
return $ this-> stmt-> fetchAll();
}
public function single(){
$ this-> execute();
return $ this-> stmt-> fetch();
}
}
您可以这样使用:
//建立连接。
$ db = new DB('user','password','database');
//创建查询,绑定值并返回单行。
$ row $ db-> query('SELECT col1,col2,col3 FROM mytable WHERE id>?LIMIT?')
- > bind(1,2)
- &bind; bind(2,1)
- > single();
//更新LIMIT并获取结果集。
$ db-> bind(2,2);
$ rs = $ db-> resultset();
//创建一个新查询,绑定值并返回一个结果集。
$ rs = $ db-> query('SELECT col1,col2,col3 FROM mytable WHERE col2 =?')
- > bind(1,'abc')
- > ; resultset();
//更新WHERE子句并返回结果集。
$ db-> bind(1,'def');
$ rs = $ db-> resultset();
您可以更改 bind
一个数组或关联数组,如果你喜欢,但我发现这个语法很清楚 - 它避免了构建一个数组。参数类型检查是可选的,因为 PDO :: PARAM_STR
适用于大多数值,但要注意传递空值时的潜在问题(请参阅在 PDOStatement-> bindValue
文档中提供)。
My web application currently has do execute simple queries: simple CRUD operations, counting,...
A few months ago, someone recommended me here to write a simple PDO wrapper for this (to avoid writing try/catch, prepare(), execute(), etc. each time a query should be executed). This example method was shown (I've made some changes so I could use it in my own project):
public function execute() {
$args = func_get_args();
$query = array_shift($args);
$result = false;
try {
$res = $this->pdo->prepare($query);
$result = $res->execute($args);
} catch (PDOException $e) { echo $e->getMessage(); }
return $result;
}
As I need to perform more operations (executing queries, retrieving 1 record, retrieving multiple records, counting results) I created a method for all of these:
public function getMultipleRecords() {
$args = func_get_args();
$query = array_shift($args);
$records = array();
try {
$res = $this->pdo->prepare($query);
$res->execute($args);
$records = $res->fetchAll();
} catch (PDOException $e) { echo $e->getMessage(); }
return $records;
}
public function getSingleRecord() {
$args = func_get_args();
$query = array_shift($args);
$record = array();
try {
$res = $this->pdo->prepare($query);
$res->execute($args);
$record = $res->fetch();
} catch (PDOException $e) { echo $e->getMessage(); }
return $record;
}
public function execute() {
$args = func_get_args();
$query = array_shift($args);
$result = false;
try {
$res = $this->pdo->prepare($query);
$result = $res->execute($args);
} catch (PDOException $e) { echo $e->getMessage(); }
return $result;
}
public function count() {
$args = func_get_args();
$query = array_shift($args);
$result = -1;
try {
$res = $this->pdo->prepare($query);
$res->execute($args);
$result = $res->fetchColumn();
} catch(PDOException $e) { echo $e->getMessage(); }
return $result;
}
As you see, most of the code is the same. Only 2 lines of code are different for each method: the initialisation of $result (I always want to return a value, even if the query fails) and the fetching. Instead of using 4 methods, I could write just one of them and pass an extra parameter with the type of action. That way, I could use a bunch of if/else statements of a switch statement. However, I think the code can get messy. Is this a good way for solving this problem? If not, what would be a good solution to it?
The second problem I have (which is why I'm working on this class right now) is that I want to use prepared statements with the LIMIT SQL statement. However, it is not possible to do this:
$res = $pdo->prepare("SELECT * FROM table LIMIT ?");
$res->execute(array($int));
The variabele will be quoted for some reason (and so the query will fail), as explained here: https://bugs.php.net/bug.php?id=40740
The solution seems to use bindValue() and use the int datatype as a parameter: http://www.php.net/manual/de/pdostatement.bindvalue.php
I could rewrite the method(s) to support this, but I would also need to use an extra parameter. I can't just use $db->execute($sql, $variable1, $variable2);
anymore as I need to know the data type.
What's the best way to solve this?
Thanks
How about creating a class with methods that you can chain (for clarity, I've removed error checking):
class DB {
private $dbh;
private $stmt;
public function __construct($user, $pass, $dbname) {
$this->dbh = new PDO(
"mysql:host=localhost;dbname=$dbname",
$user,
$pass,
array( PDO::ATTR_PERSISTENT => true )
);
}
public function query($query) {
$this->stmt = $this->dbh->prepare($query);
return $this;
}
public function bind($pos, $value, $type = null) {
if( is_null($type) ) {
switch( true ) {
case is_int($value):
$type = PDO::PARAM_INT;
break;
case is_bool($value):
$type = PDO::PARAM_BOOL;
break;
case is_null($value):
$type = PDO::PARAM_NULL;
break;
default:
$type = PDO::PARAM_STR;
}
}
$this->stmt->bindValue($pos, $value, $type);
return $this;
}
public function execute() {
return $this->stmt->execute();
}
public function resultset() {
$this->execute();
return $this->stmt->fetchAll();
}
public function single() {
$this->execute();
return $this->stmt->fetch();
}
}
You can then use it like this:
// Establish a connection.
$db = new DB('user', 'password', 'database');
// Create query, bind values and return a single row.
$row = $db->query('SELECT col1, col2, col3 FROM mytable WHERE id > ? LIMIT ?')
->bind(1, 2)
->bind(2, 1)
->single();
// Update the LIMIT and get a resultset.
$db->bind(2,2);
$rs = $db->resultset();
// Create a new query, bind values and return a resultset.
$rs = $db->query('SELECT col1, col2, col3 FROM mytable WHERE col2 = ?')
->bind(1, 'abc')
->resultset();
// Update WHERE clause and return a resultset.
$db->bind(1, 'def');
$rs = $db->resultset();
You could alter the bind
method to accept an array or associative array if you prefer, but I find this syntax quite clear - it avoids having to build an array. The parameter type checking is optional, as PDO::PARAM_STR
works for most values, but be aware of potential issues when passing null values (see comment in PDOStatement->bindValue
documentation).
这篇关于简单的PDO包装器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!