PDO. How to pass quote literal as part of query?

[nik_jain]

SQLITE has syntax like

' WHERE MATCH ( 'colname : "one two" )' 
 //My pdo sql query 
$sql .= MATCH 'colname : "?" )'; $pdo->bindValue(1,$text);`

But Pdo placeholders can't have quotes around them. So this does not work. I tried a million variations of the placeholder syntax "?" "" ? "" """ ? """ \" ? \" . But nothing works.

Errors I get : General error: 1 near "?" | 25 column index out of range . Also for this query : MATCH  ( names: ? AND categoryids: ? ) , the error is: `General error: 1 unrecognized token: ":"`

Would really love some help here.. Thanks

[Barand]

Post code we can read instead of redacted text and someone might look at it.

[salathe]

From the docs:

Quote

If using the MATCH or = operators, the expression to the left of the MATCH operator is usually the name of the FTS5 table (the exception is when specifying a column-filter). The expression on the right must be a text value specifying the term to search for.

This means that the expression you want to be matched is a single, text (string) value.  If you're using a prepared statement, in PHP, with PDO, that means a single question mark (?) or a single ":name" placeholder if you're going for named parameters.

A quick demo example:

<?php

$db = new PDO("sqlite::memory:");
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

$db->exec("CREATE VIRTUAL TABLE example USING FTS5 (a, b)");
$db->exec("INSERT INTO example (a, b) VALUES ('this is an amazing message', 'kittens are lovely')");
$db->exec("INSERT INTO example (a, b) VALUES ('this is a great message', 'dogs are also lovely')");
$db->exec("INSERT INTO example (a, b) VALUES ('this is a message', 'hamsters are best')");

$query = $db->prepare('SELECT * FROM example WHERE example MATCH ?');
$query->execute(array('(b: kittens) OR (a: great)'));
$rows = $query->fetchAll();

print_r($rows);

The above example outputs the two matching rows.

Array
(
    [0] => Array
        (
            [a] => this is an amazing message
            [b] => kittens are lovely
        )

    [1] => Array
        (
            [a] => this is a great message
            [b] => dogs are also lovely
        )

)

 

Hope that helps.

[gizmola]

On 3/7/2019 at 9:18 AM, nik_jain said:

SQLITE has syntax like

' WHERE MATCH ( 'colname : "one two" )' 
 //My pdo sql query 
$sql .= MATCH 'colname : "?" )'; $pdo->bindValue(1,$text);`

But Pdo placeholders can't have quotes around them. So this does not work. I tried a million variations of the placeholder syntax "?" "" ? "" """ ? """ \" ? \" . But nothing works.

Errors I get : General error: 1 near "?" | 25 column index out of range . Also for this query : MATCH  ( names: ? AND categoryids: ? ) , the error is: `General error: 1 unrecognized token: ":"`

Would really love some help here.. Thanks

Nik,

This forum has excellent support for code snippets.  Just use the <> in the editor, for future reference.  

[nik_jain]

This is late. SOrry my question wasn't clear. But I found that in this instance prepared statements cannot be used. The SQLITE syntax required literal that PDO driver could not just pass and there was no way of escaping. Note I could escape the parameter passing it from PHP to PDO . But could not escape it from PDO to sqlite.

[requinix]

Posts edited.

I don't see anything that says MATCH arguments cannot be sent through a prepared statement. What is the full code you have now regarding that query?