在两个mysql表中搜索多个列？

I have two tables and a search form to just search for a keyword. I am trying to search for that keyword on two table for multiple columns and if the query matches get the id column for further use. I have tried this (suppose "coupon" is the term user is searching for)

我有两个表和一个搜索表单来搜索关键字。我试图在两个表上为多个列搜索该关键字，如果查询匹配，则获取id列以供进一步使用。我试过这个（假设“优惠券”是用户正在搜索的术语）

SELECT `ID` FROM `Profiles` AS `p` WHERE `p`.`Status` = 'Active' AND `p`.`Address`
LIKE '%coupon%' OR `p`.`BusinessName` LIKE '%coupon%' OR `p`.`BusinessSubCategory`
LIKE '%coupon%' OR `p`.`DescriptionMe` LIKE '%coupon%' OR `p`.`Tags` LIKE '%coupon%'
UNION SELECT `id` FROM `products` AS `d` WHERE `d`.`status` = 'approved' AND
`d`.`title` LIKE '%coupon%' OR `d`.`desc` LIKE '%coupon%' OR `d`.`tags` LIKE '%coupon%'

Here i want the id of profile and id of products that matches the keyword. I tried this and this is returning very strange results and looks like only profile ID. So, its a wrong query. What should be the query for this kind of search? INNER JOIN? Please give me some sample queries for this, i will be very grateful for any help.

在这里，我想要与匹配关键字匹配的产品的个人资料和ID。我试过这个，这会返回非常奇怪的结果，看起来只有个人资料ID。所以，这是一个错误的查询。这种搜索的查询应该是什么？内部联接？请给我一些示例查询，我将非常感谢任何帮助。

2 个解决方案

#1

Try this::

尝试这个：：

SELECT `ID`,'profile_ID' FROM 
`Profiles` AS `p` 
WHERE `p`.`Status` = 'Active' AND `p`.`Address`
LIKE '%coupon%' OR `p`.`BusinessName` LIKE '%coupon%' OR `p`.`BusinessSubCategory`
LIKE '%coupon%' OR `p`.`DescriptionMe` LIKE '%coupon%' OR `p`.`Tags` LIKE '%coupon%'


UNION ALL

SELECT `id`, 'productID' FROM `products` AS `d` WHERE `d`.`status` = 'approved' AND
`d`.`title` LIKE '%coupon%' OR `d`.`desc` LIKE '%coupon%' OR `d`.`tags` LIKE '%coupon%'

#2

First off, I wouldn't use AS p when you're not doing an INNER JOIN etc... seems like overdoing it plus I guess you need parentheses after AND - surrounding the ORs as well if you explicity want the to find results where status is "Active".

首先，当你没有做INNER JOIN等时我不会使用AS p ...似乎过度使用它我想你在AND之后需要括号 - 围绕OR也是如果你明确想要找到结果的地方状态为“活动”。

How about:

怎么样：

    SELECT ID FROM Profiles WHERE Status = 'Active' AND (Address LIKE '%coupon%' OR BusinessName LIKE '%coupon%' OR BusinessSubCategory LIKE '%coupon%' OR DescriptionMe LIKE '%coupon%' OR Tags LIKE '%coupon%')
UNION SELECT id FROM products WHERE status = 'approved' AND (title LIKE '%coupon%' OR desc LIKE '%coupon%' OR tags LIKE '%coupon%')

#1