MySQL中如何在使用LIKE搜索语句的同时使用OR语句

发布于 2024-09-26 13:26:59 字数 508 浏览 7 评论 0原文

我正在尝试创建一个很好的小搜索语句，根据返回的内容使用不同的搜索词来搜索多个字段。我基本上设置了一个顺序，如果找不到一个搜索词，请尝试下一个搜索词。不过，我使用 WHERE 子句来仅搜索一种类型的数据。目前我收到一些奇怪的结果返回，因为我认为我的订单不正确，似乎我的 WHERE 子句被忽略。

这是我的声明（我使用 PHP）：

mysql_query("SELECT * FROM item AS i LEFT JOIN country AS c ON i.country_id = c.country_id WHERE i.item_status = 'active' AND (i.item_name LIKE '%".$_SESSION['item']."%') OR i.item_description LIKE '%".$_SESSION['description']."%' OR i.item_name LIKE '%".$_SESSION['description']."%' ");

提前谢谢您。

原文

I am trying to create a good little search statement that searches muitple fields using the with different search terms depending on what is returned. I am basiclly setting an order in which I want to search if one search term is not found try the next one. However I am using a WHERE clause to seperate search only one type of data. Currently I am getting some strange results returned because I don't think my order is correct, it seems that my WHERE clause is being ignored.

here is my statement (I am usig PHP):

mysql_query("SELECT * FROM item AS i LEFT JOIN country AS c ON i.country_id = c.country_id WHERE i.item_status = 'active' AND (i.item_name LIKE '%".$_SESSION['item']."%') OR i.item_description LIKE '%".$_SESSION['description']."%' OR i.item_name LIKE '%".$_SESSION['description']."%' ");

Thank you in advance.

分享到QQ

分享到微博

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

发布评论

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

躲猫猫 2024-10-03 13:26:59

为什么不使用全文搜索功能？

需要更改表结构以创建全文搜索索引。

ALTER TABLE item ADD FULLTEXT (item_name, item_description);

然后您的查询就会变得：

mysql_query("
SELECT * FROM item AS i LEFT JOIN country AS c ON i.country_id = c.country_id 
WHERE i.item_status = 'active' AND 
MATCH (i.item_name, i.item_description)
AGAINST (" . $_SESSION['item'] . " " . $_SESSION['description'] . ");
");

简单、准确且更快。

Why don't you use full-text search feature?

Need to alter table structure for create fulltext search index.

ALTER TABLE item ADD FULLTEXT (item_name, item_description);

Then your queries turns to:

mysql_query("
SELECT * FROM item AS i LEFT JOIN country AS c ON i.country_id = c.country_id 
WHERE i.item_status = 'active' AND 
MATCH (i.item_name, i.item_description)
AGAINST (" . $_SESSION['item'] . " " . $_SESSION['description'] . ");
");

Simple, accurate, and faster.

回复收藏 0 原文

嗫嚅 2024-10-03 13:26:59

将右括号移至语句末尾：

SELECT * 
FROM item AS i LEFT JOIN country AS c 
ON i.country_id = c.country_id 
WHERE i.item_status = 'active' 
AND (i.item_name LIKE '%".$_SESSION['item']."%' 
OR i.item_description LIKE '%".$_SESSION['description']."%' 
OR i.item_name LIKE '%".$_SESSION['description']."%')

Move the right parenthesis to the end of the statement:

SELECT * 
FROM item AS i LEFT JOIN country AS c 
ON i.country_id = c.country_id 
WHERE i.item_status = 'active' 
AND (i.item_name LIKE '%".$_SESSION['item']."%' 
OR i.item_description LIKE '%".$_SESSION['description']."%' 
OR i.item_name LIKE '%".$_SESSION['description']."%')

回复收藏 0 原文

和影子一齐双人舞 2024-10-03 13:26:59

这是你的括号放错地方了。这是正确的代码：

mysql_query("SELECT * FROM item AS i LEFT JOIN country AS c ON i.country_id = c.country_id WHERE i.item_status = 'active' AND (i.item_name LIKE '%".$_SESSION['item']."%' OR i.item_description LIKE '%".$_SESSION['description']."%' OR i.item_name LIKE '%".$_SESSION['description']."%')");

It is your parentheses in the wrong place. Here is the correct code:

mysql_query("SELECT * FROM item AS i LEFT JOIN country AS c ON i.country_id = c.country_id WHERE i.item_status = 'active' AND (i.item_name LIKE '%".$_SESSION['item']."%' OR i.item_description LIKE '%".$_SESSION['description']."%' OR i.item_name LIKE '%".$_SESSION['description']."%')");

回复收藏 0 原文