什么'；这是一种通过pdo多次绑定同一参数的更简洁的方法 - What's a more succinct way to bind the same parameter, multiple times, via pdo

我有一个MySQL查询字符串，其中包含许多未命名的占位符（?），每个占位符都接受相同的参数：

$sql = "SELECT DISTINCT A1.pagetitle, A1.content, A1.uri, A2.value, ".
           "CASE ".
               "WHEN A1.pagetitle LIKE ? THEN 1 ".
               "WHEN A1.content LIKE ? THEN 2 ".
               "WHEN A2.value LIKE ? THEN 3 ".
           "END AS rank ".
       "FROM mod_site_content A1 ".
       "LEFT JOIN mod_site_tmplvar_contentvalues A2 ".
           "ON A1.id = A2.contentid ".
           "AND A2.tmplvarid IN(15,17) ".
       "WHERE (pagetitle LIKE ? ".
           "OR content LIKE ? ".
           "OR value LIKE ?) ".
       "ORDER BY rank";
$stmt = $db->prepare($sql);
$searchq = "%".$searchq."%";

目前，我正在以一种似乎很冗长的方式重复绑定：

$stmt->bindParam(1, $searchq, PDO::PARAM_STR);
$stmt->bindParam(2, $searchq, PDO::PARAM_STR);
$stmt->bindParam(3, $searchq, PDO::PARAM_STR);
...etc

有没有一种更整洁、更可维护的方法可以将同一个参数绑定到任意数量的占位符？

编辑：php版本5.10.1

一个简单的循环就可以了。

foreach($i = 1; $i <= 3; ++$i) {
  $stmt->bindParam($i, $searchq, PDO::PARAM_STR);
}

或者，如果你有一些指数需要省略：

foreach($i in range(1,3,4,7)) {
  $stmt->bindParam($i, $searchq, PDO::PARAM_STR);
}

这样写查询怎么样？

SELECT DISTINCT A1.pagetitle, A1.content, A1.uri, A2.value, 
       (CASE WHEN A1.pagetitle LIKE vars.pattern THEN 1 
             WHEN A1.content LIKE vars.pattern THEN 2
             WHEN A2.value LIKE vars.pattern THEN 3
             . . .
       ) as val
FROM . . . CROSS JOIN
     (SELECT ? as pattern) vars
. . .

那么你只需要包含一次。

编辑：

您的查询如下：

SELECT DISTINCT A1.pagetitle, A1.content, A1.uri, A2.value,
       (CASE WHEN A1.pagetitle LIKE vars.pattern THEN 1
             WHEN A1.content LIKE vars.pattern THEN 2
             WHEN A2.value LIKE vars.pattern THEN 3
        END) AS rank
FROM mod_site_content A1 LEFT JOIN
     mod_site_tmplvar_contentvalues A2
     ON A1.id = A2.contentid AND A2.tmplvarid IN (15, 17) CROSS JOIN
     (SELECT ? as pattern) vars
WHERE (pagetitle LIKE vars.pattern OR content LIKE vars.pattern OR value LIKE vars.pattern);
ORDER BY rank;

您可以使用having:来简化此过程

SELECT DISTINCT A1.pagetitle, A1.content, A1.uri, A2.value,
       (CASE WHEN A1.pagetitle LIKE vars.pattern THEN 1
             WHEN A1.content LIKE vars.pattern THEN 2
             WHEN A2.value LIKE vars.pattern THEN 3
        END) AS rank
FROM mod_site_content A1 LEFT JOIN
     mod_site_tmplvar_contentvalues A2
     ON A1.id = A2.contentid AND A2.tmplvarid IN (15, 17) CROSS JOIN
     (SELECT ? as pattern) vars
HAVING rank > 0
ORDER BY rank;

然后，如果"模式"不使用通配符，您可以执行以下操作：

SELECT DISTINCT A1.pagetitle, A1.content, A1.uri, A2.value,
       field(vars.pattern, A1.pagetitle, A1.content, A2.value) AS rank
FROM mod_site_content A1 LEFT JOIN
     mod_site_tmplvar_contentvalues A2
     ON A1.id = A2.contentid AND A2.tmplvarid IN (15, 17) CROSS JOIN
     (SELECT ? as pattern) vars
HAVING rank > 0
ORDER BY rank;

在这一点上，您只有一次模式，所以如果您的模式没有通配符:，这应该有效

SELECT DISTINCT A1.pagetitle, A1.content, A1.uri, A2.value,
       field(?, A1.pagetitle, A1.content, A2.value) AS rank
FROM mod_site_content A1 LEFT JOIN
     mod_site_tmplvar_contentvalues A2
     ON A1.id = A2.contentid AND A2.tmplvarid IN (15, 17)
HAVING rank > 0
ORDER BY rank;

由于我还没有真正尝试过这个解决方案，因此有被否决的风险，我会尝试以下方法：

$sql = "SELECT DISTINCT A1.pagetitle, A1.content, A1.uri, A2.value, ".
           "CASE ".
               "WHEN A1.pagetitle LIKE CONCAT('%', :my_str, '%') THEN 1 ".
               "WHEN A1.content LIKE CONCAT('%', :my_str, '%') THEN 2 ".
               "WHEN A2.value LIKE CONCAT('%', :my_str, '%') THEN 3 ".
               ...etc
$pdo->bindValue(':my_str', $stuff);