PHP Symfony2 Doctrine PDO_PGSQL绑定变量

Question

I have this query in my PHP code:

select * from book where title ~* '\mkeyword'; /* \m matches only at the beginning of a word */

keyword is user input. How can I rewrite the query to use binding variables? When I do this:

select * from book where title ~* :keyword;

and then:

$stmt->bindValue('keyword', "\m".$keyword);

after prepare statement, I get \\m in the query and the query won't work the way I want.

Answer 1

For a parameter named :keyword, you must also use the same name - inluding colon, see Examples section of PDOStatement::bindValue

$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindValue(':calories', $calories, PDO::PARAM_INT);
$sth->bindValue(':colour', $colour, PDO::PARAM_STR);

So in your case, it should be

$stmt->bindValue(':keyword', "\m" . $keyword);

---

As an alternative, you can also give an array to PDOStatement::execute

$stmt->execute(array(':keyword' => "\m" . $keyword));