Используйте связанный параметр несколько раз
Я пытаюсь реализовать довольно простую поисковую систему для своей базы данных, в которой пользователь может включать различные виды информации. Сам поиск состоит из пары вариантов объединения, где результаты всегда объединяются в 3 столбца.
Однако возвращаемые данные извлекаются из разных таблиц.
Каждый запрос использует $term для подбора партнеров, и я привязал его к ":term" в качестве подготовленного параметра.
Теперь в руководстве говорится:
Вы должны включите уникальный маркер параметра для каждого значения, которое вы хотите передать в инструкцию при вызове PDOStatement::execute(). Нельзя дважды использовать маркер именованного параметра с одним и тем же именем в подготовленной инструкции.
Я подумал, что вместо замены каждого параметра :term на :termX (x для термина = n++) должно быть лучшее решение?
Или мне просто нужно привязать X число:termX?
Редактировать Публикую свое решение по этому вопросу:
$query = "SELECT ... FROM table WHERE name LIKE :term OR number LIKE :term";
$term = "hello world";
$termX = 0;
$query = preg_replace_callback("/\:term/", function ($matches) use (&$termX) { $termX++; return $matches[0] . ($termX - 1); }, $query);
$pdo->prepare($query);
for ($i = 0; $i < $termX; $i++)
$pdo->bindValue(":term$i", "%$term%", PDO::PARAM_STR);
Хорошо, вот пример. У меня нет времени на sqlfiddle, но я добавлю его позже, если это будет необходимо.
(
SELECT
t1.`name` AS resultText
FROM table1 AS t1
WHERE
t1.parent = :userID
AND
(
t1.`name` LIKE :term
OR
t1.`number` LIKE :term
AND
t1.`status` = :flagStatus
)
)
UNION
(
SELECT
t2.`name` AS resultText
FROM table2 AS t2
WHERE
t2.parent = :userParentID
AND
(
t2.`name` LIKE :term
OR
t2.`ticket` LIKE :term
AND
t1.`state` = :flagTicket
)
)
5 answers
Я уже пару раз сталкивался с одной и той же проблемой и думаю, что нашел довольно простое и хорошее решение. В случае, если я хочу использовать параметры несколько раз, я просто сохраняю их в MySQL User-Defined Variable
.
Это делает код намного более читабельным, и вам не нужны никакие дополнительные функции в PHP:
$sql = "SET @term = :term";
try
{
$stmt = $dbh->prepare($sql);
$stmt->bindValue(":term", "%$term%", PDO::PARAM_STR);
$stmt->execute();
}
catch(PDOException $e)
{
// error handling
}
$sql = "SELECT ... FROM table WHERE name LIKE @term OR number LIKE @term";
try
{
$stmt = $dbh->prepare($sql);
$stmt->execute();
$stmt->fetchAll();
}
catch(PDOException $e)
{
//error handling
}
Единственным недостатком может быть то, что вам нужно выполнить дополнительный запрос MySQL, но имхо это того стоит.
Поскольку User-Defined Variables
привязаны к сеансу в MySQL, также нет необходимости беспокоиться о переменной @term
, вызывающей побочные эффекты в многопользовательских средах.
Я создал две функции для решения проблемы путем переименования дважды используемых терминов. Один для переименования SQL и один для переименования привязок.
/**
* Changes double bindings to seperate ones appended with numbers in bindings array
* example: :term will become :term_1, :term_2, .. when used multiple times.
*
* @param string $pstrSql
* @param array $paBindings
* @return array
*/
private function prepareParamtersForMultipleBindings($pstrSql, array $paBindings = array())
{
foreach($paBindings as $lstrBinding => $lmValue)
{
// $lnTermCount= substr_count($pstrSql, ':'.$lstrBinding);
preg_match_all("/:".$lstrBinding."\b/", $pstrSql, $laMatches);
$lnTermCount= (isset($laMatches[0])) ? count($laMatches[0]) : 0;
if($lnTermCount > 1)
{
for($lnIndex = 1; $lnIndex <= $lnTermCount; $lnIndex++)
{
$paBindings[$lstrBinding.'_'.$lnIndex] = $lmValue;
}
unset($paBindings[$lstrBinding]);
}
}
return $paBindings;
}
/**
* Changes double bindings to seperate ones appended with numbers in SQL string
* example: :term will become :term_1, :term_2, .. when used multiple times.
*
* @param string $pstrSql
* @param array $paBindings
* @return string
*/
private function prepareSqlForMultipleBindings($pstrSql, array $paBindings = array())
{
foreach($paBindings as $lstrBinding => $lmValue)
{
// $lnTermCount= substr_count($pstrSql, ':'.$lstrBinding);
preg_match_all("/:".$lstrBinding."\b/", $pstrSql, $laMatches);
$lnTermCount= (isset($laMatches[0])) ? count($laMatches[0]) : 0;
if($lnTermCount > 1)
{
$lnCount= 0;
$pstrSql= preg_replace_callback('(:'.$lstrBinding.'\b)', function($paMatches) use (&$lnCount) {
$lnCount++;
return sprintf("%s_%d", $paMatches[0], $lnCount);
} , $pstrSql, $lnLimit = -1, $lnCount);
}
}
return $pstrSql;
}
Пример использования:
$lstrSqlQuery= $this->prepareSqlForMultipleBindings($pstrSqlQuery, $paParameters);
$laParameters= $this->prepareParamtersForMultipleBindings($pstrSqlQuery, $paParameters);
$this->prepare($lstrSqlQuery)->execute($laParameters);
Объяснение именования переменных:
p: параметр, l: локальная функция
str: строка, n: числовой, a: массив, m: смешанный
Я не знаю, изменилось ли это с момента публикации вопроса, но, проверяя руководство сейчас, там написано:
Нельзя использовать маркер именованного параметра с одним и тем же именем более одного раза в подготовленном операторе, если только не включен режим эмуляции.
Http://php.net/manual/en/pdo.prepare.php -- (Курсив мой.)
Таким образом, технически, разрешение эмулированной подготовки с помощью $PDO_obj->setAttribute( PDO::ATTR_EMULATE_PREPARES, true );
тоже будет работать; хотя это может быть не очень хорошей идеей (как обсужденный в этом ответе, отключение эмулированных подготовленных инструкций является одним из способов защиты от определенных атак с использованием инъекций; хотя некоторые написали об обратном, что для безопасности не имеет значения, эмулируются ли подготовленные инструкции или нет. (Я не знаю, но я не думаю, что последний имел в виду первое упомянутое нападение.)
Я добавляю этот ответ для полноты картины; поскольку я отключил emulate_prepares на сайте, над которым я работаю, и это вызвало поиск, чтобы прерваться, так как он использовал аналогичный запрос (SELECT ... FROM tbl WHERE (Field1 LIKE :term OR Field2 LIKE :term) ...
), и он работал нормально, пока я явно не установил PDO::ATTR_EMULATE_PREPARES
в false
, затем он начал давать сбой.
(PHP 5.4.38, MySQL 5.1.73 FWIW)
Этот вопрос подсказал мне, что вы не можете использовать именованный параметр дважды в одном и том же запросе (что кажется мне нелогичным, но ладно). (Почему-то я пропустил это в руководстве, хотя много раз просматривал эту страницу.)
Рабочее решение:
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);
$query = "SELECT * FROM table WHERE name LIKE :term OR number LIKE :term";
$term = "hello world";
$stmt = $pdo->prepare($query);
$stmt->execute(array('term' => "%$term%"));
$data = $stmt->fetchAll();
Пользовательские переменные - это один из способов использовать одну и ту же переменную несколько раз для привязки значений к запросам, и да, это хорошо работает.
//Setting this doesn't work at all, I tested it myself
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);
Я вообще не хотел использовать пользовательские переменные, как одно из решений, опубликованных здесь. Я также не хотел переименовывать параметры, как и другое решение, опубликованное здесь. Итак, вот мое решение, которое работает без использования пользовательских переменных и без переименования чего-либо в вашем запросе с меньшим количеством кода и ему все равно, сколько раз параметр используется в запросе. Я использую это во всех своих проектах, и это хорошо работает.
//Example values
var $query = "select * from test_table where param_name_1 = :parameter and param_name_2 = :parameter";
var param_name = ":parameter";
var param_value = "value";
//Wrap these lines of codes in a function as needed sending 3 params $query, $param_name and $param_value.
//You can also use an array as I do!
//Lets check if the param is defined in the query
if (strpos($query, $param_name) !== false)
{
//Get the number of times the param appears in the query
$ocurrences = substr_count($query, $param_name);
//Loop the number of times the param is defined and bind the param value as many times needed
for ($i = 0; $i < $ocurrences; $i++)
{
//Let's bind the value to the param
$statement->bindValue($param_name, $param_value);
}
}
И вот простое рабочее решение!
Надеюсь, это поможет кому-нибудь в ближайшем будущем.