Sphinx returns empty results

0
votes

I have a project with Sphinx search engine.

Problem is that for direct search (when user enters his query and presses enter) it works excellent, but when I try implement autocomplete it returns zero results.

Sphinx version is 2.0.8 (I can't update it due to some reasons).

I use this code for autocompetion (taken from here)

<?php
$ln_sph = new PDO("mysql:host=127.0.0.1;port={$confs['port2']}",
            '',
            '',
            array
            (
                PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"  
            )
        );

$stmt = $ln_sph->prepare("SELECT * FROM ".implode(",", $used_indexes)." WHERE MATCH(':match') LIMIT 0,10 OPTION ranker=sph04");

$aq = explode(' ', $query);
if(mb_strlen($aq[count($aq) - 1]) < 3)
{
    $query = $query;
}
else
{
    $query = $query.'*';
}

$stmt->bindValue(':match', $query, PDO::PARAM_STR);         
$stmt->execute();
$products_ids = array();
foreach($stmt->fetchAll() as $data)
{
    //Reading data from sphinx              
}

If I connect to Sphinx directly from server via mysql

mysql -P10006 --protocol=tcp

and perform same query, e.g.:

SELECT * FROM `app_catalog_prod_suggests` WHERE MATCH('пирс*') LIMIT 0, 10 OPTION ranker=sph04;

it also works fine.

Index from sphinx.conf

index app_catalog_prod_suggests {
    source=app_catalog_prod_suggests
    path=/path/to/index
    enable_star=1
    min_word_len=3
    min_prefix_len=3
    morphology=stem_ru,stem_en,soundex
    charset_type=utf-8
    html_strip=1
}

What am I doing wrong?

P.S. I can provide additional data if needed

UPD. searchd section

searchd {
        listen=127.0.0.1:10005
        listen=127.0.0.1:10006:mysql41
        log=/path/to/log
        query_log=/path/to/querylog
        binlog_path = #
        pid_file=/path/to/pid
}
2
phppdosphinx
Show searchd section from the config and the value of $confs['port2']. Do you see any warning or error when you run the script? – Manticore Search
@Manticore-Sphinxnextgen question updated. Port is the same as in exmaple with mysql. No errors or warnings – Rulisp

2 Answers

1
votes

After some attempts, I've found the reason of this behaviour. It was not in Sphinx, but in PDO.

Firstly, I've tested if query is working at all, for example with some constant value - пирс*. And I was very suprised, that Sphinx returned me exactly what I need.

So, problem is not in Sphinx. But where then? There was only one line that can cause it - $stmt->bindValue(':match', $query, PDO::PARAM_STR);.

I've replaced bindValue with bindParam and it started working with any query.

I don't know the real reason of this behaviour and it would be great if somebody can explain it

UPD

After some additional tests, error was not in bindParam or bindValue, but in name of the parameter

0
votes

I thought you have to use that code

$indexes = 'simplecomplete';
$query = trim($_GET['term']);

$stmt = $ln_sph->prepare("SELECT * FROM $indexes WHERE MATCH(:match)  LIMIT $start,$offset OPTION ranker=sph04,field_weights=(title=100,content=1)");
$stmt->bindValue(':match', $query,PDO::PARAM_STR);
    $stmt->execute();
$rows = $stmt->fetchAll();
   $meta = $ln_sph->query("SHOW META")->fetchAll();
foreach($meta as $m) {
    $meta_map[$m['Variable_name']] = $m['Value'];
}

database connection

define ( "FREQ_THRESHOLD", 40 );
define ( "SUGGEST_DEBUG", 0);
define ( "LENGTH_THRESHOLD", 2 );
define ( "LEVENSHTEIN_THRESHOLD", 2 );
define ( "TOP_COUNT", 1 );
define ("SPHINX_20",false);
//database PDO
$ln = new PDO( 'mysql:host=127.0.0.1;dbname=test_database;charset=utf8', 'root', '' );

//Sphinx PDO
$ln_sph = new PDO( 'mysql:host=127.0.0.1;port=9306' );