0

I'm having trouble with some character encoding/charset

I retrieve some rows from a table, 1 of which is varchar. Whenever there's æ, ø or å in the varchar column they get translated to a seemingly bogus character. Same bogus char for each of the 3.

I created the table like this

CREATE TABLE `mytable` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`account_id` int(11) DEFAULT NULL,
`name` varchar(100) DEFAULT NULL,
`mytext` int(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1402 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I'm guessing it has something to do with the collate or charset, but haven't been able to find anything that solved this.

KristianMedK
  • 1,659
  • 6
  • 24
  • 52

2 Answers2

5

You have to set the charset on (at least) 3 places:

  1. The document:

    <head>
    <meta charset="utf-8">
</head>

    See the official w3c pages for more information.

  2. The database:

    ALTER DATABASE `my_database` CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `my_table` CONVERT TO CHARACTER SET utf8;
ALTER TABLE `my_table` MODIFY `my_column` … CHARACTER SET utf8 COLLATE utf8_general_ci;

    In case you wonder, see the difference between utf8_unicode_ci and utf8_general_ci.

  3. The connection, using PHP's PDO extension with the charset in the DSN:

    $DBH = new PDO(
    'mysql:host=http://example.com;dbname=mydb;charset=utf8', 
    'username', 
    'password');
Community
  • 1
  • 1
arielnmz
  • 8,354
  • 9
  • 38
  • 66
  • 1
    [Declaring character encodings in HTML](http://www.w3.org/International/questions/qa-html-encoding-declarations) – Gumbo Jun 20 '14 at 07:52
0

While retrieving record from database set it to UTF8 it will solve your problem. Use the following code hope it'll help you

$con=mysqli_connect("localhost","username","password","db_name");
$con->set_charset("utf8"); 
$result = mysqli_query($con,"SELECT * FROM mytable");
Awais Usmani
  • 188
  • 1
  • 10
  • 5
    Plase don't encourage the use of the `mysql` extensions, provide samples with either the `mysqli` extensions or `PDO`. – arielnmz Jun 20 '14 at 07:22