MySQL collate error occurs when sent by PHP app but does not occur in phpMyAdmin

Question

I have a query that uses:

t3.`value` LIKE "%someval%" COLLATE utf8_general_ci

I load the page in my browser and get this error printed to the screen:

COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

The query is also echoed to the page. And if I copy and paste the outputted query into a phpMyAdmin SQL window, it runs fine, producing the expected result.

I had a previous problem where PHP could not use LOAD DATA INFILE. I wonder if this is a similar type of problem. But I can't see how it would be.

This is running on an Ubuntu Server 10.04 MySQL Apache PHP stack.

Answer 1

Thank you google.

Apparently, I have been using PHP with MySQL without ever needing to worry about client collation.

I don't know if this will lead to other problems, but for the moment I've solved this problem by adding the following query to the main controller (so that the query is run for every script on the site).

SET NAMES "utf8" COLLATE "utf8_general_ci";

Now my query works through my PHP script. I suppose that phpMyAdmin might take the collation of whatever tables you are querying and set the client collation accordingly. I'm just guessing on that one.

Here's where I found the answer: http://dev.mysql.com/doc/refman/4.1/en/charset-collation-charset.html