Advanced search functionality

Question

I have a website with a jQuery based autocomplete search functionality which works great.

Currently though I have just one search box for all categories, what I want is for someone to be able to type in, say for example, dorian gray dvd (in any order) which will search for dorian gray within the dvd category. What this will require then is a bit of magic on the server side to figure out if any of the words are category keywords, and then limit the search by that.

What is the best (and quickest) way to do this in PHP / MySQL?

I currently have a few trains of thought

• Search the category table for matches and perhaps order the results by that.
• Or split up the search terms into an array and separately search the categories for that for a match.
• Another thought I just had is to concat the category title to the dvd title in the database and match against that, or something similar... but this sounds computationally expensive?

Any advice?

Answer 1

Your first idea is probably the quickest as far as loops and lines of code goes. explode the search query, wrap each part of the new array to format it so you can implode it and append the new string right on the end of your mysql query.

Then just fetch the result and append it to the search query. To save space format the search query at the same time as the categories query.

$temp=explode(" ", $string);
foreach($temp as $key=>$var) {
   $where[$key]=>"category='".mysql_real_escape_string($var)."'";
   $where2[$var]=>"field like '%".mysql_real_escape_string($var)."%'"; //formating the second query at the same time
}
$result=mysql_query("SELECT name FROM categories WHERE ".implode(" OR ", $where));
$cat=mysql_fetch_object($result)->name;
unset($where2['cat']); //removing the category from the search
$where2=implode(" OR ", $where2);
$result=mysql_query("SELECT * FROM table WHERE ($where2) and category='$cat'");

Answer 2

IN() comes in handy here. There are several methods, they vary in complexity and performance I'll assume that you have a Categories table, and that whatever uses the category uses it by the category identifier, like so:

categories
    id
    name

posts
    id
    category
    name

This function also removes the keywords that match a category-- as you described. It also allows you to search multiple categories. It leaves your search implementation up to you, though i discourage using LIKE "%keyword%" as it really hits performance.

Use IN keyword

$keywords = $escaped_keywords = explode(' ', $user_input);
array_walk($escaped_keywords, 'db_escape');
$query = 'SELECT id, name FROM categories WHERE name IN ("' . implode('", "', $escaped_keywords) . ")";
$result = db_query($query);
$category_in = '';
while($row = db_fetch($result)) {
    $category_in .= ($category ? ', ' : '') . db_escape($row['id']);
    unset($keywords[$row['name']))
}
if($category_in) {
    // $where is your conditional from your current script
    $where .= ($where ? ' AND ' : '') . "category IN($category_in)";
}

Build an search index instead

Another solution is drastically more complicated as it requires making a search index. A search index would allow you to insert the category names in part of the search index and mark them as more relevant for that keyword (than say in the post body), and make mark the category as more relevant than the other keywords. The advantage to this is you have all sorts of control on what comes up first: something like:

SELECT p.*, MAX(pi.relevance) relevance FROM posts LEFT JOIN post_index pi ON pi.post = post.id WHERE keyword IN (keyword_list) GROUP BY pi.post_date, pi.post ORDER BY relevance DESC

Your indexer can do whatever it likes to determine sorting or relevance. The hard part is figuring out when to index your content.