PHP的PDO准备重复的变量 [英] php pdo prepare repetitive variables
问题描述
在编写pdo语句时,是否可以重复变量的值?我的意思是:
While writing a pdo statement, is it possible to repeat the value of a variable? I mean:
$query = "UPDATE users SET firstname = :name WHERE firstname = :name";
$stmt = $dbh -> prepare($query);
$stmt -> execute(array(":name" => "Jackie"));
请注意,我重复:name"名称持有者,而我仅提供一次值.我该如何进行这项工作?
Please note that I repeat the ":name" nameholder whereas I provide the value only once. How can I make this work?
推荐答案
简单的答案是:不能. PDO对准备好的语句使用了抽象,这有一定的局限性.不幸的是,这是一个问题,您必须使用类似的方法
The simple answer is: You can't. PDO uses an abstraction for prepared statements which has some limitations. Unfortunately this is one, you have to work-around using something like
$query = "UPDATE users SET firstname = :name1 WHERE firstname = :name2";
$stmt = $dbh -> prepare($query);
$stmt -> execute(array(":name1" => "Jackie", ":name2" => "Jackie"));
在某些情况下,例如使用某些版本的PDO/MySQL驱动程序模拟的准备好的语句,支持重复的命名参数;但是,这不应该依靠,因为它很脆弱(例如,它可能会使升级需要更多的工作).
In certain cases, such as emulated prepared statements with some versions of the PDO/MySQL driver, repeated named parameters are supported; however, this shouldn't be relied upon, as it's brittle (it can make upgrades require more work, for example).
如果您要支持命名参数的多个外观,则始终可以扩展PDO和PDOStatement (通过经典继承或(按组成)或PDOStatement,然后通过设置PDO::ATTR_STATEMENT_CLASS
属性将您的类设置为语句类.扩展的PDOStatement(或PDO::prepare
)可以提取命名的参数,查找重复项并自动生成替换项.它还将记录这些重复项.传递给命名参数时,bind和execute方法将测试该参数是否重复并将该值绑定到每个替换参数.
If you want to support multiple appearances of a named parameter, you can always extend PDO and PDOStatement (by classical inheritance or by composition), or just PDOStatement and set your class as the statement class by setting the PDO::ATTR_STATEMENT_CLASS
attribute. The extended PDOStatement (or PDO::prepare
) could extract the named parameters, look for repeats and automatically generate replacements. It would also record these duplicates. The bind and execute methods, when passed a named parameter, would test whether the parameter is repeated and bind the value to each replacement parameter.
注意:下面的示例未经测试,并且可能存在错误(某些与语句解析有关的错误在代码注释中指出).
Note: the following example is untested and likely has bugs (some related to statement parsing are noted in code comments).
class PDO_multiNamed extends PDO {
function prepare($stmt) {
$params = array_count_values($this->_extractNamedParams());
# get just named parameters that are repeated
$repeated = array_filter($params, function ($count) { return $count > 1; });
# start suffixes at 0
$suffixes = array_map(function ($x) {return 0;}, $repeated);
/* Replace repeated named parameters. Doesn't properly parse statement,
* so may replacement portions of the string that it shouldn't. Proper
* implementation left as an exercise for the reader.
*
* $param only contains identifier characters, so no need to escape it
*/
$stmt = preg_replace_callback(
'/(?:' . implode('|', array_keys($repeated)) . ')(?=\W)/',
function ($matches) use (&$suffixes) {
return $matches[0] . '_' . $suffixes[$matches[0]]++;
}, $stmt);
$this->prepare($stmt,
array(
PDO::ATTR_STATEMENT_CLASS => array('PDOStatement_multiNamed', array($repeated)))
);
}
protected function _extractNamedParams() {
/* Not actually sufficient to parse named parameters, but it's a start.
* Proper implementation left as an exercise.
*/
preg_match_all('/:\w+/', $stmt, $params);
return $params[0];
}
}
class PDOStatement_multiNamed extends PDOStatement {
protected $_namedRepeats;
function __construct($repeated) {
# PDOStatement::__construct doesn't like to be called.
//parent::__construct();
$this->_namedRepeats = $repeated;
}
/* 0 may not be an appropriate default for $length, but an examination of
* ext/pdo/pdo_stmt.c suggests it should work. Alternatively, leave off the
* last two arguments and rely on PHP's implicit variadic function feature.
*/
function bindParam($param, &$var, $data_type=PDO::PARAM_STR, $length=0, $driver_options=array()) {
return $this->_bind(__FUNCTION__, $param, func_get_args());
}
function bindValue($param, $var, $data_type=PDO::PARAM_STR) {
return $this->_bind(__FUNCTION__, $param, func_get_args());
}
function execute($input_parameters=NULL) {
if ($input_parameters) {
$params = array();
# could be replaced by array_map_concat, if it existed
foreach ($input_parameters as $name => $val) {
if (isset($this->_namedRepeats[$param])) {
for ($i=0; $i < $this->_namedRepeats[$param], ++$i) {
$params["{$name}_{$i}"] = $val;
}
} else {
$params[$name] = $val;
}
}
return parent::execute($params);
} else {
return parent::execute();
}
}
protected function _bind($method, $param, $args) {
if (isset($this->_namedRepeats[$param])) {
$result = TRUE;
for ($i=0; $i < $this->_namedRepeats[$param], ++$i) {
$args[0] = "{$param}_{$i}";
# should this return early if the call fails?
$result &= call_user_func_array("parent::$method", $args);
}
return $result;
} else {
return call_user_func_array("parent::$method", $args);
}
}
}
这篇关于PHP的PDO准备重复的变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!