[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

[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

[TheWebDiva]

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

[chetf]

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

[Rogi]

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.

[BurgerBoy]

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.

[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

[TheWebDiva]

Great info! Thanks!

I'm now off to rebuild my search function. See ya in three or four days.

[fish4017]

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

-Kyle

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

[chetf]

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

[BurgerBoy]

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.

[chetf]

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

[UncleScooter]

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

[TheWebDiva]

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.

[fish4017]

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

[chetf]

Post your sql select statement.

[Webnet]

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.

[chetf]

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

[UncleScooter]

What is the best way to search all text in the db?

[Webnet]

Full-text searches are supported for MyISAM tables only.

PHP Code:

SELECT `products` FROM `table` WHERE MATCH (Product Name) AGAINST ($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

[chetf]

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