0

I have a simple mysql query:

$query = addslashes($_REQUEST['query']);
$sql = mysql_query ("SELECT * FROM countries WHERE name LIKE '%{$query}%'");

It works with any $query unless it has a special character in it.

Let's say i'm looking for "Åland Islands" or "Côte d'Ivoire", it will not retrieve anything.

I also tried without the addslashes but it just doesn't change anything.

Thank you for your help.

EDIT

This is my variables setup in Mysql:

character_set_client     | utf8    
character_set_connection | utf8                       
character_set_database   | utf8                    
character_set_filesystem | binary                    
character_set_results    | utf8                      
character_set_server     | latin1                    
character_set_system     | utf8

The character set server is something I can't change. It seems to be set by my hosting. Any attempt to change this gives me a message telling me that I don't have the super permissions that will allow me to do that.

Baylock
  • 1,246
  • 4
  • 25
  • 49
  • 1
    try this :-http://stackoverflow.com/questions/18455406/how-to-extract-data-from-mysql-that-contains-special-characters or http://stackoverflow.com/questions/10578694/mysql-selecting-string-with-multi-special-characters to replace those characters while searching – Divya Mar 19 '14 at 09:04
  • 2
    please check that the input string and database encoding are the same (obviously I suggest utf-8) – Lorenzo Marcon Mar 19 '14 at 09:04
  • @Lorenzo Marcon: I edited my question. This may give you some extra info. – Baylock Mar 20 '14 at 10:57
  • @A5I-IE5: i tied but it didn't help – Baylock Mar 20 '14 at 10:58
  • you have to proceed for steps, to identify where the problem could be. Try to hardcode a query in your script, such as `$sql = mysql_query ("SELECT * FROM countries WHERE name LIKE '%Åland Islands%'")`. Does it provide results? – Lorenzo Marcon Mar 20 '14 at 11:31

2 Answers2

1

Use following after mysql_query:

header('Content-type: text/html; charset=utf-8');
dipendra
  • 261
  • 1
  • 3
  • 9
0

I got the answer but the issue was not the one I thought it was...

It's not about mysql per se but about a json_encode that I use to output the MySQL result.

So, I had to utF8 decode the input data...

$query = utf8_decode($_REQUEST['query']);

Then I make my Mysql query and finally, output my result like this:

$response = array_map("utf8_encode", $response); 

echo json_encode ($response);
Baylock
  • 1,246
  • 4
  • 25
  • 49