Jim Moseby wrote:
While this query would work, using a fulltext index would give you a much more powerful search. Check to see if your database offers some sort of text indexing (it probably does!)Hi,
I need to build up a search module for a shop. If I make a basic search (product title for example) it is ok.
$query = "SELECT product_id FROM products WHERE title LIKE '%$title%'";
But i need an advance search for more than one field (title, description, price, weight)
The problem is that i don't know which field is filled in by the user (title, description, price or weight)
I mean, the user can fill in all fields, or only price field, or title and weight etc
How can i do the search?
Thanks
$query = "SELECT product_id FROM products WHERE title LIKE '%$title%' and description LIKE '%$description%' and price like '%$price%'
and weight like '%weight%'";
JM
Suppose you have a product title like "The Lion, the Witch, and the Wardrobe". If your user did a search on "lion witch wardrobe", you'd want my example to show up. Just comparing these fields with a LIKE will not give you my result, unless you explode the search string and create several LIKE statements based the individual terms, but then you have to do three LIKE comparisons, which will probably be slower than a full text index. If you are able to use a full text index, then you can write queries like this:
$sql = 'SELECT * FROM products WHERE MATCH ( product_title ) AGAINST ( "' .$_REQUEST['product_title']. '" ) AND MATCH ( product_description ) AGAINST ( "' .$_REQUEST['product_description']. '" )';
which would give you "The Lion, the Witch, and the Wardrobe" if search terms were "lion witch wardrobe".
kgt
