Need help to find string words via using like oprator

[prash21m]

I have a table with two columns "storename" and "discription".

id---- Storename ---- Description

1 ---- Myntra ---- 20% of on all product
2 ---- Flipkart ---- 40% of on all product

for search i am using 'like' query like this :

select * from table_name where description like %search_terms%;
When I am trying to search "20% off on all product",Its returning 1 result for Myntra,

BUT when i am searching for "20% of on all product and clothes" then its not returning any result.

Because the description row does not contain the words "and clothes".

I know that we can use 'full text index' but it will contain all the results from all the rows.

[rubberman]

So, what IS your problem. You seem to have determined that the row doesn't contain the "and clothes" term searched for, hence the lack of results. Please be more specific about what you are trying to accomplish.

[Borzoi]

I believe that prash21m is trying to get search results returned even if the search query contains something not in the description.

I haven't done anything like this for a while so there may be a more efficient way to do this but what I did when I needed to do something like this is convert the input string in to an array then loop through that array, expanding the SQL qeury with OR statements. Note that the below is from memory.

$search = $_POST["search"]; //Your variables may be different.

$searcharr = explode(" ",$search); //This converts the search string into an array, using the spaces as delimiters.

$sql = "SELECT * FROM table_name WHERE description LIKE %".$searcharr[0]."% "; //This starts the SQL query and inputs the first entry of the array.

for ($i = 1; isset($searcharr[$i]; $i++) //This will continue to loop as long as there is another word/entry in the string.
{
    $sql .= "OR description LIKE %".$searcharr[$i]."% "; // This will append the SQL query. Note the period in front of the equals.
}

$sql .= ";"; //This closes off the SQL query.

You then run the SQL query as normal.

With this code, you search the database per word instead of the whole string. The conversion to an array from a string will convert this:

20% off on all product and clothes

To this:

Array
{
    [0] = 20%
    [1] = off
    [2] = on
    [3] = all
    [4] = product
    [5] = and
    [6] = clothes
}