Sphinx search query by PDO

Question

$dsn="mysql:host=127.0.0.1; dbname=test; charset=utf8;";
$username="member";

$password="";
$options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8');

$db=new PDO($dsn, $username, $password, $options);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
$db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

$query=$db->prepare("   
    SELECT *
    FROM test WHERE MATCH (name, location) AGAINST (:search IN BOOLEAN MODE)
    LIMIT 0, 10
");
$query->bindValue(':search', $search, PDO::PARAM_STR);  
$query->execute();
while($data=$query->fetch(PDO::FETCH_ASSOC)){

}

I have a mysql query using PDO for fulltext search, I'm currently installed sphinx search.

My question is how to query through Sphinx search's index and fetch out data from Mysql by using PHP PDO?

score 2 · Answer 1 · edited May 23 '17 at 12:25

You need two PDO connections. One to the SphinxSearch Server (sphinx talks SphinxQL, which is binary protocol compatible with mysql, so works with PDO). And another to the Mysql Server.

Run the SphinxQL query via the SPhinx onnections. Get a list of IDs.

Plug that list of ids, into a MySQL Query - to get the actual row data. Use the IN(..) filter function.

PDO IN() Array Statement AND a placeholder may be useful

Sphinx search query by PDO

1 Answers1