Databases Reference
In-Depth Information
$
php -q test.php -i products ipod
Query 'ipod ' retrieved 3 of 3 matches in 0.010 sec.
Query stats:
'ipod' found 3 times in 3 documents
Matches:
1. doc_id=123, weight=100, cat_id=100, price=159.99, added_ts=2008-01-03 22:38:26
2. doc_id=124, weight=100, cat_id=100, price=199.99, added_ts=2008-01-03 22:38:26
3. doc_id=125, weight=100, cat_id=100, price=249.99, added_ts=2008-01-03 22:38:26
The final step is to add searching to our web application. We need to set sorting and
filtering options based on user input and format the output nicely. Also, because Sphinx
returns only document IDs and configured attributes to the client—it doesn't store any
of the original text data—we need to pull additional row data from MySQL ourselves:
1 <?php
2 include ( "sphinxapi.php" );
3 // ... other includes, MySQL connection code,
4 // displaying page header and search form, etc. all go here
5
6 // set query options based on end-user input
7 $cl = new SphinxClient ();
8 $sortby = $_REQUEST["sortby"];
9 if ( !in_array ( $sortby, array ( "price", "added_ts" ) ) )
10 $sortby = "price";
11 if ( $_REQUEST["sortorder"]=="asc" )
12 $cl->SetSortMode ( SPH_SORT_ATTR_ASC, $sortby );
13 else
14 $cl->SetSortMode ( SPH_SORT_ATTR_DESC, $sortby );
15 $offset = ($_REQUEST["page"]-1)*$rows_per_page;
16 $cl->SetLimits ( $offset, $rows_per_page );
17
18 // issue the query, get the results
19 $res = $cl->Query ( $_REQUEST["query"], "products" );
20
21 // handle search errors
22 if ( !$res )
23 {
24 print "<b>Search error:</b>" . $cl->GetLastError ();
25 die;
26 }
27
28 // fetch additional columns from MySQL
29 $ids = join ( ",", array_keys ( $res["matches"] );
30 $r = mysql_query ( "SELECT id, title FROM products WHERE id IN ($ids)" )
31 or die ( "MySQL error: " . mysql_error() );
32 while ( $row = mysql_fetch_assoc($r) )
33 {
34 $id = $row["id"];
35 $res["matches"][$id]["sql"] = $row;
36 }
37
38 // display the results in the order returned from Sphinx
39 $n = 1 + $offset;
40 foreach ( $res["matches"] as $id=>$match )
