Storing International Characters through PHP

Togakangaroo

Posted this in the databases forum but in retrospect the MySQL might just be a symptom of some setting in PHP being wrong:

I am working on a large project which involves among other things storing names with international characters in MySQL tables. This data is typically delivered via spreadsheets so I have written a utility to do upload a file and do just that. I am running into the problem currently that certain characters (i.e. the spanish e with an accent) will not import correctly causing the rest of the name to be cut off.

So for example, running
$term = 'México';
mysql($database_info, "INSERT INTO tblSomeTable VALUES $term");
will store only 'M'.

If I hardwire the term into the query
mysql($database_info, "INSERT INTO tblSomeTable VALUES 'México'");
It doesn't cut off but does insert gibberish

One intersting thing that I've found is that everything works out fine when the variable is passed through the HTTP Post Method
mysql($database_info, "INSERT INTO tblSomeTable VALUES $POST['name']");
where $POST["name"]='México' from a form imput
this inserts correctly.

My intuition is that I need to set PHP to use the same encoding that HTTP POST uses but I don't know if that's true nor exactly how to do that. Has anybody run into this problem before/can help me out?
Thanks a lot for your help.

MarkR

You need to be utterly consistent about:

The encoding of your tables in MySQL itself. This is set when you create your tables, or the database default can be used (See MySQL docs for more info)
The encoding of your pages in your web app - This is usually set with a HTTP header "Content-type", or you might ini_set("default_charset",...
The MySQL client encoding, as set with "SET names". Needs to be set on each connection.
The encoding that you saved your PHP files (plus templates, etc) in your editor

If any of them vary, problems might ensue.

Some tools don't do encodings right, so it's very important to see what is ACTUALLY stored in the database, not just what appears to be there. Use a tool which is correctly made (I have significant doubts about phpmyadmin's correctness).

Mark

Togakangaroo

actually I have been using SQLyog. What are your opinions on that? (Running the insert query in it cuts off the word whereas running it form the command line works correctly)

leatherback

Hi,

this a combined problem. You need to ensure that the character encoding of the form and of the database are the same. I personally use utf8-universal for the encoding. If you specify this for the database and for the form, you should be ok.

search on Google for form encoding, look for:

enctype = content-type [CI]
This attribute specifies the content type used to submit the form to the server (when the value of method is "post"). The default value for this attribute is "application/x-www-form-urlencoded". The value "multipart/form-data" should be used in combination with the INPUT element, type="file".
accept-charset = charset list [CI]
This attribute specifies the list of character encodings for input data that is accepted by the server processing this form. The value is a space- and/or comma-delimited list of charset values. The client must interpret this list as an exclusive-or list, i.e., the server is able to accept any single character encoding per entity received.

The default value for this attribute is the reserved string "UNKNOWN". User agents may interpret this value as the character encoding that was used to transmit the document containing this FORM element.

Togakangaroo

Perhaps I did not make myself clear, when you encode the data a form submission is when it DOES work.

The problem comes is when data is input directly from the server-script into mysql. Such as a program that opens and parses a csv file and places it directly into the database (nothing is sent to the client)

Also, everything in the database is definitely set to utf8 and when the same query is run from the command line it works fine.

I cannot say the same for sure about the PHP settings or about whatever settings it might be that are not MySQL.
Like I said, this works fine from the command line:
INSERT INTO tblSomeTable VALUES 'México'");
But will insert incorrectly from php:
mysql($dbdata, "INSERT INTO tblSomeTable VALUES 'México'"));

Interestingly enough it also breaks when I insert it through the SQLyog front-end which leads me to think it might be some setting that affects whatever libraries are similar bewteen SQLyog and PHP.

leatherback

You need to convert the file intself to UTF8 encoding. Most likely this is now in some ISO format. Unfortunately.. I am having a similar problem (Have a search for threads started by me). I have not solved automatic detection of encoding yet. It seems impossible to do...

Togakangaroo

that is interesting, I will certainly take a look at the csv file that I'm importing!
Are you saying that I need to convert the .php file itself to a utf8 encoding? Can you tell me how I can do that? - I've never seen it as an option.

leatherback

Not so much the PHP (Unless you are serving your website in UTF8-encoding), but the CSV file needs to be in the correct character set encoding, as the server does not automatically convert between them.
So a file in some ISO format served as UTF8 might show incorrect characters (As the BYTES refer to one characterset, and the page tells the browser to interpret them as an other characterset. The same happens when inserting them to the database.

Togakangaroo

I see what you're saying and that IS something that I have to deal with but it cannot be the only thing. The following test-line gets the same bad result:
<?php
db_connect(...);
mysql(..., "INSERT INTO tblSomeTable VALUES 'México'");
?>

My point is that although the csv file encoding is something I might have to deal with later, the same thing happens even when I am hard-coding the value in; it still has the same problem.