Problem with coding in phpmyadmin on remote server

Question

The problem is this.

I am developing a site locally in PHP with access to database and I am facing coding problems, but this problem only happens in hosting, works normally on the local server.

Accentuated words get " "(?) but only for the data coming from the database.

In the database all accents are displayed correctly.

I am as my files encoded in UTF-8.

My pages contain <meta charset="utf-8">.

and apparently all the coding information in my database is also in UTF-8 (utf-8_general_ci), the same way it is configured on the local server.

when using the commands

show variables like '%char%';

show variables like '%coll%';

I realized the only difference is in "collation_server"

the site is utf8mb4_general_ci

and the remote is like latin1_swedish_ci

please what is happening on the remote server I have no idea how to solve

Answer 1

Good morning guys, after a good study I managed to solve the problem and for those who have this same problem I solved as follows:

There are standard settings for character sets and collations in four levels:

server, database, table and connection.

Each of them has a different solution, keep in mind that in the 4 levels it is necessary to contain the same coding as in the case I am working with utf-8 I will use utf-8 for all levels. let’s do some basic checks:

first check the encoding your file has been saved. this is found in some code adders usually at the bottom of the application. as in the case of Vscode and the windows NOTEPAD itself:

After this check, we go to the file code, it is very important that inside the file contains the identification of the type of encoding.

there are two ways to do this:

in the HTML inside the head you can identify as follows

<meta charset="utf-8">`

as shown in the image:

and inside PHP at the beginning of the code use:

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

In this way we establish connection by informing the ultialized encoding.

In case that hasn’t been solved, let’s go deeper.

Now it will be necessary to check the database and its tables they must be in UTF-8 or utf8mb4.

when opening phpmyadmin and accessing the database it is possible to see in the information of the tables the last encoding in each one and check the encoding of the database itself.

The encoding of the database itself is displayed in the last line of the image.

If the base encoding is different from the above levels this is probably the coding problem of your site.

to resolve just select the database and click operations in the top menu

Find the Grouping option (Collation)

and select the correct encoding from the dropdown menu so the database will be correct, now just check the tables and perform the same procedure in all tables of the database in question.

select the desired table go to operations and find the Grouping option (Collation).

Solved ?? if not, as it was in my case, now it is more complicated, at least it was for me.

Let’s settle it once and for all.

use in phpmyadmin the following command:

show variables like '%char%';
show variables like '%coll%';

The result will be shown in the following image, as shown in the image the circled part the problem was in

collation_server e character_set_server

where its incorrect values were:

character_set_server: latin1 collation_server: latin1_swedish_ci

After correcting for:

character_set_server: utf8mb4_general_ci collation_server: utf8mb4_general_ci

as shown in the image itself the problem has been solved.

to resolve this configuration access the mysql configuration file can be both my.cnf and my.ini browse the section [mysqld] and modify the lines in my case was

character-set-server=latin1
collation-server=latin1_swedish_ci 

and modified it to

character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

Attention ! if this problem is happening on the external server (hosting) you probably will not have access to this configuration file then contact the support of the hosting and request the modification.

I hope I’ve helped, any doubt I’m willing to help.