my sql dont return value having special character

Question

my sql query returns no result when just the value to return have special character. exemple :

the query is :

$sql = "SELECT * from categorie ";

    $result = $conn->query(sprintf($sql));

the result was in json

   {"rows":2,"categorie":[{"idCategorie":"8","nomCategorie":"loisir ","imageCategorie":"imgCategorie1.png"},{"idCategorie":"9","nomCategorie":"destination","imageCategorie":"imgCategorie2.png"}],"message":"Liste cat\u00e9gorie","error":0}

But when I change the "nomCategorie" from "loisir" to "quelle loisir avez vous ??

The query dont return any thing or make chane like nomCategorie =cinéma paris

it doesn't work. So I've cancel all the space and the special character and it works

Answer 1

It ok I 've find the solution , I just add that ligne in my config file :

 $conn->query("SET CHARACTER SET utf8");

Answer 2

I had a very similar problem. Any time I was trying to get data with special characters it wasn't being returned. Then I found this very comprehensive guide:

https://www.toptal.com/php/a-utf-8-primer-for-php-and-mysql

It wasn't really clear in the question what mysql connector you were using but this guide should have what you are looking for. Here is a basic mysql connection:

 $link = mysql_connect('localhost', 'user', 'password');
  mysql_set_charset('utf8', $link);

Useful also to note was setting your special characters for display in php within the header:

header('Content-Type: text/html; charset=utf-8');

Hope that helps!