Mysql – Arabic language search in codeigniter error : Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)

join;MySQLutf-8

Arabic language search in codeigniter error : Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)

In English language search is working

JOIN Query to use to search

SELECT *, `guest`.*, `guest`.`id` as `guestid`, `assistant`.`guest_id` as `assistantid` FROM `guest`
LEFT JOIN `assistant` ON `guest`.`id` = `assistant`.`guest_id`
WHERE   (
`guest`.`id` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`title` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`firstname` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`middlename` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`lastname` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`arabic_title` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`arabic_firstname` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`arabic_middlename` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`arabic_lastname` LIKE '%محلل مالي%' ESCAPE '!'
OR  `company_name` LIKE '%محلل مالي%' ESCAPE '!'
OR  `position` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`country_code` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`mobile_no` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`email` LIKE '%محلل مالي%' ESCAPE '!'
OR  `nationality` LIKE '%محلل مالي%' ESCAPE '!'
OR  `category` LIKE '%محلل مالي%' ESCAPE '!'
OR  `gender` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest_type` LIKE '%محلل مالي%' ESCAPE '!'
OR  `location` LIKE '%محلل مالي%' ESCAPE '!'
OR  `guest`.`remarks` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`id` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`id_user_master` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`guest_id` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`title` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`firstname` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`middlename` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`lastname` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`arabic_title` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`arabic_firstname` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`arabic_middlename` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`arabic_lastname` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`country_code` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`mobile_no` LIKE '%محلل مالي%' ESCAPE '!'
OR  `assistant`.`email` LIKE '%محلل مالي%' ESCAPE '!'
 )
ORDER BY `guest`.`id` DESC
 LIMIT 10

in database "utf8_general_ci"

Best Answer

Check the connection for being character set utf8
Check the application for whether it is using UTF-8

Meanwhile, that looks like an awfully slow query -- lots of ORs and leading wildcard on LIKEs.

Related Solutions

MySQL: Illegal mix of collations

After some trial and error, I've learned how and where to apply COLLATE:

Converted lines like:

SELECT SOMETHING
FROM SOMEWHERE
WHERE table_schema = given_database
AND   table_name   = given_table
AND   index_name   = given_index;

To:

SELECT SOMETHING
FROM SOMEWHERE
WHERE table_schema COLLATE utf8_unicode_ci = given_database
AND   table_name   COLLATE utf8_unicode_ci = given_table
AND   index_name   COLLATE utf8_unicode_ci = given_index;

MySQL Error : 1422 Explicit and Implicit Commit is not allowed in SP and Trigger

(Too much for a Comment...) (Working backwards...)

LIMIT 1, but no ORDER BY?? You don't care which item you get?

SELECT .. INTO badgeId, but then don't really use it? Perhaps the subsequent SELECT should have WHERE name = 'Golden Ball'?

Instead of doing IF EXISTS, try to cause the final SELECT to return nothing. That would have the same effect, and will probably eliminate the 'need' for a temp table.

Does that IF EXISTS do anything other then see if there are any rows in tempname?? If so please explain.

Is d.user_id the PRIMARY KEY of team_trophies? If not, the GROUP BY may be improper.

No mention of OLD. or NEW.??

I suspect the INSERT .. SELECT could be simply use the original SELECT, plus an additional WHERE clause based 'Golden Ball', but I am too lost to attempt to rewrite it.

Best Answer

Related Solutions

MySQL: Illegal mix of collations

MySQL Error : 1422 Explicit and Implicit Commit is not allowed in SP and Trigger

Related Question