Language Special Characters in database search

Question

me working on search in a multilingual site (currently only english and turkish).

As long as visitor uses the correct language characters, search out puts corrent results.

e.g.

for word "Moudle" (english) like clause of sql query is like "%Moudle%" and it works fine.
for word "modül" (turkish) like clause of sql is like "%modül%" and it works fine.

My problem is that for turkish if i use "modul" rather that "modül", it does not works. Only because of "ü".

Is there any way that i can get results from database even if i use same counterpart characters from english language instead of original?

Structure of table is

CREATE TABLE IF NOT EXISTS post_lang (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  post_id bigint(20) NOT NULL,
  ln enum('en','tr') NOT NULL,
  title text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (id)
);
INSERT INTO post_lang (id, post_id, ln, title, content, doc) VALUES
(1, 1, 'en', 'How can I create a new module?'),
(2, 1, 'tr', 'NasÄ±l yeni bir modül yaratÄ±rÄ±m?');

score 4 · Accepted Answer · edited May 23 '17 at 10:24

4

You could create a field say normalizedText where you are going to store only the chars without accents following your logics.

So you can run a search on your normalizedText with a string not containing accents and having consisent result.

You can use a handy php wordpress function (remove_accents) that converts your accented chars to non accented chars

edited May 23 '17 at 10:24

Community

1
1

answered Jun 15 '12 at 10:47

dynamic

46,985
55
154
231

Would it work for converting from upper given table? Because i have lots of records in that table and i cannot do it manually. – Imran Naqvi Jun 15 '12 at 10:50
you have to first make a script that updates your normalizedText with the non accented chars, then you can run any query on it – dynamic Jun 15 '12 at 10:51
ok thanks for your response, will update you after trying this solution. – Imran Naqvi Jun 15 '12 at 10:52
i came up with this solution but was not sure how to remove accents. – Imran Naqvi Jun 15 '12 at 10:54

Language Special Characters in database search

1 Answers1