4

I've got a MySQL problem. My whole website except my database is in UTF8. I just recently tried to convert it to UTF8 with all kind of commands, but the main issue is still there:

the accents don't work. Instead of é, I've got é. Same for è, there is another type of weird character.

Is there an easy to convert all these charaters, let's say é, to é, in the base?

THanks a lot.

(by the way, the database is in latin1)

Yannick Bloem
  • 147
  • 2
  • 4
  • 12
  • possible duplicate of [MySQL Convert latin1 data to UTF8](http://stackoverflow.com/questions/1440837/mysql-convert-latin1-data-to-utf8) – Wooble Apr 18 '13 at 16:50

3 Answers3

6

In this case, é is é interpreted as LATIN1 and converted to UTF-8. It might be that your database is trying to be helpful and converts to UTF-8 where no conversion is required.

Have you tried switching the character set on your database tables?

tadman
  • 208,517
  • 23
  • 234
  • 262
  • Yes I have actually. It says it's in UTF8 but still the same issue. Weirdly. But is there a solution to convert the already existing characters to normal ones? – Yannick Bloem Apr 18 '13 at 16:57
  • I've got an [answer for a related question](http://stackoverflow.com/questions/9304485/how-to-detect-utf-8-characters-in-a-latin1-encoded-column-mysql) that addresses this. That could be switched to an `UPDATE` statement if you're feeling brave, and have backups. – tadman Apr 18 '13 at 17:14
  • 1
    Thanks a lot. I'm going to try. I feel brave and I have backups, so why not :-) – Yannick Bloem Apr 18 '13 at 19:23
2

You could try this function :

CREATE TABLE `utf8decodemap` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `encoded` VARCHAR(128) NOT NULL,
  `decoded` VARCHAR(128) NOT NULL,
  UNIQUE KEY urlcodemapUIdx1(encoded),
  PRIMARY KEY (`id`)  
);

INSERT INTO utf8decodemap (decoded,encoded) VALUES ("â","â");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("é","é");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("è","è");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("ê","ê");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("ë","ë");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("î","î");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("ï","ï");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("ô","ô");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("ö","ö");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("ù","ù");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("û","û");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("ü","ü");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("ç","ç");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("œ","Å?");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("€","â¬");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("°","°");
INSERT INTO utf8decodemap (decoded,encoded) VALUES ("à","Ã");

DELIMITER $$
CREATE FUNCTION `utf8decoder`(str VARCHAR(4096)) RETURNS VARCHAR(4096) DETERMINISTIC
BEGIN
               DECLARE X  INT;               
               DECLARE chr VARCHAR(256);
               DECLARE chrto VARCHAR(256);
               DECLARE result VARCHAR(4096);
               SET X = 1;
               WHILE X  <= (SELECT MAX(id) FROM utf8decodemap) DO
                   SET chr = (SELECT `encoded` FROM utf8decodemap WHERE id = X);
                   SET chrto = (SELECT `decoded` FROM utf8decodemap WHERE id = X);                
                           SET str = REPLACE(str,chr,chrto);
                           SET  X = X + 1;                           
               END WHILE;
               RETURN str;
       END$$

DELIMITER ;

ex : SELECT utf8decoder(MyCol) from MYtable

Louk Oum
  • 21
  • 3
-1

thanks for the list above and maybe this PHP script can help someone

1- solution A

$title = mb_convert_encoding($article['post_title'], 'ISO-8859-1', 'UTF-8');

2- solution B

function convertSpacialChars($text)
{
    $charsMap = [
        "â" => "â",
        "é" => "é",
        "è" => "è",
        "ê" => "ê",
        "ë" => "ë",
        "î" => "î",
        "ï" => "ï",
        "ô" => "ô",
        "ö" => "ö",
        "ù" => "ù",
        "û" => "û",
        "ü" => "ü",
        "ç" => "ç",
        "Å?" => "œ",
        "â¬" => "€",
        "°" => "°",
        "Ã" => "à",
    ];
    return str_replace(array_keys($charsMap),array_values($charsMap),$text);
}
Anas Alpure
  • 510
  • 5
  • 9