[Tenderfoot]

Hello guys.

I've been programming a class that inserts into, updates and deletes from tables in my database. My problem is that, when I attempt to insert something with Icelandic characters (covered in UTF-8) - it isn't inserted into the database. I then proceeded to attempt to use the same query on the database level, and that worked just fine. But for some reason, when I'm trying to send it through with PDO, it doesn't work. So what I assume is going on here is a charset problem, somewhere, and at first I tried this:

$this->dbh = new PDO('mysql:host=localhost;dbname=gluteusbase;charset=UTF-8', 'root', 'password (this is fine)');

The part I added was charset=UTF-8. This didn't change anything, things with Icelandic characters still don't get inserted. I then found someone saying that it should be charset=utf8. I tried this as well with no luck. However it didn't give me an error.

After which I tried this:

$this->dbh = new PDO('mysql:host=localhost;dbname=gluteusbase;charset=UTF-8', 'root', 'VEF2A3U', array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

Still no luck. Does anyone have an idea of what I may be doing wrong?

[Dormilich]

enable PDO’s error reporting. it’s set to none in the default configuration. then you should get a message (if there is one).

either by

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

or via option in the constructor:

$options = array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
);
$pdo = new PDO($dsn, $login, $pass, $options);

[CTphpnwb]

What character set is the database set up for?

[Tenderfoot]

Dormilich, on 12 October 2012 - 07:27 AM, said:

enable PDO’s error reporting. it’s set to none in the default configuration. then you should get a message (if there is one).

either by

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

or via option in the constructor:

$options = array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
);
$pdo = new PDO($dsn, $login, $pass, $options);

I opted to use this:

$options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
			$this->dbh = new PDO('mysql:host=localhost;dbname=gluteusbase;charset=UTF-8', 'root', 'VEF2A3U', $options);

And it does work, I'm finally getting exceptions. And this is the one I get when I attempt to insert a string with Icelandic characters:

SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xE1\xED\xF3\xFE\xE6' for column 'exerciseName' at row 1

So I tried a line of code that I found online:

$this->exerciseName = mb_convert_encoding($this->exerciseName,'UTF-8'); 

I was just seeing whether that would work - honestly I'm not sure if I want that to be my solution, seeing I'd have to do that manually for every column that is entered. That's not so difficult per se, I'm just wondering whether that's the right approach. But if that is viable, I still get this error:

Fatal error: Call to undefined function mb_convert_encoding() in C:\apache\htdocs\Gluteus\class.Exercise.php on line 267

And thus I'm given to understand that I need to install the mbstring exception (http://uk.php.net/manual/en/mbstring.installation.php).

I'm not too experienced with installing extensions, so I was wondering whether you guys think that using that line of code is a decent solution for this, before I attempt to change my PHP configuration.

[Tenderfoot]

CTphpnwb, on 12 October 2012 - 07:33 AM, said:

What character set is the database set up for?

I'm not sure, I'm trying to find out how to find out. I don't see any .cnf / config file under my mysql directory. But I know that I can insert Icelandic characters into tables when I do it from the MySQL workbench - so it doesn't sound like that's the issue.

Edit:

When I type SHOW COLLATION into the workbench, I can see a list of character sets, and utf8_general_ci's default value is yes. But the same can be said for a dozen others, as well. There are also several other utf8 options (spanish, swedish, Icelandic), none of which are set to default but I assume they're all included in utf8_general_ci.

This post has been edited by Tenderfoot: 13 October 2012 - 09:25 AM

[Atli]

Which version of PHP are you using again? The "charset" part of the MySQL DSN was ignored until PHP 5.3.6. Before that time you would have to use the SET NAMES MySQL command to set the charset of the connection. You can do that by either executing the query right after connecting, or by using the PDO::MYSQL_ATTR_INIT_COMMAND option.

This is the example from the manual entry.

<?php
$dsn = 'mysql:host=localhost;dbname=testdb';
$username = 'username';
$password = 'password';
$options = array(
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
); 

$dbh = new PDO($dsn, $username, $password, $options);
?>

I've been using something like this for years, and never had trouble using Icelandic chars.

Of course, like CTphpnwb says, the database must also be set up to store UTF8 chars. If the above doesn't work, that is likely the problem.

[Tenderfoot]

Atli, on 13 October 2012 - 09:18 AM, said:

Which version of PHP are you using again? The "charset" part of the MySQL DSN was ignored until PHP 5.3.6.

I'm using PHP version 5.4.6. I'm going to try the code you provided though, and see if it changes anything.

Atli, on 13 October 2012 - 09:18 AM, said:

I've been using something like this for years, and never had trouble using Icelandic chars.

Of course, like CTphpnwb says, the database must also be set up to store UTF8 chars. If the above doesn't work, that is likely the problem.

I've been looking for that option for a bit. I must say that I'm not a big fan of the fact that every single guide on the internet assumes that I am running on a Linux system.

Apparently I could have used this:

CREATE DATABASE mydb
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci;

When I created the database, but it's a bit late for that.

[Atli]

Tenderfoot, on 13 October 2012 - 04:55 PM, said:

