I have a problem returning DB results when input string that contains accents. Examples of unique names in my DB are akuéson, Church´s and Borås.
For the purpose of my problem, I'm not including "sql injection" prevention.
I'm currently not able to extend my DAL class to PDO or mysqli
DB encoding: UTF-8
Table encoding: UTF-8
HTML page encoding: UTF-8
I can add records (from form input) where names uses accents (e.g. "Fióre`s Häßelberg")
I can read records with accents when using -> "name LIKE '$labelName%'"
I can NOT read records with accents when using -> "name = '$labelName'" (0 rows returned)
Here's my code (simplified):
class DAL {
protected $username;
protected $pwd;
protected $host;
protected $dbname;
private $conn;
private $db;
private $query_result; // the stored result of the last query you ran
public function __construct($cfg_file = 'nf.config') {
$config = parse_ini_file($cfg_file);
$this->username = $config['db_user'];
//...
}
public function connect() {
//...
}
private function query($sql) {
$this->query_result = mysql_query($sql, $this->conn)
}
public function getLabelData($labelName)
{
$sql = "SELECT * FROM sl_label WHERE name = '$labelName'";
$this->query($sql);
return $this->query_result;
}
public function getSearchResult($labelName)
{
$sql = "SELECT name FROM sl_label WHERE name LIKE '$labelName%'";
$this->query($sql);
return $this->query_result;
}
}Outputting the $sql just before execution of $this->query($sql) gives this result:
SELECT * FROM sl_label WHERE name = 'Church´s'
Analyzing the mysql query log afer execution, the query looks like this:
1 Connect root@localhost on
1 Init DB norwegianfashion
1 Query SELECT id FROM sl_label WHERE name = 'Church´s'
1 Quit
If I analyze the encoding of the input string using mb_detect_encoding (and the input string is "Church´s"), I get the following results:
mb_detect_encoding($labelName) = UTF-8
$labelName = Church´s
If I encode the input string using utf8_encode($labelName), and then display encoding and name, I get this result:
mb_detect_encoding($labelName) => UTF-8
$labelName = Church´s
Here's something interesting with SET NAMES
If I add the following just after connection "mysql_query("SET NAMES 'utf8'");", all names with accents turnes in to gibberish.
Fióres Häßelberg -> Fióres Häßelberg
If I run the following directly in my DB interface tool (Toad for MySQL), I get normal results:
SET NAMES 'utf8';
SELECT name FROM sl_label;
In 2006, Derek Sivers write an extensive article regarind this issue: http://www.oreillynet.com/onlamp/blog/2006/01/turning_mysql_data_in_latin1_t.html But I'm not being very successfull in fixing the issues the way he does.
I really banging my head against the wall here. Any help appreciated!!