Fulltext search

Opteron

Below code is from a CMS's search script. When you search for plain words like Britney Spears it displays the results correctly. But when you search for a URL like http://www.phpbuilder.com then it does not return any results.
Is there anything wrong with the following SQL code?

...
$this->sql = "SELECT story_id, story_date WHERE $where $sterms ";
...
$where = " MATCH (story_title, story_url, story_tags, story_content) AGAINST ('$words') ";

Note: story_url stores URLs exactly as http://www.phpbuilder.com not encoding etc.
story_url field is FULLTEXT same as others.

Roger_Ramjet

Mysql will break the url up into words on the full stops. What happens then is that most segments exceed the 50% match limit and are ignored: http//, www, com etc.
The more unique part of the domain name should however still be applied but may still exceed the 50% limit and be ignored.

If you are storing the url then just search when = url

Opteron

Roger Ramjet wrote:
If you are storing the url then just search when = url

so how can i do that? i barely know sql.
thanks

Roger_Ramjet

If you are storing the URLs in a varchar column, one to a row then you just have to SELECT ... WHERE story_url=$url simple as that. Of course the urls have to match exactly.

If you want to continue to use FTS then you can avoid the 50% threshold by doing it in BOOLEAN MODE

PS: I also forgot in my first post that www, com are only 3 chars and too short to be used unless you have reset the minimum word length.

Opteron

Roger Ramjet wrote:
If you are storing the URLs in a varchar column, one to a row then you just have to SELECT ... WHERE story_url=$url simple as that. Of course the urls have to match exactly.

If you want to continue to use FTS then you can avoid the 50% threshold by doing it in BOOLEAN MODE

PS: I also forgot in my first post that www, com are only 3 chars and too short to be used unless you have reset the minimum word length.

Thank you Roger. I changed it to BOOLEAN MODE. Now it returns results but all of them. Since every URL has http://www or http:// part it matches all. Is there a way to make this exact search? If I'm searching for http://www.phpbuilder.com/index.php?id=16 it should return only one row not all the rows containing the same domain different URLs.

Roger_Ramjet

Well no, not with FTS. Do it with a simple '=' and forget FTS for this.

If you insist on FTS then you are going to have to strip off all the common parts and reduce the domain name to just the salient parts. Even then I don't see how you are going to match 'id=16' so you'll get everything from 'phpbuilder'.

FTS matches words, and 'www.phpbuilder.com' is 3 words.