how to deal with accents and strange characters in a database?

Question

im trying to safe spanish words with accent in my database but it won't work, i have already tried:

1) changing conllation from tables and rows to utf8_spanish_ci and utf_unicode_ci.

2)adding a header tag with

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

3)adding

header("Content-Type: text/html;charset=utf-8");

in a php tag.

doing this in an xampp server in my laptop will work, but when i upload the database to a login monster server it wont save the accent properly.

edit: this is the connection im using:

    private function Connect()
    {
        //$this->settings = parse_ini_file("settings.ini.php");
        try 
        {
            # Read settings from INI file, set UTF8
            $this->pdo = new PDO('mysql:host=localhost;dbname=xxxxx;charset=utf8', 'xxxxx', 'xxxxxx', array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

            # We can now log any exceptions on Fatal error. 
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            # Disable emulation of prepared statements, use REAL prepared statements instead.
            $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

            # Connection succeeded, set the boolean to true.
            $this->bConnected = true;
        }
        catch (PDOException $e) 
        {
            # Write into log
            echo $this->ExceptionLog($e->getMessage());
            die();
        }
    }

Edit:

i can't save accent, it shows like strange characters like á = á

Answer 1

Collation affects text sorting only, it has no effect on actual character set of stored data.

I would recommend this configuration:

1. Set the character set for the whole DB only, so you don't have to set it for each table separately. Character set is inherited from DB to tables to columns. Use utf8 as the character set.

2. Set the character set for the DB connection. Execute these queries after you connect to the database:

   SET CHARACTER SET 'utf8'
   SET NAMES 'utf8'
   

3. Set the character set for the page, using HTTP header and/or HTML meta tag. One of these is enough. Use utf-8 as the charset.

This should be enough.

If you want to have proper sorting of Spanish strings, set collation for the whole database. utf8_spanish_ci should work (ci means Case Insensitive). Without proper collation, accented Spanish characters would be sorted always last.

Note: it's possible that the character set of data you already have in a table is broken, because you character set configuration was wrong previously. You should check it using some DB client first to exclude this case. If it's broken, just re-insert your data with the right character set configuration.

How does character set work in a database

• objects have a character set attribute, which can be set explicitly or it's inherited (server > database > table > column), so the best option is to set it for the whole database

• client connection has also a character set attribute and it's telling the database in which encoding you're sending the data

If client connection's and target object's character sets are different, the data you're sending to the database are automatically converted from the connection's character set to the object's character set.

So if you have for example the data in utf8, but client connection set to latin1, the database will break the data, because it'll try to convert utf8 like it's latin1.

Answer 2

Here is my checklist for storing UTF8 characters. Though, be sure to isolate the cause of failure to be on the part where you store the strings into the database -- meaning the string to store is still as it was when the user inputed it.

First. Make sure the character set of the table being used is utf8 or better yet use utf8mb4 for full unicode support (though it has its drawbacks too). It doesn't matter which charset has been set for the entire database; it is overridden by the table definition, if specified. The DDL code for creating such a table would be like:

CREATE TABLE table_name (
    id INT AUTO_INCREMENT NOT NULL,
    name VARCHAR(190) NOT NULL,
    date_created DATETIME NOT NULL,
    PRIMARY KEY(id)
)
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci
ENGINE = InnoDB;

Second. Use utf8 charset for the database connection.

// This should be enough
new PDO(
    'mysql:host=localhost;dbname=xxxxx;charset=utf8mb4;',
    'username',
    'password'
);

Answer 3

For MySql Use these code after invoking the database connection:

$set_utf=$dbh->exec("SET NAMES UTF8"); 

Answer 4

I had to store a lot of accentuated letters from different languages (including french and spanish), and the only safe way I found at the moment was to store everything in utf8_bin in MySQL, and display pages in charset utf-8 like you do. No further processing needed, neither from MySQL, nor from PHP.

Also, make sure your IDE manages your files in utf8.