CODEWITHSUNDEEP
phpmysqlpdo
verism
verism

Reputation: 1164

What's a more succinct way to bind the same parameter, multiple times, via pdo

I have a MySQL query string with a number of unnamed place-holders (?), each accepting the same parameter:

$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."%";

And currently I'm repeating the binds, in what seems like quite a verbose way:

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

Is there a tidier and more maintainable way of binding the same parameter to any number of placeholders?

EDIT: php version 5.10.1

Upvotes: 0

Views: 117

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269573

How about writing the query like this?

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
. . .

Then you only need to include it once.

EDIT:

Your query would look like this:

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;

You can simplify this by using 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;

And then, if the "patterns" are not using wildcards, you could just do:

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;

At this point, you only have the pattern once, so this should work if your pattern has no wildcards:

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;

Upvotes: 1

N.B.
N.B.

Reputation: 14060

With the risk of downvotes since I haven't really TRIED this solution, I'd try something along these lines: 

$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);

Upvotes: 0

knittl
knittl

Reputation: 265161

A simple loop would do.

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

Or, if you have some indices to be left out:

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

Upvotes: 1

Related Questions