php mysql COLLATE issue?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

php mysql COLLATE issue?

Post 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?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: php mysql COLLATE issue?

Post by onion2k »

Shouldn't you be collating the variable into the same encoding as the database column?
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

Re: php mysql COLLATE issue?

Post 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?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: php mysql COLLATE issue?

Post 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.
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

Re: php mysql COLLATE issue?

Post by Citizen »

Hmmm, is there any way to query the database and find out what the collation is? I cant find anything on google.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: php mysql COLLATE issue?

Post 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
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

Re: php mysql COLLATE issue?

Post 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?
Post Reply