mysql fulltext

[kristian]

Hi.
According to this text from
http://dev.mysql.com/doc/refman/5.0/...t-boolean.html mysql
supports the * like this 'apple*' "Words match if they begin with the
word preceding the * operator."
But how can I make use of the * like this '*apple' where words match if
they end with the word?

[Sjoerd]

Re: mysql fulltext


kristian wrote:[color=blue]
> mysql
> supports the * like this 'apple*'[/color]

I think you want this:

SELECT * FROM table WHERE textfield LIKE '%apple'

This selects everything where textfield ends on apple. See chapter
12.3.1 in the MySQL 5.0 reference manual.

[kristian]

Re: mysql fulltext

not quite, although it fits the '*apples' problem.

But if I enter a search phrase like 'php5 mysql' it dosnt match
anything. If I use select title, description from sometable
where match (title,descript ion) against ('$word' in boolean mode) I
can add '$word*' and I will get results where $word is at the
beginning, what I want is also to have results where $word is at the
end.

[Stefan Rybacki]

Re: mysql fulltext

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

kristian schrieb:[color=blue]
> not quite, although it fits the '*apples' problem.
>
> But if I enter a search phrase like 'php5 mysql' it dosnt match
> anything. If I use select title, description from sometable
> where match (title,descript ion) against ('$word' in boolean mode) I
> can add '$word*' and I will get results where $word is at the
> beginning, what I want is also to have results where $word is at the
> end.
>[/color]
Unfortunately this is not supported by the nature of how mysql fulltext
index works.
What you can do is some tricky stuff like having your text also in a
mirrored version like "drow" and also an index on it. That way you could
also find words ending on "word" by doing a search against
'str_reverse($w ord)*'.

This was just a thought out of the blue and since I don't know your
entire design and stuff it fits or it doesn't.

Regards
Stefan
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.1 (MingW32)

iD8DBQFEQGXIyeC Lzp/JKjARApGCAKCAAU TJWnsyKuIejtzg4 t6X1T/Y1QCfaLLS
nskbus9X3jIfa/tVooe7SPk=
=Lu7u
-----END PGP SIGNATURE-----

[kristian]

Re: mysql fulltext

thanks for the reply, I found this page
http://dev.mysql.com/doc/refman/5.0/en/regexp.html and I think using
regexp in mysql will do the trick, I'll give it a try.

Thanks again.