Accented character problem (ADODB + Mysql + PHP)

Asked

Viewed 112 times

1

Hello guys :D

I’m going through a problem developing a project where I can’t put accented characters.

I am 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 with UTF-8 encoding, as shown below:

Image 02

OBS: I use the Vscode.

All PHP files that are pages, are with the charset meta set as utf-8, as image below:

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

My database is set to utf8, as shown below:

Image 04

Tables are configured with the utf8 charset, as shown below:

Image 5

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

Image 6

OBS: added the "NOT NULL" rule to ignore tables that do not have the charset setting.

When executing the query to include the information in the database, I run it 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);

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

After performing this operation, the database displays the information thus:

Image 8

When viewing the information on the site, appears as follows:

Image 9

Since the original text is this:

Image 10

I was able to make it work by adding the "setCharset" before giving the "execute" in the query, as shown 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);

Getting the following result in Mysql:

Image 12

I’d like to understand where I’m going wrong. I am trying to leave utf8 "automatic" without I need to get called "setCharset" before any kind of query execution.

I appreciate any kind of help. :)

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

php mysql character-encoding utf-8

  • What is the result of the query in the terminal? Also with encoding error?

  • You mean in the query? I put in the main file a query with a "select" test and the character "is" is displayed normally on the screen. Running this same query in Mysql Workbench, the character "is" is presented as "Ã". If I go to the Workbench itself and change this "À" record to "is" and query in PHP, it gets the character as "?".

  • Have you tried inserting a line with PHP strings into the database without using $_POST content? Also try to treat the content that came from $_POST using the utf8_decode function().

  • @wensiso, In the file that makes the inclusion of the information in the BD and that receives the contents of $_POST, I created a variable for testing putting as content the "is" and includes in the BD. It appears as "Ã". If I use the "utf8_encode" in the information received, in the database it appears as "Â". If I use "utf8_decode", the "is" appears correctly in the BD. As far as I could tell, the information is coming into the database as something other than "UTF-8", so the "is" appears as "IS". If I change the "Ã" by "is" directly in the BD, in PHP the value query appears normal as "is".

2 answers

0

Hello, all right.

Check defaul_charset in php.ini.

It is also important to check the source of this $_POST data. if it is a form, try to intercept the variable to see if it already arrives incorrect on the server. If it is a file, check the file encoding.

And we always have the possibility to use encoding functions like utf8_encode() or utf8_decode(), in your case, I believe it is the second.

Abs

  • Thanks for the answer. I checked in PHP.ini and the "default_charset" parameter does not exist. I took a look at the PHP.net documentation and the default value is "UTF-8" from PHP 5.6.0 (in my case it is 7.3.14). I believe that in the case of PHP.ini there would be no problem (I can be wrong rs). I took a look at phpinfo() and the default_charset value is "UTF-8" in master and local value. I use AJAX via jQuery. In the PHP file that receives the information, I started the mb_detect_encoding function receiving the POST information and appears as UTF-8.

  • All files that are part of the project are encoded in UTF-8. I would like to avoid the use of "utf8_encode" and "utf8_decode" at all times whenever I insert or query information in the database.

  • Hello, Allan, I understand you perfectly... but one thing is what we would like to do and another thing is what we need to do rsrsrs. If using utf8_decode solves the problem, it is better to use it, put it as solved and move on... maybe it is better to do this than to waste a lot of time trying to solve a bank encoding bug... this is my opinion. Good luck on your project!

0


I identified the reason for the problem.

Even when 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, among them:

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

When performing these functions in texts with accentuation, their return ended up being lost. After removing these functions from my entire project, the strange character problem in the database stopped occurring (including and consulting by PHP and through Mysql Workbench).

I hope this can help other people who end up going through the same trouble I do. :)

OBS: It took me a long time to answer because I could only validate this information after putting these changes in the production environment.

Browser other questions tagged php mysql character-encoding utf-8

You are not signed in. Login or sign up in order to post.