Mysql charset

Hi,

small problem using utf8 in my mysql database, æøå that is. This is what I have tried (and made sure of) so far:

1. main.php - <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

2. main.php config db - ‘charset’=>‘utf8’,‘initSQLs’=>array(‘SET NAMES utf8’),

3. default charset in mysql utf8

4. default chharset in Apache utf8

5. default charset in php utf8

6. using eclipse, checking that text file encoding is utf8

Right now æøå is showing in the website after models are fetched from database, but are presisted as �. If I use the command line to insert into mysql, the characters are shown correctly.

Any help is appreciated.

Thanks

on your main.php config file:

'db' => array(

			...

			'charset' => 'utf8',

		),

Hi Antonio,

and thank you for your reply. As posted I have the following in the configuration of Mysql in main.php.

2. main.php config db - ‘charset’=>‘utf8’,‘initSQLs’=>array(‘SET NAMES utf8’),

Maybe a stupid formatting

Something else I could check?

Sorry, that is happening to me for not reading through…

Maybe it is a double converted, what is that character æøå supposed to be?

also, check the encode of the page you are displaying the text, it must be UTF-8 also

(æøå are the three characters ‘æ’,‘ø’,‘å’ corresponding to ‘ä’,‘ö’,‘å’ in my language.)

Please tell us more about the view and controller code (Gii generated?) as well as the (command line/web/browser) environment. Perhaps take a closer look at the text posted back from the form (if applicable) before saving it to db. What about phpMyAdmin, what do you see, what is the result if you enter data from there?

/Tommy

AFAIK this is not necessary for displaying data from db to html fields.

/Tommy

true but, For me, it is necessary to display the static text correctly

Olemara:

Also, you might have saved the data before you make the changes you wrote, and that caused it to save to the db incorrectly, try save something now and display it

Seems to be some confusion here since I’m not the person that asked the question

Let’s await more info about which steps were performed in which environment.

(Or perhaps user olemara can use your suggestion for solving his problem right away.)

/Tommy

Hi guys,

and thank you for the replies. Sorry for my own response time as I have been on vacation for some time, without computer.

ÆØÅ are Norwegian characters, and Swedish in Tommy’s case

I have checked that the page is utf-8 encoded as in "<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />", on the top of the page + the saved file is also utf-8.

Some info on the environment:

Apache2 on Ubuntu, using eclipse/firefox.

Much of the code this far is Gii generated, the controller skeleton is anywas. Here is the form i am posting to the controller.

<div class="form">


<?php $form=$this->beginWidget('CActiveForm', array(

	'id'=>'user-form',

	'enableAjaxValidation'=>false,

)); ?>


	<p class="note">Felt med <span class="required">*</span> må fylles inn.</p>


	<?php echo $form->errorSummary($model); ?>

	<?php echo $userType; ?>

	<div class="row">

		<?php echo $form->labelEx($model,'firstName'); ?>

		<?php echo $form->textField($model,'firstName',array('size'=>20,'maxlength'=>20)); ?>

		<?php echo $form->error($model,'firstName'); ?>

	</div>


	<div class="row">

		<?php echo $form->labelEx($model,'lastName'); ?>

		<?php echo $form->textField($model,'lastName',array('size'=>20,'maxlength'=>40)); ?>

		<?php echo $form->error($model,'lastName'); ?>

	</div>


	<div class="row">

		<?php echo $form->labelEx($model,'email'); ?>

		<?php echo $form->textField($model,'email',array('size'=>20,'maxlength'=>60)); ?>

		<?php echo $form->error($model,'email'); ?>

	</div>


	<div class="row">

		<?php echo $form->labelEx($model,'password'); ?>

		<?php echo $form->passwordField($model,'password',array('size'=>20,'maxlength'=>65)); ?>

		<?php echo $form->error($model,'password'); ?>

	</div>

	

	<div class="row">

		<?php echo $form->labelEx($model,'password_repeat'); ?>

		<?php echo $form->passwordField($model,'password_repeat',array('size'=>20,'maxlength'=>65)); ?>

		<?php echo $form->error($model,'password_repeat'); ?>

	</div>

	

	<!-- Used to set the type on the model itself -->

	<div>

		<?php echo $form->hiddenField($model, 'type', array('value'=>$userType))?>

	</div>

	

	<!-- For the selection of user type to work with setting errors, we need the user type -->

	<div class="row">

		<?php echo CHtml::hiddenField('userType', $userType, array()); ?>

	</div>

	

	<div class="row buttons">

		<?php echo CHtml::submitButton($model->isNewRecord ? 'Registrer' : 'Lagre'); ?>

	</div>


