0

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

Gynteniuxas
  • 7,035
  • 18
  • 38
  • 54
user3070123
  • 159
  • 2
  • 20
  • I 'am using php my admin of ovh – user3070123 May 27 '16 at 15:48
  • Are you saying, that when you change json in database, then select query do not work? Impossible. Maybe in php you are doing json_decode() and that is what is not working? – nospor May 27 '16 at 15:50
  • I 'am doing it with a web service ; i write the query and then I 'am doing with json_encode and it dosn't work – user3070123 May 27 '16 at 15:53
  • so it is not problem with query but with json_encode... try to focus on problem next time. **json_encode require correct utf8 encoding** – nospor May 27 '16 at 15:56
  • ive tried this `json_encode($response,JSON_UNESCAPED_UNICODE)` but alwyes same probleme , HOW CAN I FIX IT ? – user3070123 May 27 '16 at 16:06
  • I change the response when fetchin the result but same error : header("Content-type:application/json; charset=UTF-8"); $response["rows"] = $result->num_rows; $response["categorie"] = $rows; $response["message"] = "Liste catégorie"; } – user3070123 May 27 '16 at 16:13
  • I var_dump teh result , it is ok but with json encode it does not work – user3070123 May 31 '16 at 11:00
  • Let's see if the data is already Unicode in the table. Please do `SELECT message, HEX(message) FROM categorie WHERE ...`. – Rick James Jun 04 '16 at 06:38
  • It ok I 've find the solution , I just add that ligne in my config file : $conn->query("SET CHARACTER SET utf8"); – user3070123 Jun 06 '16 at 09:05

2 Answers2

0

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

 $conn->query("SET CHARACTER SET utf8");
user3070123
  • 159
  • 2
  • 20
  • You don't say what your database library is but encoding is normally a connection property; running a query manually is not the best choice because it doesn't let the client know about the proper encoding. Also, shouldn't it it `set names` rather than `set character set`? – Álvaro González Jun 06 '16 at 09:16
  • Related [Is “SET CHARACTER SET utf8” necessary?](http://stackoverflow.com/questions/1566602/is-set-character-set-utf8-necessary) and [UTF-8 all the way through](http://stackoverflow.com/questions/279170/utf-8-all-the-way-through). – Álvaro González Jun 06 '16 at 09:19
0

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!

Benjamin Vaughan
  • 77
  • 1
  • 7