CODEWITHSUNDEEP
phpmysqlunicodenull
Meeh
Meeh

Reputation: 21

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

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?

Upvotes: 1

Views: 408

Answers (2)

bobince
bobince

Reputation: 536369

$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.)

Upvotes: 1

Jayesh Chandrapal
Jayesh Chandrapal

Reputation: 684

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];
}

Upvotes: 2

Related Questions