Encoding issue with MySQLi

Question

I have a MySQL table which I set to use the encoding utf8_swedish_ci. Two example values are 2 Kung and 1 Krön - note the swedish character. I need to search on these internationalized strings from within a PHP script. My PHP file is encoded in UTF-8. My code, with error handling stripped for clarity, looks like this:

$db = new mysqli($host, $user, $password, $user);
$db->set_charset('UTF-8');
$stm = $db->prepare('SELECT `id` FROM `myTable` WHERE `bok` LIKE ?');
echo "Searching on $value'...";
$stm->bind_param('s', $value);
$stm->execute();
$stm->bind_result($result);
$stm->close();
echo "Fetched '$result'.";

Now if I set $value to the first example value, I get the single matching row. With the other example value I get no rows. I have tested the queries to work in PHPMyAdmin. I figure the error lies with the international character and therefore that I messed up the encodings somewhere. So what did I do wrong and how do I fix this?

Edit: the line $db->set_charset('UTF-8'); fails because I used an incorrect charset. It should be utf8. Turns out I had too little error handling, I should've checked $db->error after set_charset.

Where you went wrong: You didn't specify an encoding when you created the table, so it used the default. How to fix it: [Change the encodings to be consistent.](http://stackoverflow.com/questions/6115612/how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8) — Sammitch, Oct 10 '14 at 19:25
@Sammitch why do you think he didn't specify encoding? @David try set names and use backticks in column and table names because if you don't in some cases it can cause problem. Also did you check if the `$value` is correct? — Laci K, Oct 10 '14 at 19:32
@Sammitch I did specify the encoding of the table. @LaciK I actually use backticks everywhere, removed them when I pulled together the example. I used `echo $value` so I'm confident it has the right value. — David, Oct 10 '14 at 20:03
Also note, this is `utf8` and not `UTF-8` for MySQL (`Unknown character set: 'UTF-8'`) — julp, Oct 10 '14 at 21:06
@julp That's it! I used the wrong name for the character encoding. Please add this as an answer so I can give you credit for solving it. — David, Oct 11 '14 at 11:36

score 1 · Accepted Answer · answered Oct 11 '14 at 15:08

1

$db->set_charset('UTF-8');

Note that MySQL is particular, the name it uses internally for the UTF-8 character set is utf8 (without dash), not the regular UTF-8.

In case of doubt, refer to SHOW CHARACTER SET; ;)

answered Oct 11 '14 at 15:08

julp

3,860
1
22
21

score -1 · Answer 2 · answered Oct 10 '14 at 20:52

Try this, it might help.

$db = new mysqli($host, $user, $password, $user);
$db->set_charset('UTF-8');
$stm = $db->prepare('SELECT `id` FROM `myTable` WHERE `bok` LIKE ?');
$value = '%'.$value.'%'; //added this line
$stm->bind_param('s', $value);
$stm->execute();
$stm->bind_result($result);
$stm->close();
echo "Fetched '$result'.";

I tried LIKE '' with UTF-8 chars on a char(180) column with utf8_general_ci encoding and it returned 0 rows so I tried it with LIKE %''% and that worked

But I'm not using utf8_general_ci, I'm using utf8_swedish_ci. I tried your suggestion and it didn't make any difference. — David, Oct 11 '14 at 11:33

Encoding issue with MySQLi

2 Answers2