Welcome to WebmasterWorld Guest from 54.160.131.144

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Searching a MySQL database with PHP

I need a little help please :)

     

Philarmon

3:56 pm on Jun 14, 2004 (gmt 0)

10+ Year Member



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

5:42 pm on Jun 14, 2004 (gmt 0)

10+ Year Member



Perhaps you could try something like :

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

StupidScript

4:30 pm on Jun 15, 2004 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



How about:

select * from tablename where product regexp '$product'

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

Philarmon

5:21 pm on Jun 15, 2004 (gmt 0)

10+ Year Member



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

6:05 pm on Jun 16, 2004 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

6:17 pm on Jun 16, 2004 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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.

 

Featured Threads

Hot Threads This Week

Hot Threads This Month