CODEWITHSUNDEEP
phpmysqlutf-8
Dennis Hackethal
Dennis Hackethal

Reputation: 14275

UTF-8 works inside database, but not with strings from database

I am quite new to utf-8 encoding and have used htmlentities() ever since, but now want to change to have utf-8 in the database. I thought this might come in very handy, since as the website I am programming is German, I am using lots of umlauts.

However, it occurs to me I have a problem that I can't explain. In the html head, I say

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

For testing, if I type a plain "ö" and hardcode it in html, it is displayed fine.

Now here comes the queer thing: In my database, I have an entry "Köln" - in the database it looks exactly like this. However, when I get the string from the table with php and echo it out, it is echoed out as "K�ln".

On the other hand, if I type in umlauts and send that to the table with ajax, "Köln" - the string that I typed in - becomes "Köln" in the database.

I use the following code for that:

//Change city
$('#newcitybutton').click(function(){
//Get id and city
var id=encodeURIComponent($('#id').html()); //This does not seem to be the problem - if I leave that out, I still does the same thing
var city=$('#newcity').val();

//Call script to change city
$.ajax({
    type: 'POST',
    url: 'action/changecity.php',
    data: 'id='+id+'&city='+city+'&kind='+kind,
    success: function(feedback){
        var json=$.parseJSON(feedback);
        if(json.success=='false'){
            $('#newcityfeedback').css('color','red').html(json.message).hide().fadeIn(200);
        }else if(json.success=='true'){
            $('#newcity').hide();
            $('#newcitybutton').hide();
            $('#'+kind+'city').html(decodeURIComponent(city));
            $('#newcityfeedback').css('color','green').html(json.message).hide().fadeIn(200).delay(2000).fadeOut(200);
        }
    }
});
});

Changecity.php:

//Save in vars
$id=$_POST['id'];
$city=$_POST['city'];
$kind=$_POST['kind'];

//Update city
$query="UPDATE ".$kind."s SET city='$city' WHERE id=$id";
mysql_query($query);

I figured that when I use $city=utf8_decode($city); before putting in in the database, it is saved there correctly. But then I need to use utf8_encode to display it correctly on the page.

What am I doing wrong here? I just can't figure out whether the mistake comes from my code, or the database.

Upvotes: 0

Views: 207

Answers (3)

CodeZombie
CodeZombie

Reputation: 5377

You also need to set the encoding used by MySQL for the current connection.

Using PDO, pass the following options to the PDO class constructor:

$pdoOptions = array(
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
);


Using the the deprecated MySQL extension, execute the following query right after you created the connection:

mysql_query("SET NAMES utf8");

Check this stackoverflow question for more information: SET NAMES utf8 in MySQL?

Upvotes: 1

mvillaress
mvillaress

Reputation: 509

Try using the PHP function mysql_set_charset() with "UTF8" as parameter.

http://php.net/manual/en/function.mysql-set-charset.php

Upvotes: 3

dpitkevics
dpitkevics

Reputation: 1258

You can set name encoding after db connection - follow this tutorial: http://www.oreillynet.com/onlamp/blog/2006/01/turning_mysql_data_in_latin1_t.html/

Upvotes: 0

Related Questions