How to deal with accents and strange characters in the database?

Question

im trying to save Spanish words with an accent in my database, but this will not work, I already tried:

1) configuration change from tables and rows to utf8_spanish_ci and utf_unicode_ci.

2) adding a title tag using

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

3) add

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

in php tag.

Doing this on the xampp server on my laptop will work, but when I upload the database to the login monster server, it will not save the emphasis properly.

edit: this is an im connection 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 keep the accent, it shows how strange characters like á = á

Answer 1

Collation only affects the sorting of text; it does not affect the actual set of stored data.

I would recommend this configuration:

• Set the character set only for the entire database , so you do not need to install it for each table separately. The character set is inherited from the database to the tables in columns. Use utf8 as a character set.

• Set the character set for connecting to the database . Run these queries after connecting to the database:

   SET CHARACTER SET 'utf8' SET NAMES 'utf8' 

• Define the character set for the page using the HTTP header and / or HTML meta tag. One of them is enough. Use utf-8 as a charset .

That should be enough.

If you want to have Spanish rows sorted correctly, set the sort order for the entire database. utf8_spanish_ci should work ( ci means case insensitive). Without proper matching, accented Spanish characters will always be sorted last.

Note : it is possible that the character set that you already have in the table is broken because the incorrect character set configuration was previously installed. You must first check it with some DB client to rule out this case. If it is broken, just paste your data into the desired character set configuration.

How a character set works in a database

• objects have a character set attribute that can be set explicitly or inherited (server> database> table> column), so it’s best to set it for the entire database

• the client connection also has a character set attribute, and it tells the database in which the encoding is "re sending data

If the character set of the client and the target is different, the data that you send to the database is automatically converted from the character set of the connection to the character set of the object.

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

Answer 2

Here is my checklist for storing UTF8 characters. Although, do not forget to isolate the reason for the failure from where you store the rows in the database - this means that the row for storage is still executed as it was when the user entered it.

. . Make sure you use the table utf8 character set, or even better use utf8mb4 for full Unicode support (although it has its drawbacks too). It does not matter what encoding is set for the entire database; it is overridden by the table definition, if specified. The DDL code for creating such a table will look like this:

 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 to connect to the database.

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

Answer 3

For MySql Use this code after calling the database connection:

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

Answer 4

I had to store many underlined letters from different languages ​​(including French and Spanish), and the only safe way that I found at the moment was to save everything in utf8_ bin in MySQL and display the pages in charset utf-8 like you do. No further processing is required from either MySQL or PHP.

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