How to use PDO bindParam for SphinxSearch 3?

Question

I have recently installed SphinxSearch 3.1.1 on Ubuntu 18 and am currently creating a snippet of code using PHP 7.2. I am having trouble making bindParam work.

Here is my code:

$mytest = "hello";
$query = $conn->prepare("SELECT *, weight() FROM test WHERE MATCH('\"@title :mytest \"/1')");
$query->bindParam(':mytest', $mytest, PDO::PARAM_STR);
$query->execute();

When I try to execute it, result is empty.

However, when I try to directly put $mytest inside the statement, I get the expected result

$query = $conn->prepare("SELECT *, weight() FROM test WHERE MATCH('\"@title". $mytest ."\"/1')");

Does this mean, sphinx does not support bindParam within the MATCH() function? Or am I missing something else here.

Answer 1

Note from http://php.net/manual/en/pdo.prepare.php

Parameter markers can represent a complete data literal only. Neither part of literal, nor keyword, nor identifier, nor whatever arbitrary query part can be bound using parameters.

ie you trying to bind inside a string literal. In the practical sense PHP (or the mysql server) wll add the ' around the value to make the statement.

... In effect would end up with sphinxql query of:

SELECT *, weight() FROM test WHERE MATCH('"@title 'hellow' "/1')

which is clearly wrong!

---

Instead bind the whole string literal.

$query = $conn->prepare("SELECT *, weight() FROM test WHERE MATCH(:mytest)");
$query->bindParam(':mytest',  '"@title '.$mytest.'"/1', PDO::PARAM_STR);

Although that looks like an invalid Sphinx 'extended syntax' query, The @field qualifer, should be outside the quorum,

$query->bindParam(':mytest',  '@title "'.$mytest.'"/1', PDO::PARAM_STR);