Php/ MySql 'Advanced Search' Page

Question

I'm working on an 'advanced search' page on a site where you would enter a keyword such as 'I like apples' and it can search the database using the following options:

Find : With all the words, With the exact phrase , With at least one of the words, Without the words

I can take care of the 'Exact phrase' by:

SELECT * FROM myTable WHERE field='$keyword';

'At least one of the words' by:

SELECT * FROM myTable WHERE field LIKE '%$keyword%';//Let me know if this is the wrong approach

But its the 'With at least one of the words' and 'Without the words' that I'm stuck on.

Any suggestions on how to implement these two?

Edit: Regarding 'At least one word' it wouldn't be a good approach to use explode() to break the keywords into words, and run a loop to add

(field='$keywords') OR ($field='$keywords) (OR)....

Because there are some other AND/OR clauses in the query also and I'm not aware of the maximum number of clauses there can be.

Solution 1

I would suggest the use of MySQL FullText Search using this with the Boolean Full-Text Searches functionality you should be able to get your desired result.

Edit:

Requested example based on your requested conditions ("Its just one field and they can pick either of the 4 options (i.e 1 word, exact words, at least 1 word, without the term).")

I am assuming you are using php based on your initial post

<?php
$choice = $_POST['choice'];
$query = $_POST['query'];

if ($choice == "oneWord") {
    //Not 100% sure what you mean by one word but this is the simplest form
    //This assumes $query = a single word
    $result = mysql_query("SELECT * FROM table WHERE MATCH (field) AGAINST ('{$query}' IN BOOLEAN MODE)");
} elseif ($choice == "exactWords") {
    $result = mysql_query("SELECT * FROM table WHERE MATCH (field) AGAINST ('\"{$query}\"' IN BOOLEAN MODE)");
} elseif ($choice == "atLeastOneWord") {
    //The default with no operators if given multiple words will return rows that contains at least one of the words
    $result = mysql_query("SELECT * FROM table WHERE MATCH (field) AGAINST ('{$query}' IN BOOLEAN MODE)");
} elseif ($choice == "withoutTheTerm") {
    $result = mysql_query("SELECT * FROM table WHERE MATCH (field) AGAINST ('-{$query}' IN BOOLEAN MODE)");
}
?>

hope this helps for full use of the operators in boolean matches see Boolean Full-Text Searches

Solution 2

I'm not sure you could easily do those search options in a naive manner as the other two.

It would be worth your while implementing a better search engine if you need to support those scenarios. A simple one that could probably get you by is something along these lines:

When an item is added to the database, it is split up into the individual words. At this point "common" words (the, a, etc...) are removed (probably based on a common_words table). The remaining words are added to a words table if they are not already present. There is then a link made between the word entry and the item entry.

When searching, it is then a case of getting the word ids from the word table and the appropriate lookup of item ids in the joining table.

Solution 3

You could use

With at least one of the words

SELECT * FROM myTable WHERE field LIKE '%$keyword%' 
or field LIKE '%$keyword2%' 
or field LIKE '%$keyword3%';

Without the word

SELECT * FROM myTable WHERE field NOT LIKE '%$keyword%';

Solution 4

Search is notoriously difficult to do well.

You should Consider using a third party search engine using something like Lucene or Sphider.

View more solutions