MySQL: Tweaking MATCH() AGAINST()

[davidjnels]

Hi there,

I was wondering if it is possible to somehow tweak the methods and/or 'sensitivity' of the MySQL match() against() query commands.

For example, let's say I have a row with the name value of "DavidNelson", a MATCH(name) AGAINST(David) query would not return the row, because it must be exactly as entered.... MATCH(name) AGAINST(davidnelson) would return the row.

Is there any way I can change the way it works, so it does not require an exact match to return a row?

Of course, within reason; it would not be good if searching for David also returned Sam merely because it has the letter 'a' in it. I suppose an 'if matching >3 consecutive characters' of sorts would do the trick, either way, I'm not sure how I could do this.

I figured someone may have already done something similar. And if anyone has any alternatives to MATCH() AGAINST() for returning MySQL rows your input is welcome. :)

Thanks!

David

[chazzy]

could you post the exact query you're using? depending on how you're using fulltext, it might not be returned because you need a 50% match.

[davidjnels]

Here you go, Thanks!

function search($search){
global $settings;

$search = gpcStripSlashes($search);

$search=trim($search);

$q = 'MATCH(`url`,`name`,`description`) AGAINST("'.mysql_safe($search).'")';

$query = 'SELECT * FROM '.$settings['links_table'].' WHERE `approved`="true" and '.$q;
$result = mysql_query($query);

[chazzy]

ok, so part of your problem is using the double quotes instead of single quotes. rewrite the line of $q as...


$q = "MATCH(`url`,`name`,`description`) AGAINST('".mysql_safe($search)."'IN BOOLEAN MODE)";


based on what you're doing i'd recommend the BOOLEAN MODE

since i'm not sure what the value of "$seach" is, i'd recommend that you add a + in front of each word in $search. you should be able to do this using a str_replace to turn the " "'s into " +"'s

[davidjnels]

Hi,

Thanks! However, that still doesn't return things like my example in the topic post, i.e., searching for 'web' would not return 'webdeveloper' since it is one whole word instead of two.

Not sure if I can make this work with MATCH() AGAINST() unless you have any ideas regarding that. :)

David

[chazzy]

well, i'm not sure "web" would ever work seeing as full text search requires 4 characters.

Edit: You know, we might have a conceptual issue. If you're searching for "web" but the text is actually "my post on webdeveloper was moved to a different forum" it may not return. The reason is that full text is a linguistic based searching tool, so it looks for important words in a document. if you're searching for a substring of another word, there's no guarantee that it will work 100%. The same text should be returned 100% of the time if you search for "webdeveloper." It should also be of higher importance if you were to search for something like "+web +developer"

[davidjnels]

I see.

Well, a more realistic example; an entry of my database is ACDCLovers, i'd like it if searching ACDC would turn up the ACDCLovers row.

Maybe this isn't possible by using match() against(), I dont know.

David

[chazzy]

no, you're right. if your search column is something simple like a group name, the results you're looking for are found using a LIKE comparison, not a full text search. Even the simple descriptions (like I gave above) are more searchable using LIKE and a properly created INDEX on the column rather than full text search. The benefit of full text search is when you need to find the search term in this body of text:


Man oh man am I big a fan of ACDC. I absolutely love rock and roll music, and everything is black to me. Their latest album is absolute garbage, but their old stuff is amazing. I say get rid of the current ACDC and bring back the old! I still love them though, they're always going to be number one in my heart.

[davidjnels]

Ah I see... Well I'm actually a MySQL novice compared to PHP so I don't exactly have that "problem solving train of thought" for MySQL yet as I'm sure you do, so I ask; any ideas? :P

David

Yeah I know, whats with all the hype on "Rock and Roll Train"!? ;)

[chazzy]

any ideas from what?


the results you're looking for are found using a LIKE comparison, not a full text search. Even the simple descriptions (like I gave above) are more searchable using LIKE and a properly created INDEX on the column rather than full text search