PHP - SQL query containing unicode is returning NULL for some reason

Question

I am trying to run this query:

SELECT trans FROM `dictionary` WHERE `word` LIKE 'Çiçek'

like this (relevant code):

function gettranslation($word){
    return $this->query("SELECT trans FROM `dictionary` WHERE `word` LIKE '$word'");
}
function query($query){
    $result=mysqli_query($this->conn, "set character_set_results='utf8'");
    $result=mysqli_query($this->conn, $query);
    return $row = mysqli_fetch_row($result)[0];
}

My mySQL table is made like this:

Word  | Trans
-------------
Flower| Çiçek
-------------
Çiçek | Flower

When the $word I pass to the gettranslation function is Flower, the result is Çiçek. However when I do the reverse, the result is NULL.

Also if I do var_dump on it, I get nothing. Not even an empty array.

Strangely enough, when I run the same query in PHPmyAdmin it runs fine.

Help?

Answer 1

As far as I remember, mysqli_query($con, "SET NAMES 'utf8'"); was required, like this:

function gettranslation($word){
    return $this->query("SELECT trans FROM `dictionary` WHERE `word` LIKE '$word'");
}
function query($query){
    //$result=mysqli_query($this->conn, "set character_set_results='utf8'");
    mysqli_query($con, "SET NAMES 'utf8'");
    $result=mysqli_query($this->conn, $query);
    return $row = mysqli_fetch_row($result)[0];
}

Answer 2

$result=mysqli_query($this->conn, "set character_set_results='utf8'");

This only affects the character set used for returned strings, not the character set for incoming queries. So your query is interpreted as if it were ISO-8859-1-encoded: LIKE 'Ãiçek'. This doesn't match any data in the table.

Instead, set the character set for the whole connection:

$this->conn->set_charset('utf-8');

It's better to do this once when you connect, rather than on every query.

(Never use SET NAMES. This sets the connection encoding for MySQL without letting PHP's mysqli know that the encoding has changed, so when it comes to mysqli_real_escape_string some content it will use the wrong encoding. If the encoding in question is an East Asian multibyte encoding that means you get an SQL injection hole.)