Whats the best way to retrieve information from Sphinx (in PHP)?

Question

I'm new to sphinx, and I'm seting it up on a new website. It's working fine, and when i search with the search in the console, everything work.

Using the PHP api and the searched, gives me the same results as well. But it gives me only ids and weights for the rows found. Is there some way to bring some text fields togheter with the 'matches' hash, for example?

If there is no way to do this, does anyone have a good idea about how to retrieve the records from the database (sql) in the sphinx weight sort order (searching all them at the same time)?

Answer 1

Hello, unfortually sphinx didn't returns matched fields, only its ids (sphinx index didn't contains data - only hash from data). Post about this issue you can find on the sphinxsearch.com forum.

Answer 2

As Alex says, Sphinx doesn't return that information. You will have to use the IDs to query the database yourself - just loop through each ID, get your relevant data out, keeping the results in weighting order. To do it all in one query, you could try something like the following (psuedo-code - PHP ain't my language of choice):

results = db.query("SELECT * FROM table WHERE id IN (%s)", matches.join(", "));
ordered_results = [];

for (match in matches) {
  for (result in results) {
    if (result["id"] == match) {
      ordered_results << result;
    }
  }
}

return ordered_results;

Answer 3

Yeah, sphinx doesn't bring the results. But I found out a simple way to reorder the query using the IN() clause, to bring all together.

Quering something

SELECT * FROM table WHERE id IN(id_list... )

just indexing the result, with their id in the table:

while ($row = mysql_fetch_objects)
   $result[$row->id] = $row;

and having the matching results from sphinx, its very easy to reorder:

$ordered_result = array();
foreach ($sphinxs_results['matches'] as $id => $content)
   $ordered_result[] = $result1[$id];

this shall work, if your $sphinxs_results are in the correct order.

its almost pat's answer, but with less one loop. Can make some diference in big results, I guess.

Answer 4

You can use a mysql FIELD() function call in your ORDER BY to ensure everything is in the order sphinx specified.

$idlist = array();
foreach ( $sphinx_result["matches"] as $id => $idinfo ) {
    $idlist[] = "$id";
}
$ids = implode(", ", $idlist);

SELECT * FROM table WHERE id IN ($ids) ORDER BY FIELD(id, $ids)

Answer 5

I wanted to know how to do this in php as well. Thanks for the answers.