3

I'm having problems when inserting Norwegian letters (æøå) into a database. The charset coding of my document (using Notepad++) is set to UTF-8. The variable that is being inserted has the proper characters, but when it is inserted a word that should be shown as "spørsmål", is when inserted to the database, shown as "spørsmÃ¥l".

I'm using the following code to insert:

$newContent = htmlspecialchars($_POST['newContent']);

$stmt = $mysqli->prepare("UPDATE info SET frontpageText=?");
$stmt->bind_param('s', $newContent);
$stmt->execute(); 
$stmt->close();

And when connecting to the databse, I've tried using 

$mysqli->set_charset("utf8");
$mysqli->query("SET NAMES 'utf8'");

I've also done the following:

  • The database collation is set to utf8_danish_ci
  • Using header('Content-type: text/html; charset=utf-8'); (PHP header)
  • Using meta charset="utf-8" (in the HTML-head)
  • The document itself is encoded in utf-8
  • Tried running SET NAMES utf8;

The worst part about this, is that I've actually had it working earlier, but I've appearantly broken something (which I don't know what).

Anyone has an idea what could be done to fix this issue?

EDIT: Problem has been solved. Apparently the table wasn't properly set to UTF-8. Ran this code in phpMyAdmin

ALTER TABLE table_name CHARSET = 'utf8';
Qirel
  • 25,449
  • 7
  • 45
  • 62

3 Answers3

3

The character set and the collation have separate encodings (one can be latin1 and the other can be utf8).

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set. Let's make the distinction clear with an example of an imaginary character set.

To diagnose the encoding of a table this query can be run, where ##TABLENAME## should be the actual table name.

SHOW CREATE TABLE ##TABLENAME##

If the encoding is not utf8 it can be altered with

alter table table_name charset = 'utf8';

Here's a thread on collation vs. character set, What does character set and collation mean exactly?.

Community
  • 1
  • 1
chris85
  • 23,846
  • 7
  • 34
  • 51
0

You can convert all characters to html chars and save into database that way. 

mb_convert_encoding($string, 'HTML-ENTITIES', 'utf-8');

All browsers will display it in the right way.

bignick
  • 121
  • 3
  • 2
    It would display correctly in browsers but searching with the db in the future will be a pain. – chris85 Jul 02 '15 at 00:04
  • you can do the same thing with search term and query afterwards in application. You`re right about pain if you do it outside of the application through database gui. That`s the downside of it. – bignick Jul 02 '15 at 00:06
0

Make sure the default table charset or field charset is set to utf-8. I know you've said that database charset is set correctly, but tables may interfere with that if they specify a different charset.

Lex Podgorny
  • 2,598
  • 1
  • 23
  • 40