I'm going utf8 crazy trying to sort out the following problem, and would appreciate any help!
I have been trying to convert into using utf8, which for a lot of cases seems to work OK, but not all!
I get the error message "Illegal mix of collations (utf8_bin,IMPLICIT) and (latin1_swedish_ci,COERCIBLE)" when I do something like "SELECT * from term_case WHERE tmc_case_text = "Íslenska"". I don't always get it, so I think it is when the constant string is not a real latin1 string.
An example of where I get the error is for the text "Íslenska" as seen on:
http://dmoz.org/World/%C3%8Dslenska/
I have a table, term_case, which is designed to contain a particular search term item in a case insensitive manner:
CREATE TABLE `term_case` (
`fk_term_key` mediumint(8) unsigned NOT NULL default '0',
`tmc_case` tinyint(3) unsigned NOT NULL default '1',
`tmc_case_text` varchar(255) character set utf8 collate utf8_bin NOT NULL default '',
PRIMARY KEY (`fk_term_key`,`tmc_case`),
UNIQUE KEY `tmc_case_text` (`tmc_case_text`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
PHP Trace output:
SQL="SELECT * from term_case WHERE tmc_case_text = "Íslenska""
SQL error: "Illegal mix of collations (utf8_bin,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='" in file c:\program files\apache group\www\Terms.class.php, line 138
SQL="SELECT * from term WHERE term_match_text = "Íslenska""
SQL error: "Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='" in file c:\program files\apache group\www\Terms.class.php, line 222
I don't really know what I'm doing wrong or how to fix it. I'm really getting out of my depth in all this utf8 stuff, though I've tried hard to understand it. I have tried:
* prefixing the literal with "_utf8"
* doing a CONVERT("$termText" USING utf8)
* using the PHP function utf8_encode($termText);
Each doesn't seem to work as I want.
Help!!!! Thanks :-)