Search Product names in MySQL

[greatlogix]

I am using following query to find product names in products table
WHERE product_name LIKE lower('%$keyword%')
Product in DB is 'Blue Cradle Sheet' when I search "blue cradle" or "cradle sheet" than this product is in search result but when I search for "Cradle blue" than result is zero row returned. What should I change in query that if I search for "Cradle blue" I can get 'Blue Cradle Sheet' in result

[MakeADifference]

if you are using php
$paramArray = explode(" ",$keywords); //$keywords = "Cradle blue";
$sql = "";
for($i = 0; $i < count($paramArray); $i++)
{
$param = trim($paramArray[$i]);
if($i > 0){
$sql .= ' OR ';
}

$sql .= "(product_name LIKE lower('%$param%'))";

}

echo $sql;

Basically you need to build your query dynamically. There may be errors in the code that I wrote but I just wanted to give you an idea.

Cheers

[greatlogix]

Not working.

I have following products

Cradle - Royal Blue Woven
Round Crib - Blue Gingham Jersey Knit
Cradle - Solid Blue Woven

Now If I search for 'Cradle blue' or 'Blue Cradle' I want "Cradle - Solid Blue Woven" and "Cradle - Royal Blue Woven" in result
Although "Round Crib - Blue Gingham Jersey Knit" has keyword blue in it but its not cradle so it should not be in result. How can I do this?

[jakomo]

Take a look here
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

[greatlogix]

Take a look here
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
I tried following query

SELECT * FROM products s, colors c WHERE MATCH (poduct_name, clr_name) AGAINST ('cradle blue' IN BOOLEAN MODE) AND s.cid = c.clrId
I was expecting "Cradle - Solid Blue Woven" in result but result set was zero row.

What I am doing wrong in this query?

[Jamie18]

what you're going to want to do is split the query string up.. i don't use php so i can't really help you there.. but after that you'd have put a loop in the where condition..

this pseudo should get you on track i think
i.e.

$keyword is an array holding each individual search word..

where product_name LIKE '%$keyword[0]%'
php loop from 1 to keyword.length index=i
AND product_name LIKE '%$keyword[i]%'
end loop

this way in order to be returned by the query, product_name must include all the words from the keyword array