数据库搜索中的语言特殊字符

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?');

Answer 1

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