homepage Welcome to WebmasterWorld Guest from 54.167.244.71
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Searching a MySQL database with PHP
I need a little help please :)
Philarmon




msg:1305343
 3:56 pm on Jun 14, 2004 (gmt 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

 

sned




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

Perhaps you could try something like :

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

StupidScript




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

How about:

select * from tablename where product regexp '$product'

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

Philarmon




msg:1305346
 5:21 pm on Jun 15, 2004 (gmt 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");
}

StupidScript




msg:1305347
 6:05 pm on Jun 16, 2004 (gmt 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.)

StupidScript




msg:1305348
 6:17 pm on Jun 16, 2004 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved