Storing International Characters through PHP

[TOgakangaroo]

I posted this in the MySQL forum but upon thinking about it, this should probably go here

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.

[anatak]

I hope you are using utf charset
you have to change the headers to utf
you have to change in the html the content to utf
you also have to modify mysql
character set and collation should be utf
and you have to run
mysql_query(SET NAMES utf8);
here you will find a ton of information.
http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html

if you plan to use ADOdb connections you have to do some things different

run a query
show variables like %c
and then you should have most of it in utf

read this page (about adodb connections wich is what i use but it will still help you to understand)
http://www.adviesenzo.nl/examples/php_mysql_charset_fix/


anatak

[TOgakangaroo]

Thanks for the replies and the links
I am starting to wonder if this might be a php problem first and foremost (after all it DOES insert fine when I go through the $_POST)
My thought is that there is some sort of header placed on it when the data is sent through HTTP Post and PHP applies different rules to it when it is placed in $_POST.  The question then becomes what is it exactly that is done to the data and how do I make PHP do that to my data as well.

I have not read your links yet and am just posting some ideas.  That second link actually looks quite promising.

I am pretty sure that everything the database is set to work on UTF-8 (the sysadmin spent all last week making these changes) however if PHP is not then I don't understand why it would work ok in the $_POST variable.

Anyways, thanks a lot for the help.  I'll post a big thanks back on here if it works. (And an angry letter to THE INTERNET if it doesn't)

[anatak]

read this and then write the angry letter

http://www.joelonsoftware.com/articles/Unicode.html

this is the single most important page on the internet about character :)