Encoding hell. Mysql, php and utf-8

Question

Performing a query comparing phrases is a pain in the ass. Look the following configuration to understand my problem.

sql.sql

create user "myStore"@"localhost" identified by "1234"; grant all privileges on myStore.* TO "myStore"@"localhost";

create database myStore character set utf8 collate utf8_general_ci;

use myStore;

create table item (
    item_id integer not null,
    name varchar(60) not null,

    primary key (item_id)
);

alter table item engine=innodb default charset=utf8 collate=utf8_bin;

insert into item (item_id, name) values (0, "apple"), (1, "maça");

my.cnf

[mysqld]
collation-server = utf8_unicode_ci
character-set-server = utf8

mysql> SHOW VARIABLES LIKE 'collation%';

+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+

mysql> SHOW VARIABLES LIKE 'character_set%';

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

index.php

<?php
    try {
        header ('Content-Type: text/html; charset=utf-8'); 

        $mysqli = new mysqli ("localhost", "myStore", "1234", "myStore");

        if ($mysqli->connect_error) throw new exception ("Error (" . $mysqli->connect_errno . "). " . $mysqli->connect_error);

        $sql = $mysqli->prepare ("select item_id, name from item where name = '" . utf8_decode ("maça") ."'");

        if (! $sql) throw new exception ($mysqli->error);

        $sql->execute ();

        $sql->bind_result ($item_id, $name); 

        while ($sql->fetch ()) echo $item_id . " - " . utf8_encode ($name);

        $sql->close ();
    }
    catch (exception $e) {
        echo $e;
    }
?>

As you can see, I MUST decode the string before sending it to MySQL and then encode it again.

I really don't know what is wrong, is there a way to avoid those encoding and decoding functions ? Thanks.

Did you also save the PHP source in utf8?

Jon
– Jon

2012-09-08 01:38:42 +00:00
Commented Sep 8, 2012 at 1:38 — Jon
– Jon, Commented Sep 8, 2012 at 1:38
I'm not sure what the down vote was for, so I voted you up.

Mike
– Mike

2012-09-08 01:43:13 +00:00
Commented Sep 8, 2012 at 1:43 — Mike
– Mike, Commented Sep 8, 2012 at 1:43
Sure, every single file is utf-8 :)

Caio
– Caio

2012-09-08 17:03:55 +00:00
Commented Sep 8, 2012 at 17:03 — Caio
– Caio, Commented Sep 8, 2012 at 17:03

eggyal · Accepted Answer · 2012-09-08 01:41:01Z

3

mysql> SHOW VARIABLES LIKE 'character_set%';

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |

These variables are session-specific. Please ensure that they are set appropriately for your client during your sessions by calling SET NAMES 'charset_name' during an interactive mysql session, or mysqli::set_charset ( string $charset ) from PHP.

answered Sep 8, 2012 at 1:41

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Caio Over a year ago

Thanks for your help but now I'm confused. I was looking for a server-side solution and the official website tells that this is the preferable way :s

eggyal Over a year ago

@Caio: I'm not sure I understand what you mean by "a server-side solution". Clients may use a different string encoding to that used by MySQL, so translations must be performed: this can occur either in MySQL (by informing it of the client encoding, which is the normal approach) or else within the client (which is what you're doing above).

Collectives™ on Stack Overflow

Encoding hell. Mysql, php and utf-8

As you can see, I MUST decode the string before sending it to MySQL and then encode it again.

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

As you can see, I MUST decode the string before sending it to MySQL and then encode it again.

1 Answer 1

2 Comments

Related