<?php $this->endWidget(); ?>


</div>

I have tried with setting HTML attributes here as well, with no luck. Maybe I overlooked something though.

Trying to use myphpadmin i try to insert the following statement

mysql> insert into User (id,email) values (110,"lajskfæøå")

and get the error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql> insert into User (id,email) values (110,"lajskfæøå")' at line 1

The statement does work in mysql though.

I did not quite get that Gustavo, could you elaborate?

Thanks guys!

add to the root directory of your application file .htaccess, which contains string

AddDefaultCharset utf-8

phpMyAdmin should work.

I reproduced the insert command without problem using Firefox 5 on Windows XP and Firefox 3.6.18 on Ubuntu 10.04 (where MySql is also installed). I used the string "lajskfæøå" copypasted from your recent post.

More info from phpMyAdmin start page (on Ubuntu)

/Tommy

@Layne: Thanks for the tip. Tried that, but no success.

Hm, I tried inserting via phpmyadmin again and got a successfull insertion. But the characters is still persisted as ��.

Here’s my info

MySQL


    Server: Localhost via UNIX socket

    Server version: 5.1.54-1ubuntu4

    Protocol version: 10

    User: root@localhost

    MySQL charset: UTF-8 Unicode (utf8)


Web server


    Apache/2.2.17 (Ubuntu)

    MySQL client version: 5.1.54

    PHP extension: mysqli


phpMyAdmin


    Version information: 3.3.10deb1

So there’s some difference in version numbers. The only thing I can see is the difference in PHP extension. Without knowing exactly what the difference between mysql and mysqli… I’ll check it out and see if it makes any difference.

I’m using Firefox 5 on Ubuntu btw. Tried verifying that the charset is utf-8 here as well. Hm, weird that this is so hard to figure out this problem.

Well Mysqli is mysql improved, with same funcionality, but augmented in some different ways: security, OO interface, faster etc. I guess this should not be a problem.

What exactly do you mean by "still persisted as ��"? Are you able to view the inserted record correctly in phpMyAdmin?

/Tommy

I do a bunch of stuff to guarantee that charset is always UTF-8

here is a list:

set Yii charset to utf-8

add to the connection config ‘charset’=>‘utf-8’

add to .htaccess "AddDefaultCharset utf-8"

add to all requests a charset header in the config file, like

header("Content-Type: text/html;charset=".Yii::app()->charset);

add to the clientScript a metatag like the header above

Yii::app()->getClientScript()->registerMetaTag("Content-Type","text/html; charset=".app()->charset,"Content-Type");

whenever I find a character like you the one you posted, I make sure that the file that outputs the text has the encoding utf-8 without bom

strtolower and strtoupper php’s functions do not handle utf-8 correctly

what I use whenever a have a problem with those functions is

$output = mb_strtolower($output, "UTF-8");

$output = mb_strtoupper($output, "UTF-8");

also, javascript files must have utf-8 encoding without bom

maybe it is a little too much, but its always better to prevent possible problems

Hope this helps

When inserting through phpmyadmin, I can view the inserted record correctly with phpmyadmin, as I also can in the Yii web site (I.e. viewing a record/model). But then æøå is shown as � when I query mysql through the command line interface.

If I insert a record through the mysql command line interface, for ex. å@å.com, it appears as Ã¥@Ã¥.com when I view it in phpmyadmin. But it is correct when viewed with the command line interface.

@Gustavo - Thanks for the tips, most of them I have tried, but I’ll look through my settings and verify that I follow all your guidelines.

Please take a look here.

For others experiencing the same problem. The default charset parameter needs to be set for both the client and mysqld in the mysql config my.cnf.

I’m curious about what value you will see for LANG=, if you type env from the command line.

In my case:

LANG=en_US.UTF-8

/Tommy