Predictive Search Guidance

PHP
1

I want to combine content from several websites into one central “encyclopedia.” So I’m going to be querying several database tables simultaneously. I’d love to add a search function similar to Wikipedia’s.

For example, if you type in lion, it displays all the database values that begin with lion, from lion to Lion King. Below that it displays values that include the term lion (e.g. ant lion).

Can anyone tell me how to do that, or point me to a good tutorial or script I can download? The term for this kind of thing is apparently “predictive search.” I’ve been looking for information on Google, but I haven’t found anything really helpful yet.

I found a jQuery script that handles the autocomplete/autofill function @ https://jqueryui.com/autocomplete/

So I guess the next step is to modify my query (below) to make it work with the jQuery script. But I’m not sure what comes next, or if I’m even on the right track. Thanks.

$stm = $pdo->prepare("SELECT GS.URL, GS.Title, 'GS' as Site, 'World' as Section
 FROM gs_world GS
 WHERE GS.URL = :MyURL
 UNION ALL
 SELECT G.URL, G.Title, 'GW' as Site, 'World' as Section
 FROM gw2_pol G
 WHERE G.URL = :MyURL");
 $stm->execute(array(
  'MyURL'=>$MyURL,
 ));

while ($row = $stm->fetch())
{
 $URL = $row['URL'];
 $Title = $row['Title'];
 $Site = $row['Site'];
 $Section = $row['Section'];
}
2

While MySQL and relational databases provide basic search functionality it scales poorly and is limited with todays needs such as; faceted search and stemming. As an alternative with less of a learning curve than others I highly recommend checking out Sphinx. Sphinx actually works alongside MySQL to provide a more than adequate search system without going bull blown to something like Solr which is more difficult to set-up and has less sensible defaults. Sphinx also has open source integrations with many popular languages such as; php. Solr has this all as well but is all around much more a pita to set-up. Using MySQL full text indexes or worse like is riddled with limitations and negative consequences. I would recommend avoiding MySQL altogether for searching unless this is just a quick a dirty job with limited functionality. If you were to use MySQL though you would want to use full text indexes to allow full text searching not just a LIKE condition as that is VERY limited in function and scales poorly.

1 Like
3

Wow, I’ve never even heard of Sphinx before. I’ll check it out. Thanks.

4

We recently replaced our Magento stores MySQL full text search with Sphinx.Originally we were going to use Solr but opted for something simpler which lead us to Sphinx. A week later our search drop off rate decreased by 10% with very little work outside of purchasing, installing, and configuring a Magento extension. Sphinx provides some nice sensible default behavior without investing a tone of time tuning. Not to mention you don’t need to run separate server using a java stack and worry about the hooking it all up with php which is even more a pain depending on what version of php you’re running. Sphinx just runs on python and piggybacks on top of MySQL. Super simple to install in comparison to Solr – especially on a LAMP stack.

1 Like
5

Also take a look at Elasticsearch. It is a very powerful search index system and relatively easy to use from a PHP perspective. Hmm… interesting. They’ve changed their name to Elastic.

Scott

1 Like
6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.