Inserting special characters

I'm losing it!

[Sub_Seven]

Hello all,

Could someone <<please>> tell me what is the best way to insert spanish characters like áéíóúñ and such into MySQL?

I just do not understand this, I have a DB at work where I have done it before and now I am working in this new project and things just don't work, I have tried several different types of collations and they seem to be indifferent to the problem, I have tried both htmlentities and htmlspecialchars and the result is just the same, I get things like this inserted when I do tests: áfffé

This is part of my code right now

$line=htmlspecialchars($_POST['cp_cat_line']);
$name=htmlspecialchars($_POST['cp_cat_name']);
$sql="INSERT INTO cp_category (cp_cat_line, cp_cat_name)
VALUES ('$line', '$name')";

I am in actual physical pain from the frustration, this can't be so difficult, any help as always will be extremely appreciated, thanks.

[timster]

Relax and let the pain go, you are not the first or will you be the last to have character encoding trouble.

<div type="long_winded_background">
So why does an "a" come through fine but and "á" get scrambled? As they say, a computer doesn't really know letters, just numbers. Long ago, computer makers agreed that when it came to representing letters, 97 would mean "a". (This is the ASCII standard.) But back then you couldn't type á into your computer, so it didn't get a number. And to this day, there is no universal agreement on what number should represent it. That is, there are many character encodings where "a" is 97 but for "á" the number is not consistent.</div>

So what do you need to do? The easiest thing to do is to find out what character encoding your MySQL data is in, and then make sure the PHP web application uses it to communicate with MySQL. Third, make sure your app is telling the browser to use the same encoding as well. (The other way is to translate character sets on the way. That doesn't sound like it is necessary here.)

Check the "character set" variables in MySQL to ensure they are all the same, by running the following SQL statement:

 show variables

The PHP function "mysql_client_encoding" returns what character set PHP is using to communicate with the MySQL database.

You inform the browser to use a character set either in the HTTP header or in a meta tag.

You can confirm what character set Firefox is using by control clicking the page and selecting "View Page Info."

I hope that's enough to get you going. For further reading, Wiki has a good description of character encodings.

[en.wikipedia.org ]

[timster]

Two more quick things:

(1) You can check phpInfo() for a default PHP encoding (default_charset) but it may just tell you "none set".

(2) In MySQL, it's possible to override the database's default character set for a schema (ie an individual "database") or a table. You can check the character set for the given table with the SQL statement: SHOW TABLE STATUS

[Sub_Seven]

Hello timster,

Thank you for taking the time to provide such a helpful answer, following your suggestions' order, my reply:

The easiest thing to do is to find out what character encoding your MySQL data is in

I am using utf8_unicode_ci for the DB and tables (I understand this is compatible with most characters but please correct me if I'm wrong.)

...and then make sure the PHP web application uses it to communicate with MySQL

I'm not sure exactly what is the exact procedure to achieve this, but two things come to mind:

1. I encode all my documents in UTF-8 without BOM.
2. I tried this: mysql_query("SET NAMES utf-8"); right after my DB connection, but it actually messed up the Spanish characters that were displaying fine.

Now, you may wonder why some characters are displaying correct while others don't, the reason for that is that those inserted directly from phpmyadmin display correct and those inserted from my php script page don't, (hence my problem and my theory that the root of the issue is in my script.)

The PHP function "mysql_client_encoding" returns what character set PHP is using to communicate with the MySQL database.

This returns "latin1"

You inform the browser to use a character set either in the HTTP header or in a meta tag.

I use meta tag
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

You can confirm what character set Firefox is using by control clicking the page and selecting "View Page Info."

This returns UTF-8.

(1) You can check phpInfo() for a default PHP encoding (default_charset) but it may just tell you "none set".

Yes, no value.

(2) In MySQL, it's possible to override the database's default character set for a schema (ie an individual "database") or a table. You can check the character set for the given table with the SQL statement: SHOW TABLE STATUS

Collation is utf8_unicode_ci

After all of this the only thing that seems to not match is what mysql_client_encoding returned (latin1), isn't that what mysql_query("SET NAMES utf-8"); is for? if yes, why would it mess the characters that were inserted directly from phpmyadmin and still why would it not accept new spanish characters? I tried inserting them while I had mysql_query("SET NAMES utf-8"); after my connection and they would display different weird characters.

Thanks so much for the help, I hope you can see what my problem is.

[timster]

The PHP function "mysql_client_encoding" ...
This returns "latin1"

Bingo, that's the problem (or at least part of it). Good work.

isn't that what mysql_query("SET NAMES utf-8"); is for?

Not quite. This statement is PHP telling MySQL what character set to expect.

Here's how to tell PHP what encoding to send to MySQL:

mysql_set_charset( 'utf8' );

Try putting that above any of your mysql_query statements. If you still get problems, try setting PHP's internal character set as well, like this:

declare(encoding='utf8');

After that I'm thinking everything should be pretty well nailed down.

[Sub_Seven]

mysql_set_charset( 'utf8' ); did it! :)

timster YOU ARE THE MAN! (that of course assuming your are a male (if not just replace to opposite gender above)).

I cannot thank you enough for the help, I honestly thought I was gonna be stuck with this problem for days and we nailed it in no time, I owe you one :)

Thanks :)

[timster]

Happy to help. (Yes, I'm a guy.)