CODEWITHSUNDEEP
mysqlutf-8character-encoding
Peter
Peter

Reputation: 3184

MySQL European Characters

I can't figure this out for the life of me.

I have a query that pulls translations of elements on a page. So any number of 15 languages can appear on that page. When I start to add languages like Swedish anything that has a symbol such as ö results in the whole field returning a null string.

I've verified the encoding on the table and it claims it's using UTF-8 but seeing as how this doesn't work I'm confused.

Here is the query I'm working with:

SELECT
   form.form_id,
   elem.elem_type,
   elem.elem_name,
   elem.elem_format,
   elem.elem_required,
   trans.trans_label,`
   trans.trans_description,
   trans.trans_defaultValue,
   trans.trans_other,
   elem.elem_advancedcommand
FROM
   events_form form
LEFT JOIN
   events_form_elements elem
ON
   form.event_id = elem.event_id
INNER JOIN
   events_form_translations trans
ON
   elem.elem_id = trans.elem_id
INNER JOIN
   events_form_languages lang
ON
   trans.lang_id = lang.lang_id
WHERE 
   form.form_id = '{$formid}' AND lang.language = '{$language}'
ORDER BY
   elem.elem_sortorder

Now I tried to do something like:

CONVERT(CAST(trans.trans_description as BINARY) USING latin1) as trans_description,

To force it to covert the encoding but that doesn't yield a result at all.

After I get the result it's immediately json_encoded and returned to the user (Ajax Request). I DON'T think it's the json_encode as doing a print_r of the output array yields the same issues.

Also.. lastly, the system I'm building on is using xPDO so I'm not too sure if that's the issue either.

EDIT: It seems that PHP IS returning a correct value or at least a value for example here is a print_r dump:

[trans_label] => Ditt f�rnamn?
[trans_description] => 
[trans_defaultValue] => First Name

So it seems that when my json_encode touches that string is when it turns the string to null.

Upvotes: 6

Views: 991

Answers (4)

Sindri Þór
Sindri Þór

Reputation: 2927

I've had problems endcoding letters to my native language Icelandic but ive found a mutual solution for all utf8 letters.

right after mysql_select_db and before mysql_query insert the following:

 mysql_query("SET character_set_connection=utf8, 
 character_set_results=utf8, 
 character_set_client=utf8", $con);

Where $con is the connection to mysql

Happy coding..

Upvotes: 1

Joni
Joni

Reputation: 111219

Your PDO connection string should specify the encoding. For example:

mysql:host=localhost;port=3306;dbname=test;charset=utf8

This controls the encoding that the database driver will use when it returns a result, and the encoding the driver assumes your queries are in. If you don't specify it, the default encoding will be used. Often the default is latin1.

You can confirm this by printing the hexadecimal representation of the data with bin2hex in PHP: the ö in förnamn is being returned as f6. If the text was encoded in UTF-8 you would obtain c3b6.

Upvotes: 10

JDGuide
JDGuide

Reputation: 6525

Your answer is null after conversion due to incompatibility of data types.But showing European or Arabic characters on the page is quite simple.I had the same problem with Arabic language, but after few experiment its works fine now.

If you want to show those European characters on the page (jsp,php,html) first set the page encoding to UTF-8 like: -

pageEncoding="utf-8"

And also you need some changes on your database connection class for utf-8 characters

Use the code below:-

jdbc:mysql:your_ipaddress":3306/"+db+"?requireSSL=false&useUnicode=true&characterEncoding=UTF-8

Hope it will help you.

Upvotes: 0

Jocelyn
Jocelyn

Reputation: 11393

You said nothing about the encoding of your web pages.
Do you have that line in the <head> section of your page to force the encoding to UTF-8?

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

Upvotes: 3

Related Questions