I've been looking for that option for a bit. I must say that I'm not a big fan of the fact that every single guide on the internet assumes that I am running on a Linux system.

Most MySQL server, excluding perhaps those used for WAMP development, are set up on Linux. It's what MySQL DBAs typically use. It only makes sense they'd write about it from that perspective. But most of the time the same exact info is valid for Windows as well. Like in this instance, there is no difference between Linux and Windows support for UTF-8 chars. (As far as I am aware, at least.)

Tenderfoot, on 13 October 2012 - 04:55 PM, said:

Apparently I could have used this:

CREATE DATABASE mydb
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci;

When I created the database, but it's a bit late for that.

No, you can use the ALTER DATABASE command to change it.

ALTER DATABASE mydb
    CHARACTER SET = utf8
    COLLATE = utf8_icelandic_ci;

[Tenderfoot]

Atli, on 13 October 2012 - 10:10 AM, said:

Tenderfoot, on 13 October 2012 - 04:55 PM, said:

I've been looking for that option for a bit. I must say that I'm not a big fan of the fact that every single guide on the internet assumes that I am running on a Linux system.

Most MySQL server, excluding perhaps those used for WAMP development, are set up on Linux. It's what MySQL DBAs typically use. It only makes sense they'd write about it from that perspective. But most of the time the same exact info is valid for Windows as well. Like in this instance, there is no difference between Linux and Windows support for UTF-8 chars. (As far as I am aware, at least.)

I don't use WAMP myself. I do get that though, just dislike feeling left out every time I end up having to look something up. A lot of the time I end up with terminal commands or something that in general isn't useful for a Windows 7 user.

Atli, on 13 October 2012 - 10:10 AM, said:

No, you can use the ALTER DATABASE command to change it.

ALTER DATABASE mydb
    CHARACTER SET = utf8
    COLLATE = utf8_icelandic_ci;

I've run this command successfully, so that should be set.

I've also altered my dbh code to:

$dsn = 'mysql:host=localhost;dbname=gluteusbase';
			$username = 'root';
			$password = 'myPswd';
			$options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',);
			$this->dbh = new PDO($dsn, $username, $password, $options);

It inserts into the database when there's no Icelandic character involved, but once I attempt to put some Icelandic characters in there, it fails. However the exception I got before is gone. When I change my database handler code back to my original code, I get this again:

"SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xE1\xED\xF3\xFE\xE6' for column 'exerciseName' at row 1"

So.. huh. I don't know what to make of that. It seems like it isn't throwing an exception when I use your PHP code, but it isn't inserting either.

[Atli]

Tenderfoot, on 13 October 2012 - 06:05 PM, said:

I don't use WAMP myself.

I don't necessarily mean the WampServer package thing. WAMP is a general term for Windows servers running Apache, MySQL and PHP/Perl/Python, like LAMP is for Linux. Those guys just kind of assumed the term for their package.


If you want to see the exceptions again, you can add that to PDO options too.

$options = array(
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
);

Even though the ALTER DATABASE command changes the database's default charset, it shouldn't change the charset of existing tables in the database. Try executing this command, as see what it says:

SHOW CREATE TABLE `yourtablenamehere`;

At the bottom of the CREATE TABLE command that shows should be the charset of the table, and if any columns in it have charsets defined on them as well, those should show too.

To change the charset of a table, and all it's text columns, to UTF-8, user the ALTER TABLE command:

ALTER TABLE `yourtablenamehere`
    CONVERT TO CHARACTER SET 'utf8' 
               COLLATE 'utf8_icelandic_ci';

[Tenderfoot]

Atli, on 13 October 2012 - 11:28 AM, said:

Tenderfoot, on 13 October 2012 - 06:05 PM, said:

I don't use WAMP myself.

I don't necessarily mean the WampServer package thing. WAMP is a general term for Windows servers running Apache, MySQL and PHP/Perl/Python, like LAMP is for Linux. Those guys just kind of assumed the term for their package.


If you want to see the exceptions again, you can add that to PDO options too.

$options = array(
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
);

Ahh. I never realized that - I thought WAMP was just the erm, simple set-up package for PHP/MySQL.

Atli, on 13 October 2012 - 11:28 AM, said:

Even though the ALTER DATABASE command changes the database's default charset, it shouldn't change the charset of existing tables in the database. Try executing this command, as see what it says:

SHOW CREATE TABLE `yourtablenamehere`;

At the bottom of the CREATE TABLE command that shows should be the charset of the table, and if any columns in it have charsets defined on them as well, those should show too.

To change the charset of a table, and all it's text columns, to UTF-8, user the ALTER TABLE command:

ALTER TABLE `yourtablenamehere`
    CONVERT TO CHARACTER SET 'utf8' 
               COLLATE 'utf8_icelandic_ci';

What I got was this:

'CREATE TABLE `exercise` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`exerciseName` varchar(50) NOT NULL,
`description` varchar(255) NOT NULL,
`warning` varchar(100) DEFAULT NULL,
`videoPath` text NOT NULL,
`deprecated` bit(1) DEFAULT b''0'',
PRIMARY KEY (`ID`),
UNIQUE KEY `exerciseName` (`exerciseName`)
) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=utf8'

