[RESOLVED] PDO and COLLATION

Roger_Ramjet

Does anyone know how to bind a collation to the parameters when using PDO prepared statements?

The server default gets applied to string literals which over-ride my database defaults. Now I can solve this when it is straight sql but can't get it to work with the parameter array literals when using pdo->prepare.

So of course I get a PDO exception for mixed collations and nothing works. Don't want to have to go to latin1 just because of this - wanted to use utf8 but the host server default is always latin1.

Roger_Ramjet

Fixed itself, or rather the problem was only with some string literals in the SELECT clauses of a UNION query but does not arise with those in the WHERE clause. My mis-reading of the manual.

Still think this is a pain in the butt from mysql: why on earth should the string collation inherit from the server collation not the database or table and then over-ride that of the tables and columns. If they are going to do that then they should automatically coerce the table columns to the same not just break the query.

MarkR

Are you sure you don't mean character set, not collation?

The encoding which MySQL uses for prepared statement parameters will be the same as the one it uses for everything else in that connection - as set using SET NAMES.

Ensure that you don't have a funny situation where you have latin1 stored in a utf8 table or vice versa (without appropriate conversions having been done).

This can add junk data to your database quite easily, and it's possible not to notice, if the same junk transformation is being done at the other end - your db still contains junk and this will affect ORDER BY etc.

Mark

Roger_Ramjet

No, I meant COLLATION, definitely - and the issue in question was string literal collation. The error I was getting was about conflicting collation in UNION query where I had string literals added to the selects to pass additional information. On my hosted server these all defaulted to latin1_swedish_c1 while everything else in the db is utf8.

Fix is to add 'introducers' to every string literal to coerce it to utf8 - a real pain in the butt.

"SELECT _utf8'string', t.col1 ....";

My problem was that I thought I would also have to add them to the strings in the WHERE clause and could not see how to do this in PDO. Turns out not to be neccessary as the where clause collation is taken from the column specified - makes sense really.

MarkR

Are you sure your connection encoding is set to utf8, not something else?

You should probably set it with "SET NAMES utf8" if there is even the tiniest possibility that it won't be.

Mark

Roger_Ramjet

Look, I tried all that. I have since found a small note in the manual from someone else using mysql/php 5 who found the only solution was a .htaccess entry:

AddDefaultCharset UTF-8

In truth, this looks like someone at my isp is screwing around with the server - just got a cascade of errors about session_start and my access control script has not changed. That is the trouble with hosted services, you have no control over server settings, or when they are changed, so sometimes the documented solutions just will not work.

Please stop responding to this thread - I marked it as resolved.

PS, I've got another account with the same host and the dbs are on a different server which does not throw this error so go figure.