Jump to content

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


Recommended Posts

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
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
Link to post
Share on other sites
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
Link to post
Share on other sites
  • 3 weeks later...

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.

Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.