Welcome, guest! Login
Close

Login to Your Account


  |  » Forgot your password?
Join

Thread: PHP & MySQL Search Question

 
Tools Search
  #1  
Old July 18th, 2005, 09:06 PM
Newbie
Join Date: April 24th, 2005
Posts: 14
PHP & MySQL Search Question

Hey Guys-

So... I've set up a search form on my website which uses php and mysql. It works great but if someone searches for example "sony camcorder" it will only find products with the words "sony camcorder" adjacent to eachother in the title... how could i set it up so it breaks it up and brings results that aren't exact phrase matches (ex. Sony TRV25 Camcorder) ? Here's the code i'm using...

$srch="%".$search."%";
$query = "SELECT * FROM `products` WHERE `Product Name` LIKE '$srch'";

$srch being the search term in this case...

Thanks,
Kyle
  #2  
Old July 18th, 2005, 09:15 PM
Newbie
Join Date: July 15th, 2005
Posts: 25
Send a message via AIM to Monk_LifeXpand
Great Open Source

Kyle, not sure how much you are into using open source apps but there is a great "site search engine that i have integrated into several sites. Check out:
http://www.tsep.info/index.php

Some Great Features.

Have fun

-Monk
__________________
Earn 15% Commision: 100% All Natural Supplements From The Amazon Rainforest
Lifexpand, Inc Boca Raton, FL Phone: (561) 487-3650 www.lifexpand.com
  #3  
Old July 18th, 2005, 10:14 PM
Member
Join Date: January 17th, 2005
Location: Brooklyn
Posts: 118
I hope this helps

style="margin:20px; margin-top:5px; ">
Quote:
Originally Posted by fish4017
Hey Guys-

So... I've set up a search form on my website which uses php and mysql. It works great but if someone searches for example "sony camcorder" it will only find products with the words "sony camcorder" adjacent to eachother in the title... how could i set it up so it breaks it up and brings results that aren't exact phrase matches (ex. Sony TRV25 Camcorder) ? Here's the code i'm using...

$srch="%".$search."%";
$query = "SELECT * FROM `products` WHERE `Product Name` LIKE '$srch'";

$srch being the search term in this case...

Thanks,
Kyle
I am in no shape nor form a php/mysql expert, but this is what I do to get around the problem you're having.

$field = 'fieldname LIKE ';
$srch = '';

$num = count( $array1 );
$i = 0;

