php，mysql：比较字符串 - 区分重音和不区分大小写

发布于 2024-09-20 00:31:13 字数 606 浏览 6 评论 0原文

我需要根据一组关键字在数据库中执行名称搜索。结果应该区分重音且不区分大小写。

按照我在这里找到的解决方案加上一些修改允许不区分大小写，我使用了以下代码：

$sql = "SELECT name FROM table_names WHERE LOWER(name) LIKE _utf8 '%".strtolower($keyword)."%' COLLATE utf8_bin";

当搜索关键字包含重音符号时，这对我不起作用。但奇怪的是，这个查询在 phpMyAdmin 中运行良好（但我在某处读到这应该不可靠）。这里可能有什么问题？

更新：我将代码修改为：

$sql = "SELECT name FROM table_names WHERE LOWER(name) LIKE LOWER(_utf8 '%$keyword%') COLLATE utf8_bin";

但我仍然没有结果。

谢谢！

原文

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 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!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

心凉怎暖 2024-09-27 00:31:14

您可以尝试回显正在执行的 sql 并检查重音符号是否存在。

我猜他们不是...

[编辑]

你能试试这个：

php.ini

<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：（没关系）

CREATE TABLE `dummy` (
  `id` int(11) NOT NULL,
  `name` varchar(45) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin5$

这个对我有用......

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...

回复收藏 0 原文

~没有更多了~