Showing posts with label multilingual websites. Show all posts
Showing posts with label multilingual websites. Show all posts

Monday, 21 July 2014

Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation - Multilingual Website

In MySqL Database, we stored lots of data like city address, city description and city full description etc. Many times we stored different language's character like Chinese OR Japanese which take multibyte to single single character.
When we stored multibyte-character then it means we can also search the data from multibyte-character string.

So, Basically while we are working with multilingual character then we face two common type of problems.
  1. Multibyte Character not storing properly in Database.
  2. Search with multibyte character, MySql send error like "Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation"
Both are different issues,  but solution is Simple and same.

Problem 1: Character not storing properly: Collation must be "utf8_general_ci" to stored multibyte character properly in database.

Sample Table for store multi characters
  `name` varchar(256) CHARACTER SET utf8 DEFAULT NULL,
  `detail` text CHARACTER SET utf8,
  `created_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)

Problem 2: Search with multi character, Db is sending error: While Searching multicharacter, your database sending below error "Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation OR similar issues"

To get rid of this, you have to update the collation of your search fieldCollation must be utf8_general_ci for search multicharacter
ALTER TABLE `users1` CHANGE `name` `name` VARCHAR(256) CHARACTER SET utf8 COLLATE utf8_general_ci;

If you don't want to change the Collation of field we can also pass the utf8_general_ci explicity explicitly in search query.
select * from users WHERE name = "gdaƄsk" COLLATE utf8_unicode_ci

5 Best Related Posts are Following:1. Mysql Privileges - Types of privileages in MySql - How do I grant privileges in MySQL
2. Calculate a Sum of Time using MySQL
3. Mysql Interview Questions And Answers - Atomicity, Dedlock, Client Program, Mysql Mode, Mysql Sensitivity Of Identifiers And Mysql Errors
4. Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation - Multilingual Website
5. Difference Between MyISAM and innoDB - MySQL

Tuesday, 6 September 2011

cakephp1.3 multilingual validation

Cakephp1.3 Multilingual  Validation

Website which is multilingual, messages will be also multilingual. To set the different error message follow the below some simple steps.

Paste following code in app_model.php
        function invalidate($field, $value = null) { 

        if (!is_array($this->validationErrors)) { 

            $this->validationErrors = array(); 


        if(empty($value)) { 

            $value = true; 


        $this->validationErrors[$field] = __($value,  true);         


field collation in database must be "utf8_unicode_ci"  

Multilingual in Cakephp1.3

Multilingual in cakephp
Just do the following three points

1) In view file user as
echo __('USERNAME'); 

2) IN default.po locale/eng/LC_MESSAGES/default.po
msgid "USERNAME" 
msgstr "User Name"

3) // config/bootstrap.php
define(DEFAULT_LANGUAGE, 'eng');

4)In HTML head add Following
[meta charset="UTF-8"]

Compile .po file
msgfmt message.po.txt -o message.po 

Multilingual Characters save in Database
1. Table Collation should be utf8_general_ci
2. Table's Field Collation should be utf8_general_ci
3. While Save in Db, set character set utf8
4. When Display in Browser, add following tag in Head
<meta charset="UTF-8">