foreach ( $array1 as $item ) {
$srch .= $field;
$srch .= "'%$srch%'";
$i++;
if ( $i == $num ) {
$srch .= " LIMIT 45 ";
} else {
$srch .= " AND ";
}

Each word the user inputs is separated in the array and then I construct it so that a mysql statement is built that will search for each term separately.

So, if the user inputs "corner cabinet" as their search term, $srch ends up looking like this: fieldname LIKE '%corner%' AND fieldname LIKE '%cabinet%' LIMIT 45

No doubt there is a much better way to do this, but this works quite well.

Webdiva
Join ABW to remove this sponsored message.
  #4  
Old July 18th, 2005, 10:47 PM
ABW Ambassador
Join Date: January 18th, 2005
Posts: 4,423
There was just another post on this subject. Like's are terrible for performance reasons and should always be avoided. Check the fulltext search information on mysql.

http://dev.mysql.com/doc/mysql/en/fulltext-search.html
  #5  
Old July 19th, 2005, 06:00 AM
Affiliate Marketer
Join Date: January 18th, 2005
Location: Melbourne
Posts: 415
fulltext search on mysql is the way to go. It can do everything you want. What version of MySQL are you running though? I think only the newer one (is it 4?) that supports boolean search also (like: "sony or camcorder" etc.
But fulltext search does what you want it to do. Here's some info:
http://www.phpfreaks.com/tutorials/129/0.php

Or alternatively you could do your own search and algorithm etc... split the words up, etc etc.

Also, there's some good posts on webmasterworld in one thread ( i wont post the url) but you can google for "can a php search function find near-misses" (with the quotes)
to find it.
  #6  
Old July 19th, 2005, 06:56 AM
Moderator
Join Date: January 18th, 2005
Location: Kentucky
Posts: 9,321
style="margin:20px; margin-top:5px; ">
Quote:
Originally Posted by Monk_LifeXpand
Kyle, not sure how much you are into using open source apps but there is a great "site search engine that i have integrated into several sites. Check out:
http://www.tsep.info/index.php

Some Great Features.

Have fun

-Monk
I use the Fluid Dynamics search engine on my web sites. It is free and will do anything that you want it to do.

Not only will it search the site that you have it installed on - but if you have more than one site - you can tell it to index all of your sites and it will give a combined search results from all of your sites on each one of your sites.

It will install itself - all you do is give it the url of where you want it installed.

The Admin page will let you control what it gives in search results without you having to know any programming at all.

It is located at http://www.xav.com/ if you would like to check it out.

Any time you change your site just tell it to rebuild the index and in about 30 minutes it is current - even if you have thousands of pages on your site.
__________________

ABW Forum Rules - Advertise At ABW - None are so hopelessly enslaved as those who falsely believe they are free (J. W. Von Goethe) - Politicians should be limited to 2 terms - One in office and one in prison. Give me a minute before I post again.
Join ABW to remove this sponsored message.
  #7  
Old July 19th, 2005, 07:42 AM
Newbie
Join Date: July 15th, 2005
Posts: 25
Send a message via AIM to Monk_LifeXpand
Wow, that app is pretty cool. I like the self install. The rebuilding of the site index is a must, especially wit hthe amount of fluid content needed t okeep aflot in the serps!!

-Monk
__________________
Earn 15% Commision: 100% All Natural Supplements From The Amazon Rainforest
Lifexpand, Inc Boca Raton, FL Phone: (561) 487-3650 www.lifexpand.com
  #8  
Old July 19th, 2005, 08:48 AM
Member
Join Date: January 17th, 2005
Location: Brooklyn
Posts: 118
Great info! Thanks!

I'm now off to rebuild my search function. See ya in three or four days.
  #9  
Old July 19th, 2005, 11:02 AM
Newbie
Join Date: April 24th, 2005
Posts: 14
Thanks guys

Thanks for all the input i'm gonna give a few things a try and i'll let you know how it goes!

-Kyle
Join ABW to remove this sponsored message.
  #10  
Old July 19th, 2005, 11:09 AM
lurk
Join Date: March 25th, 2005
Posts: 355
Send a message via AIM to jrod805
It may be wise to drop the SQL search and build a search that works of an index. It is less server intensive and much faster.
  #11  
Old July 19th, 2005, 01:19 PM
ABW Ambassador
Join Date: January 18th, 2005
Posts: 4,423
style="margin:20px; margin-top:5px; ">
Quote:
Originally Posted by jrod805
It may be wise to drop the SQL search and build a search that works of an index. It is less server intensive and much faster.
Fulltext seach does work on an index, not sure what you are saying.

While i understand using a 3rd party app because you technically can't handle the coding- think of what you lose.

If you had your own you could:

Capture what people are searching on to see what people are really looking for.

Check the rerfer of the search, where are people coming from then searching - so where did they get the wrong idea about your page, what did they think they were going to find.

Learn how people are looking for items to see if you should change your categories.

Create "mini-stores" inside your site to promote commonly searched on items.


Etc, etc, etc.

Chet
  #12  
Old July 20th, 2005, 06:45 PM
Moderator
Join Date: January 18th, 2005
Location: Kentucky
Posts: 9,321
style="margin:20px; margin-top:5px; ">
Quote:
Originally Posted by chetf
Fulltext seach does work on an index, not sure what you are saying.

While i understand using a 3rd party app because you technically can't handle the coding- think of what you lose.

If you had your own you could:

Capture what people are searching on to see what people are really looking for.

Check the rerfer of the search, where are people coming from then searching - so where did they get the wrong idea about your page, what did they think they were going to find.

Learn how people are looking for items to see if you should change your categories.

Create "mini-stores" inside your site to promote commonly searched on items.


Etc, etc, etc.

Chet
The Fluid Dynamics search engine has an Admin panel that tracks all search phrases and key words searched for built right into it.

It will show all of the searchs by % - will build you a graph.

It will do all of the thing you are saying.

It will also Create "mini-stores" inside your site to promote commonly searched on items.

You can create realms and tell it what realm to return for your mini store - and all of this without having to know about programming.

We're not all programmers remember - but we can do the same things you can do with this search engine without having to be a programmer.
__________________

ABW Forum Rules - Advertise At ABW - None are so hopelessly enslaved as those who falsely believe they are free (J. W. Von Goethe) - Politicians should be limited to 2 terms - One in office and one in prison. Give me a minute before I post again.
Join ABW to remove this sponsored message.
  #13  
Old July 20th, 2005, 07:32 PM
ABW Ambassador
Join Date: January 18th, 2005
Posts: 4,423
While I am glad it works for you, it can hardly do many of the things I mention. And in fact the owners of the product suggest it is not a good tool for searching a database or database-driven content,

http://demo.nickname.net/latest/en/s...2=ManageRealms

Chet
  #14  
Old July 20th, 2005, 08:22 PM
Internet Cowboy
Join Date: January 18th, 2005
Posts: 4,662
Fluid Dynamics is good. I have used it before and still do on some sites. MySQL and php leave it in the dust in every way, but it works for those of us sharpening our coding skills
__________________

  #15  
Old July 20th, 2005, 10:15 PM
Member
Join Date: January 17th, 2005
Location: Brooklyn
Posts: 118
I'm happy to say I'm almost done rebuilding my site search using fulltext in MySql. Now I just have to figure out how to make the boolean search default to "this word must be present in every row returned". Compared to the specialized LIKE searches that I created, the fulltext search returns far too many irrelevant rows without the + signs, and I'm sure most of my site visitors don't know boolean from lean bull.
Join ABW to remove this sponsored message.
  #16  
Old July 23rd, 2005, 04:34 PM
Newbie
Join Date: April 24th, 2005
Posts: 14
style="margin:20px; margin-top:5px; ">
Quote:
Now I just have to figure out how to make the boolean search default to "this word must be present in every row returned". Compared to the specialized LIKE searches that I created, the fulltext search returns far too many irrelevant rows without the + signs, and I'm sure most of my site visitors don't know boolean from lean bull.
Thats exactly my problem also. i've switch to fulltext but the search results have to many irrelavent products. if anyone figures out how to make the boolean search default to "this word must be present in every row returned" please post a reply!

Thanx
  #17  
Old July 24th, 2005, 12:56 AM
ABW Ambassador
Join Date: January 18th, 2005
Posts: 4,423
Post your sql select statement.
  #18  
Old July 24th, 2005, 01:37 AM
Member
Join Date: June 23rd, 2005
Posts: 77
style="margin:20px; margin-top:5px; ">
Quote:
Originally Posted by fish4017
$srch="%".$search."%";
$query = "SELECT * FROM `products` WHERE `Product Name` LIKE '$srch'";
Take that code and change it to:


PHP Code:
$sql "SELECT `products` WHERE `Product Name`";

//remove trailing whitespaces
$search trim($search);

//see if the string contains more than one word
if(strstr($search" "))
{
        
//seperate the string so that each word can be searched by itself
        //@ prevents the function from returning an error if the string contains no spaces or is empty
        
$search = @explode(" "$search);

        
//loop through each word to ensure it's added to the query properly
        
foreach($search as $key => $value)
                
$sql .= " LIKE '%".$value."%' OR ";

        
//remove the last OR on the end to prevent errors
        
$sql substr(strlen($sql), (strlen($sql)-4));
}
else
{
        
$sql .= " LIKE '%".$search."%'";
}

mysql_query($sql) or die(mysql_error()); 
That will take the search string and search the database for each word.
Use all the above code - it will fix your issue ^_^

If you provide me the rest of your query, I'll fix it so you can still search for "sony camcorder" and have it search for those words adjacent to each other.
Join ABW to remove this sponsored message.
  #19  
Old July 24th, 2005, 08:26 AM
ABW Ambassador
Join Date: January 18th, 2005
Posts: 4,423
Fish, I was asking if you had changed it to use with full text search.

I would suggest not doing what webnet suggests as it defeats the purpose of the fulltext search and would be very slow on a large table.

Chet
  #20  
Old July 24th, 2005, 08:47 AM
Internet Cowboy
Join Date: January 18th, 2005
Posts: 4,662
What is the best way to search all text in the db?
__________________

  #21  
Old July 24th, 2005, 09:44 AM
Member
Join Date: June 23rd, 2005
Posts: 77
Full-text searches are supported for MyISAM tables only.

style="margin:20px; margin-top:5px">
PHP Code:
SELECT `productsFROM `tableWHERE MATCH (Product NameAGAINST ($search); 
If you read the fulltext documentation on mysql.com it's not that great of an option and is heavy on the server.

http://dev.mysql.com/doc/mysql/en/fulltext-search.html
Join ABW to remove this sponsored message.
  #22  
Old July 24th, 2005, 10:42 AM
ABW Ambassador
Join Date: January 18th, 2005
Posts: 4,423
Webnet, on a large table? Especially veruses likes? Yes, it is a great option. Results returned in ms, versus seconds.

And for boolean mode to work, you need to call it in boolean mode, and you then have other options, which is why i asked him to post the example.

An example of what i have done previously.

SELECT id, title, adcode, MATCH (urldesc,title,keywords) AGAINST ('$searchtext' in boolean mode ) AS score FROM mytable WHERE active!='0000-00-00 00:00:00' and MATCH (urldesc,title,keywords) AGAINST ('$searchtext' in boolean mode) HAVING score > 0.2 ORDER BY score DESC limit 100" ;
Reply
 

Tools Search
Search:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
I think I found the best "Free" HTML Editor on the planet- PHP & MySQL Tags; Plugins HumbleFish Programming / Datafeeds / Tools 17 April 15th, 2010 01:43 AM
It's Good to Compete With Your Affiliates in PPC Search Vrindavan Search Engine Optimization 6 July 15th, 2005 09:15 AM
Question about search engine listings and who gets the commission? webworker Midnight Cafe' 4 June 16th, 2005 04:31 PM
Got this email newsletter concerning yahoo Roland Midnight Cafe' 6 March 6th, 2004 12:59 AM
PHP search function help Affiliate Ian Programming / Datafeeds / Tools 8 December 16th, 2003 08:27 AM

X

Welcome to ABestWeb.com

Create your username to jump into the discussion!

ABestWeb.com is the largest affiliate marketing community on the Internet. Join us by filling in the form below.


(4 digit year)

Already a member?



Content Relevant URLs by vBSEO ©2011, Crawlability, Inc.