Search bar for website that match any word from search bar to the database column

I want to create a search bar for a website that matches one, two, or any of the input words against a database column (named title).

For example, if I type in "MY NAME IS CHETAN FROM INDIA", I would want a successful match against title=" I AM LIVING IN INDIA" (via the match on "INDIA".)

How can I design such a query?

Some more examples: right now, if I search for "I like to play" then my query only matches with the database row "I like to play cricket everyday " but not with "you are like my brother". I want to match it also with rows "I like football matches" or "I play everyday " or "you are like my brother"

<form action="search.php" role="search" method="GET">
     <input type="text" name="searchbox">
     <input type="submit" name="submit" value="search">
</form>
$search = strtolower($_GET['searchbox']);
$search_exploded = explode ( " ", $search );
$construct = '';
foreach( $search_exploded as $search_each ) {
    if(!in_array($search_each, $commonwords)){
        $construct .="AND title LIKE '%$search_each%' ";
    }
}
$construct="SELECT * FROM vdo WHERE 1 $construct LIMIT 60";
$query=mysqli_query($con1,$construct);
while($row=mysqli_fetch_array($query)){
    $title=$row['title'];
    echo $title;
}

1 answer

  • answered 2018-04-17 04:50 Ramesh Kithsiri HettiArachchi

    You want to use MYSQL FULLTEXT_SEARCH method.

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

    Example:-

    TABLE

    |id|columntosearch|
    |2 | You can search any word|
    |3 | Yes word is the word|
    |4 | fourth row|
    
    MySQL> SELECT * FROM `TABLE` WHERE MATCH(`columntosearch`) AGAINST('word' IN NATURAL LANGUAGE MODE)
    

    Output

    |id| columntosearch
    |2 | You can search any word
    |3 | Yes word is the word