Page 1 of 1

php mysql COLLATE issue?

Posted: Tue Apr 01, 2008 4:16 pm
by Citizen
I used a query with collate here to make sure that it was case sensitive:

Code: Select all

$sql2 = "SELECT * FROM `" . $prefix . "language_phrase` WHERE `languageid` = '$pop' AND `varname` = '$varname' COLLATE latin1_bin LIMIT 1";
$result2 = mysql_query($sql2) or die(mysql_error());
Which worked perfectly on most of the servers, but not one and it got this message:
Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_bin,EXPLICIT) for operation '='
Is there any way for the query to be case sensitive AND work on every type?

Re: php mysql COLLATE issue?

Posted: Tue Apr 01, 2008 4:32 pm
by onion2k
Shouldn't you be collating the variable into the same encoding as the database column?

Re: php mysql COLLATE issue?

Posted: Wed Apr 02, 2008 4:26 pm
by Citizen
Yes, I should be but I've googled my brains out and can't find any universal way to collate the query. Is there a way to detect what kind of collation they use before making the query?

Re: php mysql COLLATE issue?

Posted: Wed Apr 02, 2008 5:18 pm
by onion2k
I don't think there's a universal way of doing it. You could always collate both the column and the variable into the same encoding... then they'd always match. Might start being a bit slow though.

Re: php mysql COLLATE issue?

Posted: Wed Apr 02, 2008 5:56 pm
by Citizen
Hmmm, is there any way to query the database and find out what the collation is? I cant find anything on google.

Re: php mysql COLLATE issue?

Posted: Wed Apr 02, 2008 9:07 pm
by John Cartwright

Code: Select all

SHOW COLLATION FROM `database`
(may be wrong)

Check out http://dev.mysql.com/doc/refman/5.0/en/ ... -show.html

Re: php mysql COLLATE issue?

Posted: Thu Apr 03, 2008 2:51 pm
by Citizen
Thanks jcart! I'll test that out.

In retrospect, a google search for "show collation" would have worked much better than the "select collation" that I was using to search for how to do it :banghead:

:)

Thanks again,
-Cit

------------------------------

Edit: I cant seem to get this work using any combination of queries. Anyone know how to get the collation of a column using php and mysql?