So it does say that the default charset is utf8 already.

I'm going to try that ALTER TABLE command though, and see if it changes anything.

Still getting the same error (I altered your code as you suggested - so the exceptions are back). I uff.

So uhm.

What we know now is that:
1) My MySQL is set to utf8 (as far as I can tell).
2) My database handler in PHP has its options set to utf8 (either via "charset=utf8" or PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',)

So, I don't know. Do you think I should try to install mbstring?

This post has been edited by Tenderfoot: 13 October 2012 - 11:51 AM

[Atli]

No, that really shouldn't be necessary. Not for this.

The next thing I would do is check the charset used by the HTML document submitting the data. I assume the data is coming from a HTML form? - If the HTML document is encoded using, say, ISO-8859-1 (the equivalent to latin1 in MySQL terms), then the browser may attempt to escape the UTF chars in much the same way your errors are showing them. In proper UTF-8 strings, you wouldn't see that kind of escaping.

The solution to that would be to set the charset of the page to UTF-8, either by passing it in a PHP header or setting a HTML meta tag in the <head>:

// PHP
header("Content-type: text/plain; charset=utf-8");

// HTML 5
<meta charset="UTF-8">

// HTML 4 and XHTML 
// (Or rather: XHTML as HTML. True XHTML has it's own mime type.)
<meta http-equiv="Content-Type" content="text/plain; charset=UTF-8">

(One of these meta tags should always be present in a HTML document. If you pass the charset in the HTTP header, it's not technically necessary, but I'd still recommend it. You never know when some newbie server admin will mess up your HTTP header )

You can also set the accept-charset attribute on the <form> element itself. That should override any page-wide charset rules when dealing with form data.

This post has been edited by Atli: 13 October 2012 - 12:36 PM

[Tenderfoot]

Atli, on 13 October 2012 - 12:35 PM, said:

No, that really shouldn't be necessary. Not for this.

The next thing I would do is check the charset used by the HTML document submitting the data. I assume the data is coming from a HTML form? - If the HTML document is encoded using, say, ISO-8859-1 (the equivalent to latin1 in MySQL terms), then the browser may attempt to escape the UTF chars in much the same way your errors are showing them. In proper UTF-8 strings, you wouldn't see that kind of escaping.

The solution to that would be to set the charset of the page to UTF-8, either by passing it in a PHP header or setting a HTML meta tag in the <head>:

// PHP
header("Content-type: text/plain; charset=utf-8");

// HTML 5
<meta charset="UTF-8">

// HTML 4 and XHTML 
// (Or rather: XHTML as HTML. True XHTML has it's own mime type.)
<meta http-equiv="Content-Type" content="text/plain; charset=UTF-8">

(One of these meta tags should always be present in a HTML document. If you pass the charset in the HTTP header, it's not technically necessary, but I'd still recommend it. You never know when some newbie server admin will mess up your HTTP header )

You can also set the accept-charset attribute on the <form> element itself. That should override any page-wide charset rules when dealing with form data.

Ultimately I do intend to use forms, but thus far I've just been setting the values manually:

<?php
require_once 'class.Exercise.php';
//require_once 'class.SQLConnection.php';

$exercise = new Exercise("áíóþæ", "Fantastic exercise, as the name implies. I'm forced to use more than 25 characters", "Huge warning", "google.com", "strength");

$exercise->exerciseInsert(); 
$exercise->selectAllExercise();
?>

And as long as I don't do.. what I did, in the first column, that is, insert Icelandic letters, this runs just fine.

Now, just for the sake of trying things out, I inserted this at the top:

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

The results were somewhat odd. All of a sudden, every Icelandic character on the page turned to a question mark. I would have assumed that.. well, I would have assumed that that wouldn't be the case.

So erm, what we can take from this.. is that.. my .php file (working with eclipse) is sending Icelandic characters in some other format than UTF-8? We know MySQL takes the Icelandic characters when working with it directly - and now that, when I add the UTF-8 metatag that all the Icelandic characters get question marked out. So, perhaps it has to do with the encoding of eclipse's php files?

This post has been edited by Tenderfoot: 14 October 2012 - 07:16 AM

[Tenderfoot]

This works:

$string = "áíóþæ";
$string = utf8_encode($string);

That's just a quick solution though. I'd rather change eclipse's default encoding to UTF-8. But at least I'm aware of where the problem lies.

I found something about it being possible to use Notepad++ on the file to convert it to UTF-8, but again that doesn't seem like something I want to do every single time.

This post has been edited by Tenderfoot: 14 October 2012 - 07:53 AM

[Tenderfoot]

This looks like it may be what I'm looking for:
http://www.eclipsezo...ms/t93442.rhtml

To sum it up: "Right click the project name in Eclipse > Go to Resource (default) > Change text file encoding to other - UTF-8".

Update: That did the trick. On one hand, I can now enter Icelandic characters into the database directly. On the other, everything I've written in the file with Icelandic characters already got jumbled. But I'll fix that in a jiff. Thanks all

This post has been edited by Tenderfoot: 14 October 2012 - 08:34 AM