php, mysql: compare strings - accent sensitive and case insensitive
i need to perform a name search in the database based on a set of keywords. the result should be accent sensitive and case insensitive.
following the solution i found here plus few modifications to allow case insensitivity, i used the following code:
$sql = "SELECT name FROM table_names WHERE LOWER(name) LIKE _utf8 '%".strtolower($keyword)."%' COLLATE utf8_bin";
this does not work for me when 开发者_StackOverflow中文版the search keyword contains accents. but the strange thing is that this query works well in phpMyAdmin (but i read somewhere that this shouldn't be reliable). what could be the problem here?
UPDATE: i revised my code to:
$sql = "SELECT name FROM table_names WHERE LOWER(name) LIKE LOWER(_utf8 '%$keyword%') COLLATE utf8_bin";
but i still have no results.
thanks!
You can try to echo the sql being executed and check if the accents are there.
My guess they are not...
[EDIT]
Can you try this:
php.
<html>
<head>
</head>
<body>
<form action="" method="get">
<input name="name" type="text"/>
<input type="submit" value="go"/>
</form>
<?php
if (!empty($_GET['name'])) {
$mysqli = new mysqli('localhost', 'root', '', 'test');
$mysqli->set_charset('latin1');
$_GET['name'] = utf8_encode($_GET['name']);
if ($result = $mysqli->query('select * from dummy where LOWER(name) LIKE _utf8 \'%' . $_GET['name'] . '%\' COLLATE utf8_bin')) {
while ($obj = $result->fetch_object()) {
var_dump($obj->name);
}
}
$mysqli->close();
}
?>
</body>
</html>
mysql: (doesn't matter)
CREATE TABLE `dummy` (
`id` int(11) NOT NULL,
`name` varchar(45) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin5$$
This one works for me...
加载中,请稍侯......
精彩评论