Jump to content
nik_jain

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

Recommended Posts

Posted (edited)

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

Edited by requinix
removing bad styling

Share this post


Link to post
Share on other sites

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

  • Haha 1

Share this post


Link to post
Share on other sites

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.

  • Great Answer 1

Share this post


Link to post
Share on other sites
Posted (edited)
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.  

Edited by requinix
removing bad styling

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.