[eluser]Kenneth Allen[/eluser]
[quote author="mddd" date="1282732411"]I have found that in some cases I needed to tell the database explicitly to send the data back to me in UTF8 format. Check out SET CHARACTER and SET NAMES in the Mysql manual.[/quote]
But MYSQL seems to be returning the correct information, as shown in my original email; that is, it is working from the command line without setting any options. It is only when it is rendered via CI to a browser page that it does not look right( and running entirely local to my iMac).
Here are my settings in MYSQL initially:
Code:
mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+--------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.0.85-osx10.5-x86_64/share/mysql/charsets/ |
+--------------------------+--------------------------------------------------------------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM GraveNotes WHERE GraveID=685;
+-----+---------+---------------------+---------------------------------------------------------------------------------+
| id | GraveID | CreateDate | NoteText |
+-----+---------+---------------------+---------------------------------------------------------------------------------+
| 204 | 685 | 2010-08-24 00:00:00 | Obit in 'Calgary Herald' but he is not listed in any of 8 Calgary cemeteries… |
+-----+---------+---------------------+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)
And with use of SET NAMES:
Code:
mysql> SET NAMES 'utf8';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+--------------------------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql-5.0.85-osx10.5-x86_64/share/mysql/charsets/ |
+--------------------------+--------------------------------------------------------------+
8 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'collation%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM GraveNotes WHERE GraveID=685;
+-----+---------+---------------------+-------------------------------------------------------------------------------------+
| id | GraveID | CreateDate | NoteText |
+-----+---------+---------------------+-------------------------------------------------------------------------------------+
| 204 | 685 | 2010-08-24 00:00:00 | Obit in 'Calgary Herald' but he is not listed in any of 8 Calgary cemeteries… |
+-----+---------+---------------------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Now the MYSQL command line has the same error as the web page!
When I added the SET NAMES 'utf8'; command in the script that loads the data into the database, all seems to work well from that point on for the web site, but I then need that command in my MYSQL command line as well. Too bad there was no way to have the command line pick that attribute up from the database itself.
