Home / Questions / 59489
Accepted
Dev 200
Asked: 2020-04-01 04:53:41 +0800 CST 2020-04-01 04:53:41 +0800 CST

Why does the Inspector Character (�) appear in some data obtained from the Database?

  • 772

I was dealing with the dilemma of converting accents and special characters from my system.

It happens that now some of the data obtained from the BBDD that have accents come out with this: �.

The strange thing is that there can be up to 20 data displayed with accents but only some come out, so SANCI�Nwhat could be happening?

The only way is to put this<meta http-equiv="content-type" content="text/html; charset=UTF-8">

But despite being on the forms, in some cases the �

Dynamically generated data gives that error

CONNECTION DATA:

config.ini

;<?php
;die(); // /* No modificar sino sabe lo que hace */
;/*
[database]
driver="mysql"
host="localhost"
port="3306"
schema="bbdd"
username="root"
password="pass" 
encode="utf8" 
;*/

Connection.php :

<?php

<?php
$file = 'config.ini.php';
$config = parse_ini_file($file, true);
$host = $config['database']['host'];
$user = $config['database']['username'];
$pass = $config['database']['password'];
$schema = $config['database']['schema'];
$encode = $config['database']['encode'];
class conexion extends mysqli

    {
    public

    function __construct($host, $user, $pass, $schema)
        {
        parent::__construct($host, $user, $pass, $schema);
        if (mysqli_connect_error())
            {
            die();
            }
        }
    }

$conexion = new conexion($host, $user, $pass, $schema);
mysqli_set_charset( $conexion, $encode);
?>
html

4 Answers

  1. Best Answer

    When the coding fails to present our information, it is necessary to take a path back to the data to determine where the problem is.

    When I say return path, I mean that we must begin to check on the surface and go deeper, to debug the problem. An example of a return path would be:

    Documento                          (HTML-Javascript-CSS, etc)   > Nivel 1
   Servidor                        (PHP u otro lenguaje)        > Nivel 2
      Conexión a la Base de Datos  (PDO, MySQLi u otros)        > Nivel 3 
          Configuración de la BD   (MySQL u otros)              > Nivel 4

    I consider it to be an intelligent debugging method, because in this case the data that is presented on our screen is taken from a database through a server language and presented on the screen. If we review in this order we will always get to the bottom of the problem , but by levels. It's logical: you can't get to the bottom without going through the surface :)

    In addition, the most precious thing here is the data , so the less we touch or alter it, the better. This also implies that it is very important when creating the database to properly configure the database itself, as well as each table and each column... this way we will not have to manipulate its structure, which could be a risk especially if the database already has information, because as is known we could lose it or cause errors in the data... but that is another matter.

    So we start checking from the top to the bottom to see where the problem is.

    Note: Before embarking on this return path, let's consider a possible Level 0 review. It is possible that if we have worked on our content in any text editor such as Notepad or others, the document encoding is not adequate. So depending on the publisher, it would be wise to check the encoding that has been set in our file. This is only in case we have taken the text from an editor.

    This Level 0 is important, especially if we have misconfigured the encoding in the editor and we plan to continue entering data in the database with an encoding that is not what we need.

    1. The HTML configuration

      • Put the following in it <head>:

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

    1. The server configuration (PHP or other)

      mb_internal_encoding("UTF-8");

      Or: default_charset(Since PHP 5.6+ it is set to UTF-8default).

      ini_set("default_charset", "UTF-8");

    VERY IMPORTANT NOTE: Here I am referring to a global PHP configuration , not to applying encoding/decoding functions to the data constantly, which would be an error (which many recommend, due to ignorance, in responses to cases similar to this one). When we have problems with the coding at the PHP level, let's solve it at the root, in the configuration, not applying halfway solutions that will keep us enslaved by constantly applying functions utf8_encodeto data.

    1. The way you connect to the DB, setting the encoding to UTF-8

      • In the case of MySQLi

          /* cambiar el conjunto de caracteres a utf8 */

  if (!$mysqli->set_charset("utf8")) {
      printf("Error cargando el conjunto de caracteres utf8: %s\n", $mysqli->error);
      exit();

  } else {
      printf("Conjunto de caracteres actual: %s\n", $mysqli->character_set_name());
  }

        See: mysqli::set_charset

      • In the case of PDO

        It can be done by sending the following attribute to the connection parameters:

    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'". Example:

               $options = array(
                  PDO::ATTR_EMULATE_PREPARES => false, 
                  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, 
                  PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"
               );
           try {
                 # Intentar la conexión 
                 $pdo = new PDO($dsn, $usr, $pwd, $options);
           }
           catch (PDOException $e) {
                 # Escribir posibles excepciones en el error_log
                 error_log($e->getMessage(),0);
           }
           ...

    It can also be done by putting the charset in the DSN string, as explained in this PHP Manual Note . For example:

            $pdo = new PDO("mysql:host=localhost;dbname=DB;charset=UTF8");
    1. The encoding of the data itself (which can be: a. a table column, b. a database table, c. the database in general).

    I would go debugging in that order, from 1 to 4.

    Since you say that in some scenario it looks correct, it seems that the problem is in level 3.

    Verify that when you connect to the Database you are setting the encoding to UTF-8.

    • 86

  2. Greetings, have you tried using utf8-default collation.

    enter image description here

    • 6

  3. If you don't want to change the encoding of your web page you can write the following: 

    á -> &aacute;                                  
é -> &eacute;                
í -> &iacute;                 
ó -> &oacute;           
ú -> &uacute;           
ñ -> &ntilde;

    https://www.gestiweb.com/?q=content/problemas-html-acentos-y-eñes-charset-utf-8-iso-8859-1

    • 4

  4. I had the same problem extracting the title of one of my products from the database. It turns out that the product ended with the word Occasion but this one showed it to me in this way.

    enter image description here

    The problem was because I was using a PHP function called substr() and discovered that this function does not respect UTF-8 characters.

    So I decided to put another PHP function that is used in the same way as the one already mentioned, this one is called mb_substr() once I put this function in my project, I stopped having this annoying problem with strange characters.

    Example:

    $texto = 'Ocasión';
echo substr($texto, 0, 3); // imprime: Оcasi�n
echo mb_substr($texto, 0, 3); // imprime: Ocasión

    You don't have to be an expert to realize that both are used in the same way, they do the same job, which is to shorten a very long text, only that one respects the UTF-8 characters and the other does not.

    I hope that my answer has been of great help, a greeting

    • 2