Welcome to WebmasterWorld Guest from 107.20.75.63

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Searching a MySQL database with PHP

I need a little help please :)

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

Junior Member

10+ Year Member

joined:July 23, 2003
posts:45
votes: 0


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

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

Full Member

10+ Year Member

joined:Mar 17, 2004
posts:215
votes: 0


Perhaps you could try something like :

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

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

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 20, 2004
posts:1475
votes: 0


How about:

select * from tablename where product regexp '$product'

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

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

Junior Member

10+ Year Member

joined:July 23, 2003
posts:45
votes: 0


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");
}

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

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 20, 2004
posts:1475
votes: 0


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.)

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

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 20, 2004
posts:1475
votes: 0


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.

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members