Jim Moseby wrote:
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
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!)
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