Clever Geek Handbook

Finding multiple words in MySQL

I am using an HTML form so that users can find records in a database table:

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

My database has a column with names that contain multiple values, so I use a SELECT query to select all rows that contain any keywords that match what the user entered into the form. Therefore controller.php contains this request:

$word= $_GET['word'];

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

This works great except when the user enters more than one word in the search box. How can I change this so that when a user types more than one word in the search box, it will return all rows that have one of the user's words in the keywords column.

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

+3


source to share


8 answers


You may try -

$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);

Checks can be added as needed.

+3


source


I am using this code and it works 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 . '"';
+1


source


LIKE

the command is good for a one-word line. For advanced database searches, use search FULLTEXT

.

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

0


source


You can use regex:

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

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


source


As in SQL, unable to find a single name, try to split the name based on spaces like ie Heavens Road and add ie ( '%Heavens%'

) OR ( %Road%

) to the end of the line in the final query.

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

for example if more terms then LIKE (%1term%) OR (%2term%) OR (%3term%) OR (%4term%)

0


source


regex does it for me.

$str = $_GET['words'];

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

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

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

foreach ($ str as $ value) {if (! in_array ($ value, $ commonwords)) {// remove 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 ​​for search and do regex

"SELECT * FROM table_name WHERE keywords

REGEXP '$ Words'"

Works for me where the user is looking for more than one product, each separated by a space. the user does not need to enter full words

0


source 


$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)";
0


source 


  $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;
-4


source






More articles:


All Articles