Searching a MySQL database with PHP

I need a little help please :)

[Philarmon]

Hello!

I am not a PHP guru so i hope someone of the gurus here can help me :)

I have built a search function which works fine for one-word searches but not for multiple words. The query looks like this:

$result = mysql_query("select * from mytablename where MATCH (product) AGAINST ('$product') order by product, quantity, price ASC");

Now, when i do a search for "red widgets", i am getting results not only for "red widgets", but also for "blue widgets" and "red socks". So if i do a two - word search, i am getting all entries with one of those words (an "OR"-search)

How do i tell the database that i want only entries with both words in the product field? (an "AND"-search)

Thanks for any help!
Cheers,
Philarmon

[sned]

Perhaps you could try something like :

select * from mytablename where product LIKE '%red%' AND product LIKE '%widgets%' order by product, quantity, price ASC"

[StupidScript]

How about:

select * from tablename where product regexp '$product'

Note that the MySQL "regexp" type of match is case-sensitive.

[Philarmon]

Thanks to you both guys, i just solved it by splitting the product name in 2 parts. Probably not the most elegant way but it works for my needs :)

In the case someone could use it:

$array = explode(" ", $product);
if (count($array) >1){
$result = mysql_query("select * from pills where MATCH (product) AGAINST ('$array[0]') AND MATCH (product) AGAINST ('$array[1]') order by product, quantity, (price+shipping) ASC");
} else{
$result = mysql_query("select * from pills where MATCH (product) AGAINST ('$product') order by product, quantity, (price+shipping) ASC");
}

[StupidScript]

Since you want to search by the entire phrase, give the regexp method a try. I think you'll like its simplicity.

You should think about keeping your db "product" entries all lowercase. That way you can clean up the search and result display easily:

In the query function:

$this_product=strtolower($product);
...
select * from tablename where procudt regexp '$this_product'
...
...
$curr_item=$row["product"];
...

In the display function:

Enjoy <?echo ucfirst($curr_item)?> every day!

(ucfirst will capitalize the first letter of each word in the product name.)

[StupidScript]

Geez! Ya use something every day for a few years, and you forget what its properties are!

I apologize. It's even easier than I figgered.

The MySQL "regexp" method is NOT case-sensitive. So title your products any way you want, and search for any of them with a mix of upper- and lowercase, and it'll find the right ones without modification.

select * from tablename where product regexp '$product'

...will do exactly what you want it to do with no fuss. Search for "red bag" matches "Red Bag", "RED BAG", "red bag", etc. They can even search for partial terms, like "red" or "bag" or "d ba"...in any case.

Sorry 'bout that. Really, though, use regexp. Fun & fast.