Sub_Seven - 5:20 pm on Mar 3, 2011 (gmt 0)
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.