[RESOLVED] set charset mysql

abecadlo

Hi all,
First off I know that my English is not good. For this reason I wrote longer code as example than text. I'm trying to solve the problem, but something go wrong. I don't know what's wrong with my code?
I read http://dev.mysql.com/doc/refman/5.1/en/charset-applications.html and http://dev.mysql.com/doc/refman/5.1/en/charset-connection.html. I included this <meta> tag within my <head> element. My database will use default character set and collation for data storage. Tables created in the database will use utf8 and utf8_polish_ci by default for any character columns. I typed into my table word 'Gżegżółka'. I conected to database MySQL. My application configure their connection to the server each time they connect. This is done by executing a "SET NAMES utf-8" statement after connecting. After all I created simple query to the database "query("select kolumna from tabela")". The result is following:
1. window GTK shows 'Gżegżółka'
2. browser Mozilla shows this ugly text 'G�eg���ka'.
How to solve the problem?

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="pl" lang="pl">
<head>
<title>strona testowa</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
</head>

<body>

<?php
/*
---------------------------------------------------------------------------------
My database and table
---------------------------------------------------------------------------------
CREATE DATABASE baza_utf DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_polish_ci;
use baza_utf;
CREATE TABLE tabela (kolumna VARCHAR(20));
INSERT INTO tabela (kolumna) values ('G&#380;eg&#380;ó&#322;ka');
---------------------------------------------------------------------------------
*/

$mysqli = new mysqli("127.0.0.1", "user", "password", "baza_utf");

//check connection
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$mysqli->query("SET NAMES utf-8");

if ($result = $mysqli->query("select kolumna from tabela")) {
	while ($row = $result->fetch_row()) {
		echo $row[0];
	}
	$result->close();
}
$mysqli->close();
?>

</body>
</html>

NogDog

If the web server sends a HTTP header specifying the character set, it will override what is in your meta tag. You might try adding this to the PHP page (before any output):

<?php
header('Content-Type: text/html; charset="utf-8"', true);

abecadlo

I checked $row and I discovered that it is ISO-8859-1. What can I do with it 😕

	while ($row = $result->fetch_row()) {
		echo $row[0];
		echo ": ". mb_check_encoding($row[0], 'ISO-8859-1');
		#result: G&#65533;eg&#65533;&#65533;&#65533;ka: 1
	}

abecadlo

I gave you more info, because I revolve around the same issue. I can't see the daylight. 🙁

<?php
header('Content-Type: text/html; charset="utf-8"', true);
/*
---------------------------------------------------------------------------------
My database and table
---------------------------------------------------------------------------------
CREATE DATABASE baza_utf DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_polish_ci;
use baza_utf;
CREATE TABLE tabela (kolumna VARCHAR(20));
INSERT INTO tabela (kolumna) values ('G&#380;eg&#380;ó&#322;ka');
---------------------------------------------------------------------------------
*/
$mysqli = new mysqli("127.0.0.1", "attyla", "amorifer", "baza_utf");

//check connection
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$mysqli->query("SET character_set_connection = utf-8");
$mysqli->query("SET character_set_client = utf-8");
$mysqli->query("SET character_set_results = utf-8");
$mysqli->query("SET NAMES utf-8");
mb_internal_encoding('UTF-8');

if ($result = $mysqli->query("select kolumna from tabela")) {
	while ($row = $result->fetch_row()) {
		mb_check_encoding($row[0], 'utf-8');
		echo $row[0];
	}
	$result->close();
}
$mysqli->close();
?>

my.ini file:
[mysqld]
default-character-set=latin1

mysql> status;
--------------
mysql.exe  Ver 14.14 Distrib 5.1.48, for Win64 (unknown)

Connection id:          72
Current database:       baza_utf
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.1.48-community MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    utf8
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 39 min 22 sec

Threads: 1  Questions: 304  Slow queries: 0  Opens: 17 
Flush tables: 1  Open tables: 1  Queries per second avg: 0.128

mysql> SHOW VARIABLES LIKE 'collation\_%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | utf8_polish_ci    |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

mysql> SHOW VARIABLES LIKE 'character\_set\_%';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_client     | latin1 |
| character_set_connection | latin1 |
| character_set_database   | utf8   |
| character_set_filesystem | binary |
| character_set_results    | latin1 |
| character_set_server     | latin1 |
| character_set_system     | utf8   |
+--------------------------+--------+

bradgrafelman

For the MySQL commands, it's not "utf-8", it's "utf8". Using "utf-8" will throw MySQL errors (though you'd never know, since you don't bother to check) such as:

ERROR 1115 (42000): Unknown character set: 'utf'

ERROR 1054 (42S22): Unknown column 'utf' in 'field list'

abecadlo

For the MySQL commands, it's not "utf-8", it's "utf8".

So, utf8 causes error string 'G¾eg¾¢ˆka' instead of 'Gżegżółka'. Close but still far from home.

abecadlo

Not so far 🙂 Now I discover that my solution works correctly. If I use foreign server, then encoding database is in working order.

It means that my home server has fault. What causes it?