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 databse 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.

Answer 1

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%';

Answer 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.

Answer 3

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

Answer 4

Search is notoriously difficult to do well.

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

Answer 5

Giraffe and Re0sless pooseted 2 good answers.

notes: "SELECT * " sucks... only select the columns that you need. Re0sless puts a "OR" between keywords. - you should eliminate common words (" ","i","am","and"..etc) - mysql has a 8kb i belive limit on the size of the query, so for really long SELECTS you should slipt it into separate queries. - try to eliminate duplicate keywords (if i search for "you know you like it" the SELECT should basically only search for "you" once and elimnate common words as "it")

Also try to use "LIKE" and "MATCH LIKE" (see mysql man page) it could do wonders for "fuzzy" searches