Problem with accented characters (ADODB + MySQL + PHP)

Question

I'm experiencing a problem developing a project where I can't put accented characters.

I'm using to make the connection to the MySQL database, Adodb in version 5.20.14.

I have a main configuration file where the connection is set as follows:

$s_driver = "mysqli";
$o_db     = adoNewConnection($s_driver);
$o_db->connect($s_dbhost,$s_dbuser,$s_dbpasswd,$s_dbname);
$o_db->SetFetchMode(ADODB_FETCH_ASSOC);
$o_db->setCharset("utf8");

All files developed are UTF-8 encoded, as shown in the image below:

Image 02

NOTE: I use VsCode.

All PHP files that are pages, have the charset meta set to utf-8, as shown in the image below:

<!DOCTYPE html>
<html lang="pt-br">
  <head>
    <meta charset="utf-8">

My database is configured for utf8, as shown in the image below:

Image 04

The tables are configured with the utf8 charset, as shown in the image below:

Image 5

The columns are configured with the utf8 charset, as shown in the image below:

Image 6

NOTE: I added the "NOT NULL" rule to ignore tables that do not have a charset configuration.

When executing the query to include the information in the database, I run as follows:

$s_query_incluir = "INSERT INTO agtb_ordensdeservicos(id_agenda,
                                    id_empresa,
                                    hora_ini,
                                    hora_fim,
                                    observacoes,
                                    tipo,
                                    csa)
                        VALUES('".$a_post['add_id_os_dt_agenda']."',
                               '".ID_EMP_ATUAL."',
                               '".$a_post["add_os_hora_ini"]."',
                               '".$a_post['add_os_hora_fim']."',
                               '".$a_post['add_observacao']."',
                               '".$a_post['add_os_tipo']."',
                               '".$a_post['add_os_csa']."');";

$o_db->execute($s_query_incluir);

NOTE: at the top of the file I include the configuration file that contains the information shown in the first image.

After performing this operation, the database shows the information as follows:

Image 8

When viewing the information on the website, it appears as follows:

Image 9

The original text being this:

Image 10

I managed to make it work by adding the "setCharset" before giving the "execute" in the query, as shown in the image below:

$s_query_incluir = "INSERT INTO agtb_ordensdeservicos(id_agenda,
                                    id_empresa,
                                    hora_ini,
                                    hora_fim,
                                    observacoes,
                                    tipo,
                                    csa)
                        VALUES('".$a_post['add_id_os_dt_agenda']."',
                               '".ID_EMP_ATUAL."',
                               '".$a_post["add_os_hora_ini"]."',
                               '".$a_post['add_os_hora_fim']."',
                               '".$a_post['add_observacao']."',
                               '".$a_post['add_os_tipo']."',
                               '".$a_post['add_os_csa']."');";
$o_db->setCharset("utf8");
$o_db->execute($s_query_incluir);

Achieving the following result in MySQL:

Image 12

I would like to understand where I am going wrong. I'm trying to make utf8 "automatic" without having to call "setCharset" before any kind of query execution.

I appreciate any kind of help. :)

NOTE: if you need more information about the process to better understand the problem, just let me know.

See "Mojibake" in https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored . And perform the suggested `SELECT HEX(col)...` to help determine whether the text was garbled during `INSERT` or later. — Rick James, Aug 21 '20 at 00:37
I don't remember hearing of anyone using adodb. What is its advantage? — Rick James, Aug 21 '20 at 00:42
What does `setCharset` do under the covers? (This is critical.) — Rick James, Aug 21 '20 at 00:43

score 0 · Accepted Answer · answered Sep 02 '20 at 15:05

I have identified the reason for the problem.

Even configuring the database, tables and columns for UTF-8 / utf8_general_ci and the PHP file in UTF-8, the problem of strange characters remained.

The problem was in the standard PHP functions that format text, including:

strtolower()
strtoupper()
ucfirst()
ucwords()

When executing these functions in accented texts (for example, "acentuação"), their return was lost. After removing these functions from my entire project, the strange character problem in the database stopped occurring (including and querying through PHP and through MySQL Workbench).

I hope this can help other people who end up experiencing the same problem as me. :)

NOTE: it took me a long time to answer because I was only able to validate this information after placing these changes in a production environment.

Glad you found it. You should therefore look into PHP `mbstring` and your SQL should be using `utf8mb4_` — Martin, Sep 02 '20 at 15:07

Problem with accented characters (ADODB + MySQL + PHP)

1 Answers1