Forum Moderators: open

Message Too Old, No Replies

How to match a UTF-8 field with acute vowels words in "BOOLEAN MODE"?

         

guarriman

8:57 am on Oct 8, 2007 (gmt 0)

10+ Year Member



Hi.

Using mySQL 4.1.22, I'd like to carry out an SQL query to find a
string containing acute vowels.

mytable:
- item1:
--- firstname: Antonio
--- lastname: Fernández
--- comments: he's from Spain

My SQL query:
------
SELECT id FROM mytable WHERE MATCH(firstname, lastname, comments)
AGAINST ('+"fernandez"' IN BOOLEAN MODE) ORDER BY firstname, lastname
--------

If mytable is in latin1, it works fine. But if mytable is in utf8
(it's the case) it doesn't work. How can I change my SQL query to
match 'Fernández' in UTF8 within the table?

My ft configuration in /etc/my.conf:
------
ft_min_word_len=1
ft_stopword_file=''
-----

I tried also adding these two lines after DB connection (PHP code):
-------
mysql_query ("SET NAMES utf8;");
mysql_query ("SET CHARACTER_SET utf8;");
------
but it won't work :(

Thank you very much.

vincevincevince

9:03 am on Oct 8, 2007 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



First you will need to rebuild your FT index after changing the character_set.

Second you will need to ensure that your PHP code which sends the acute accent sends that accent in UTF-8 (i.e. write your script and run your PHP in UTF-8)

guarriman

10:35 am on Oct 8, 2007 (gmt 0)

10+ Year Member



Hi vince. Thank you very much for your answer.

How do I rebuild my FT index after changing the character_set?

vincevincevince

10:46 am on Oct 8, 2007 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



So far as I know, the way to rebuild it is to delete the Fulltext index and then add it again.