問題描述
我的 Web 應用程序目前確實執行了簡單的查詢:簡單的 CRUD 操作、計數、...
My web application currently has do execute simple queries: simple CRUD operations, counting,...
幾個月前，有人推薦我在這里為此編寫一個簡單的 PDO 包裝器(以避免每次應執行查詢時編寫 try/catch、prepare()、execute() 等).顯示了此示例方法(我進行了一些更改，以便可以在我自己的項目中使用它):
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;
}
由于我需要執行更多操作(執行查詢、檢索 1 條記錄、檢索多條記錄、計算結果)，我為所有這些操作創建了一個方法:
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;
}
如您所見，大部分代碼是相同的.每種方法只有 2 行代碼不同:$result 的初始化(我總是想返回一個值，即使查詢失敗)和獲取.我可以不使用 4 種方法，而是只編寫其中一種并傳遞一個帶有操作類型的額外參數.這樣，我可以使用 switch 語句的一堆 if/else 語句.但是，我認為代碼可能會變得混亂.這是解決這個問題的好方法嗎?如果沒有，有什么好的解決辦法?
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?
我遇到的第二個問題(這就是我現在正在研究這個類的原因)是我想將準備好的語句與 LIMIT SQL 語句一起使用.但是，這是不可能的:
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));
由于某種原因，變量將被引用(因此查詢將失敗)，如下所述:https://bugs.php.net/bug.php?id=40740
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
解決方案似乎使用 bindValue() 并使用 int 數據類型作為參數:http://www.php.net/manual/de/pdostatement.bindvalue.php
The solution seems to use bindValue() and use the int datatype as a parameter: http://www.php.net/manual/de/pdostatement.bindvalue.php
我可以重寫方法來支持這一點，但我還需要使用一個額外的參數.我不能再使用 $db->e??xecute($sql, $variable1, $variable2); 因為我需要知道數據類型.
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?
謝謝
推薦答案
如何使用可以鏈接的方法創建一個類(為了清楚起見，我已經刪除了錯誤檢查):
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();
如果您愿意，您可以更改 bind 方法以接受數組或關聯數組，但我發現此語法非常清晰 - 它避免了必須構建數組.參數類型檢查是可選的，因為 PDO::PARAM_STR 適用于大多數值，但請注意傳遞空值時的潛在問題(請參閱 PDOStatement->bindValue 文檔中的注釋).
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 包裝器的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！