Characters like Ã not displaying correctly from MySQL

Question

I have a problem when displaying results from a database in an HTML file in two computers.

So, in one computer it shows a name like this:

SAUL FRANCISCO GARCÃA RODRÃGUEZ

The collation of the database is latin1_swedish_ci and the collation of the tables is utf8_general_ci.

In the other computer it shows like this (I want it to be this way):

SAUL FRANCISCO GARCÍA RODRÍGUEZ

The names get listed in a <select> tag The language of the two browsers is English and the Html file is the same on both environments so I think It has to do with the charset or encoding for the php.ini file

I have tried changing the values in php.ini but nothing changes and I can't find the answer anywhere.

The only difference is that where the results get displayed as I want I installed WAMP and in the other computer I installed Apache, MySQL and PHP separately.

Sorry if the details are not very informative but I have no clue where I'm going wrong.

P.S. On both databases the data looks like this from the MySQL console:

SAUL FRANCISCO GARCÃA RODRÃGUEZ

Your problem is when are showing data in a web page? – Ignacio Ocampo Dec 17 '13 at 01:45 — Ignacio Ocampo, Dec 17 '13 at 01:45
What is the collation of the actual column? – Mike Dec 17 '13 at 01:55 — Mike, Dec 17 '13 at 01:55

Giacomo1968 · Accepted Answer · 2013-12-17T01:54:04.103

Editing php.ini won’t help you much in a case like this.

What is the data collation of the database giving you an issue? By default, most MySQL installs set latin1_swedish_ci instead of utf8_general_ci for newly created databases.

Change the collation of the database & try again.

ALTER DATABASE [name of your database] CHARACTER SET utf8;

If this is a specific table, the collation can be changed as so:

ALTER TABLE [name of your table] CONVERT TO CHARACTER SET utf8;

And if it is a specific column in a table:

ALTER TABLE [name of your table] MODIFY [name of your column] [other settings] CHARACTER SET utf8 COLLATE utf8_general_ci;

Or perhaps you could export the current database, create a new database with this command & reimport the data:

CREATE DATABASE [name of your database] CHARACTER SET utf8 COLLATE utf8_general_ci;

And if you want to make a permanent change to the MySQL install on the machine giving you an issue, go and edit my.cnf. The following would set the whole chain to UTF-8:

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

JakeGould - Altering the database worked, editing the my.ini file worked for the subsequent databases. Didn't had to change the charset of the tables. Thanks a lot! — alejandroid, Dec 19 '13 at 06:04
Happy it solved the issue! DB collation and character set issues can drive you nuts. — Giacomo1968, Dec 20 '13 at 00:55
Thanks for your tipp. Is it possible to apply the my.cnf settings to a specific database? — wiesson, Dec 02 '14 at 11:30
@wiesson Thanks! Sorry, but no. The settings in my.cnf are systemwide per MySQL install. — Giacomo1968, Dec 02 '14 at 17:03

score 2 · Answer 2 · answered Dec 17 '13 at 01:48

2

add <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> to the head of your html

answered Dec 17 '13 at 01:48

Friedrich

2,211
20
42

ArendE · Answer 3 · 2013-12-17T01:57:10.237

2

You could set PHP to output the content as UTF-8:

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

In your HTML document you can specify to the browsers that the content it is UTF-8, by putting this in the HEAD of your pages:

<meta charset="UTF-8">

edited Dec 17 '13 at 01:57

answered Dec 17 '13 at 01:48

ArendE

957
1
8
14

Characters like Ã not displaying correctly from MySQL

3 Answers3

Linked