SQLite FTS5 through PHP/PDO – How to bind values while filtering on a column name?

In a FTS5 MATCH clause, column names used as filters are declared with an ending colon, like in:

... WHERE ftstable MATCH 'colname: keyword'

(as per https://sqlite.org/fts5.html#fts5_column_filters)

When I try to declare ‘keyword’ as a bound value, like in:

$sql = "SELECT * FROM ftstable WHERE ftstable MATCH 'colname: :keyword'";
$stmt = $pdo->prepare($sql);
$stmt->execute(array(':keyword' => 'keyword'));

I get the following error:

SQLSTATE[HY000]: General error: 1 unrecognized token: ":"

because of the colon following the column name.

I get the same error using alternate syntax (? placeholders, bindValue(), etc.).

Does anyone know of a workaround here? Or am I missing something obvious? Many thanks.

Answer

You can’t have parameters in string literals; there’s no interpolation done looking for them. You can, however, build an argument to MATCH using string concatenation:

$sql = "SELECT * FROM ftstable WHERE ftstable MATCH ('colname: ' || :keyword)";

Advertisement

Answer