Advanced Searching - Near Matches

[toxictoad]

Hi all,

 

I have a very basic one table db for my movies with some php code that I found and modified and the search only returns exact matches e.g. If I was to search for 'house on huanted hill' it wouldn't return any results coz of the typo.

 

So...I was wondering how complicated is it to have a search on the database that picks out near matches? so the above would actually pull 'house on haunted hill' from the db?

 

[roopurt18]

There's two approaches, both of which use SOUNDEX

 

#1:  Load a dictionary table and spell-check each incoming request.  Then you can do something like Google where they display the results of your current search and a recommended different search.

 

Zero results found for 'House on Huanted Hill.'

 

Did you mean 'House on Haunted Hill?'

 

#2: Don't perform any spell-checking and don't use any exact matching for your search results.  Just use a WHERE SOUNDEX( `movie_name` ) = SOUNDEX( 'user_provided_search' )

[chronister]

Your looking for the LIKE clause, and a wildcard of %.

 

<?php
$search_term = "House";

"SELECT * FROM table_name WHERE fieldname LIKE '%$search_term%'"

?>

would return

 

House on haunted hill, Little house on the prarie, Our House ... etc

 

<?php
$search_term = "House";

"SELECT * FROM table_name WHERE fieldname LIKE '$search_term%'"

?>

 

would return

 

House on haunted hill, house **..

 

 

Hope this helps

 

Nate

[toxictoad]

Thanks guys, I do like the look of the SOUNDEX option although it may be a little more complex (so will need some help)

 

I like the option you suggested Nate it looks easier to work with (being new to this) but I would need to add a lot of search terms into the page wouldn't I? Guess this is where the dictionary stuff comes in but will have to look into it more...

 

[chronister]

..... but I would need to add a lot of search terms into the page wouldn't I? Guess this is where the dictionary stuff comes in but will have to look into it more...

 

 

No, the search term comes from the form input. Just pass the var you have from your current form into the query. Nothing else is needed.

 

You just need to decide where you want to use the wildcard character or if you want to use 2. In your instance, 2 would be best. Like I show in the first example.

 

Nate