CODEWITHSUNDEEP
phpmysql
Modesto Lingerfelter
Modesto Lingerfelter

Reputation: 119

Search multiple words in MySQL

I am using a HTML form to allow users to find entries in a database table:

   <form action="controller.php" method="get">
   <input type="text" name="word" id="word">

The table in my database has a column called keywords that contains multiple values, and so I use a SELECT query to select all rows that have any keywords that match what the user typed into the form. So controller.php contains this query:

$word= $_GET['word'];

$query = SELECT * FROM table WHERE table.keywords LIKE '%{$word}%

This works fine, except if a user types more than one word into the search box. How do I alter this such that, when a user types more then one word into the search box, it will return all rows that have either of the users words in its keywords column.

i.e, user searches "apples oranges", and the query returns all rows that have either "apples" or "oranges" in their keywords field.

Upvotes: 6

Views: 25807

Answers (9)

Njoroge Mathu
Njoroge Mathu

Reputation: 65

regular expression does it for me.

$str = $_GET['words']; 
$commonwords = 'a,an,and,I,it,is,do,does,for,from,go,how,the,this,are';// you dont want to search for these common words

$commonwords = explode(",", $commonwords);

$str = explode(" ", $str);

foreach($str as $value){
  if(!in_array($value, $commonwords)){ // remove the common words from search

    $query[] = $value;
  }
}   

$query = implode(" ", $query);// generate coma separated values 

$str2 =(explode(" ",$query));// convert the values to an array
$Words = implode('|',array_values($str2)).'';// generate values to be searched and make regex

"SELECT * FROM table_name WHERE `keywords` REGEXP '$Words'"

Works for me where the user search for more than one product each separated by a space. user does not have to type full words

Upvotes: 1

Peter Brand
Peter Brand

Reputation: 624

Here's my PHP solution where the search string contains multiple keywords, can be in any order, may be separated by multiple spaces, may have repetitions, must be ranked on how many keywords found, and is sql injection safe. Assume table is called Topics with an ID of TopicID, text in column TopicText and keywords in column Keywords, and your mySQL connection is $dbConn

//convert search string to lowercase - will do case insensitive search later
$words = trim(strtolower($_GET['k']));
//remove punctuation and wildcards
$words = str_replace(array('*', '?', '-', '.', '/', '(', ')', '+', '&'), '', $words);
//turn the string into an array of unique words
$words = array_unique(explode(' ', $words));
//strip the words we are not interested in
$stopWords = array('', '*',  '?', 'a', 'about', 'an', 'and','are', 'as', 'at', 'be', 'by', 'for', 'from', 'how', 'in', 'is', 'it', 'of', 'on', 'or', 'that', 'the', 'this', 'to', 'was', 'what', 'when', 'where', 'who', 'will', 'with');
foreach($stopWords as $word) {
    if (($key = array_search($word, $words)) !== false) unset($words[$key]);
}
//build SQL statement
$sql =  "select TopicText, Score from (select TopicID,  0";
foreach($words as $word) {
    if($word>'') $sql .= "+if(Keywords regexp '" . $dbConn->real_escape_string($word) . "',1,0)";
}
$sql .=  " as Score from Topics) T join Topics TT on TT.TopicID = T.TopicID where T.Score>0 order by Score desc";

If you don't have a column with keywords, one could use the TopicText column as the regexp target above.

Upvotes: 1

Ajay Kumar
Ajay Kumar

Reputation: 97

$words = explode(" ",$string);
$string = array_map(function(&$word){
    return "+".$word."*";
},$words);

$term = implode(" ",$string);
$query = "SELECT * FROM table_name WHERE MATCH(column_name) AGAINST('{$term}' IN BOOLEAN MODE)";

Upvotes: 1

Mehdi Zamani
Mehdi Zamani

Reputation: 343

I use this code and work fine

    $regcond = trim($_GET['word']);
    $regcond = preg_replace('!\s+!', ' ', $regcond); //change how many space beetwen word to one space
    $regcond = trim($regcond);
    $regcond = str_replace(" ",".+",$regcond); // change all space to .+ for search with regex
    $final_cond = 'SELECT * FROM table WHERE table.keywords REGEXP "' . $regcond . '"';

Upvotes: 2

suhas r
suhas r

Reputation: 11

As in SQL unable to search for single name try splitting the name based on spaces like i.e Heavens Road and appending to string i.e ('%Heavens%') OR (%Road%) in final query.

SELECT id, gps, street_name 
FROM streets 
WHERE street_name LIKE ('%Heavens%') OR (%Road%)

e.g if more terms are then LIKE (%1term%) OR (%2term%) OR (%3term%) OR (%4term%)

Upvotes: 0

Sanjay Kumar N S
Sanjay Kumar N S

Reputation: 4739

  $word= $_GET['word'];
if (strpos($word, ' ') !== FALSE) {
    $wordArr = explode(' ', $word);
    $where = '';
    foreach ($wordArr AS $word) {
        $where .= " table.keywords LIKE '%{$word}%' OR";
    }
    $where = trim($where, 'OR');
} else {
    $where = "table.keywords LIKE '%{$word}%' ";
}
$query = "SELECT * FROM table WHERE $where";
echo $query;

Upvotes: -4

Sougata Bose
Sougata Bose

Reputation: 31739

You can try with - 

$words = explode(' ', $word);
$query = "SELECT * FROM table WHERE table.keywords IN (".implode(',', $words).")";

Or - 

$query = "SELECT * FROM table WHERE";
$conds = array();
foreach ($words as $val) {
    $conds[] = "table.keywords LIKE '%".$val."%'";
}
$query .= implode(' OR ', $conds);

The checks can be added if required.

Upvotes: 4

jeromegamez
jeromegamez

Reputation: 3541

You could use a regular expression:

$words = explode(' ', $_GET['word']);
$regex = implode('|', $words);

$query = "SELECT * FROM table WHERE table.keywords REGEXP '{$regex}'";

Upvotes: 3

pavel
pavel

Reputation: 27082

LIKE command is good for one-word string. For advanced searching in database use FULLTEXT search.

https://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Upvotes: 0

Related Questions