Help Handling special characters

[special4mysql]

I have a mySQL database which will store some special characters (á é í ó ú ñ). My problem is that the query I am using to populate the tables is not getting the charactes stored correctly in the tables:

DROP TABLE IF EXISTS `categories`;
CREATE TABLE `categories` (
`Category_ID` int(11) NOT NULL auto_increment,
`List_Order` double(11,2) default '0.00',
`Category_Name` varchar(75) NOT NULL,
`Category_Name_En` varchar(75) default NULL,
`Category_Available` tinyint(1) default '0',
PRIMARY KEY (`Category_ID`),
KEY `Category_Available` (`Category_Available`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

#
# Dumping data for table categories
#

INSERT INTO `categories` VALUES (2,0,'Ciencias','Science',1);
INSERT INTO `categories` VALUES (3,0,'Economía','Economy',1);
INSERT INTO `categories` VALUES (9,0,'Artes','Arts',1);
INSERT INTO `categories` VALUES (19,0,'Historia y Arqueología','History and Archaeology',1);
INSERT INTO `categories` VALUES (34,0,'Deportes','Sports',1);
INSERT INTO `categories` VALUES (35,0,'Población y Sociedad','Population and Society',1);
INSERT INTO `categories` VALUES (36,0,'Ambiente','Environment',1);
INSERT INTO `categories` VALUES (37,0,'Lengua y Literatura','Language and Literature',1);
INSERT INTO `categories` VALUES (38,0,'Municipios','Municipalities',1);
INSERT INTO `categories` VALUES (39,0,'Educación','Education',1);
INSERT INTO `categories` VALUES (40,0,'Diáspora puertorriqueña','Puerto Rican Diaspora',1);
INSERT INTO `categories` VALUES (41,0,'Religión','Religion',1);
INSERT INTO `categories` VALUES (42,0,'Gobierno','Government',1);
INSERT INTO `categories` VALUES (43,0,'Medios de comunicación','Media',1);
INSERT INTO `categories` VALUES (44,0,'Folclore','Folklore',1);
INSERT INTO `categories` VALUES (45,10,'Proyectos FPH','PHF Projects',1);

If I enter the values individually using phpMyAdmin SQL interface, the values are stored correctly, but the output on the web page is wrong. At least I would like to know how to store these special characters correctly. Is the syntax wrong?

 

[elsenorjose]

Perhaps it is the language you are using to generate the web page? If you query the database with MySQL Query Browser, are the special characters displayed correctly?

 

[DonQuichote]

First: you don't tell us what encoding you use.
Second (more important) you don't tell MySQL what encoding you use.

If you are using utf-8 for the strings, tell MySQL about that by sending a "SET NAMES utf8" command just after opening the connection.

This depends on the version used. Too low versions of the server do not understand the SET NAMES command.

 

[special4mysql]

If I query the database the characters are wrong, since they are wrong in the tables.

Isn't this statement "ENGINE=MyISAM DEFAULT CHARSET=latin1;"
the encoding for mySQL? If not then how do I specify the encoding?

 

[DonQuichote]

OK. You write these scripts in an editor, right (notepad or whatever). You press keys, you see characters on screen and if the file is saved, bytes are written. The encoding says what bytes are written for what characters on screen. So an [é] may be a byte 130 on one encoding, and another value or even two bytes in another encoding.

The fact that you see an [é] on screen says nothing about what the string really is that you send. MySQL cannot watch your monitor. So if your editor is set to using utf-8, you will see an [é] on screen. But if you would open the same file expecting latin-1 characters, you would see an [Ã] with another character.

The trouble with encodings is that every piece of text has an encoding, but strings have not. A string is just a series of bytes. It is the rendering on your monitor that makes it legible in characters. The real problem, off course, is that texts are stored as strings, thereby losing the encoding information. So you have to specify the encoding used in all your applications: your database connection, your e-mail server, and the browser. But the basis is: you have to specify what encoding you use.

As I already said, you can use the SET NAMES command to set the encoding. Please read the manual about encodings.