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

Question

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.

Is the character set of your database 100% definitely set to UTF8? Is the data you are inserting into the database 100% definitely encoded as UTF8? — DaveRandom, Aug 07 '12 at 12:50

score 2 · Accepted Answer · answered Aug 07 '12 at 12:52

2

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

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

answered Aug 07 '12 at 12:52

mvillaress

489
3
6

Works perfectly! I did it like this: `mysql_set_charset('utf8',$identifier);` after establishing the connection and saving it in `$identifier` Thank you a lot! – Dennis Hackethal Aug 07 '12 at 12:59

score 1 · Answer 2 · edited May 23 '17 at 10:34

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?

score 0 · Answer 3 · answered Aug 07 '12 at 12:50

0

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/

answered Aug 07 '12 at 12:50

dpitkevics

1,238
8
12

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

3 Answers3