MySQL——我如何知道在使用时哪个字段与结果匹配。

I'm creating an engine to find results in several fields using MySQL, something like:

我正在创建一个引擎，在几个字段中使用MySQL查找结果，比如:

SELECT table1.*, table2.* FROM table1
JOIN table2
ON table1.id = table2.tid
WHERE 
(table1.field1 LIKE 'keyword%' OR table1.field2 LIKE 'keyword%')
AND
(table2.field1 LIKE 'keyword%' OR table2.field2 LIKE 'keyword%')
ORDER BY table1.id ASC;

But now I need to know which field matches the keyword... How can I do that?

但是现在我需要知道哪个字段匹配关键字…我该怎么做呢?

BTW, I'm using PHP.

顺便说一句,我是使用PHP。

4 个解决方案

#1

unverified:

未经证实的:

SELECT table1.*, 
       table2.*, 
       case (table2.field1 LIKE 'keyword%' and table1.field1 LIKE 'keyword%')
           when 1 
           then 'f1' 
           else 'f2' 
       end as field
FROM table1
...

#2

You could do

你可以做

SELECT table1.*, table2.*, 'table1' as `target` as FROM table1
JOIN table2
ON table1.id = table2.tid
WHERE 
(table1.field1 LIKE 'keyword%' OR table1.field2 LIKE 'keyword%')
UNION ALL 
SELECT table1.*, table2.*,'table2' as target FROM table1
JOIN table2
ON table1.id = table2.tid
WHERE 
(table2.field1 LIKE 'keyword%' OR table2.field2 LIKE 'keyword%')

and then check if target is table1 or table2 when you retrieve the results

当你检索结果时，检查目标是否为table1或table2。

#3

You could add columns for your conditions

您可以为您的条件添加列。

SELECT table1.*, table2.*, 
table1.field1 LIKE 'keyword%' AS Table1Field1Match, 
table1.field2 LIKE 'keyword%' AS Table1Field2Match,
table2.field1 LIKE 'keyword%' AS Table2Field1Match, 
table2.field2 LIKE 'keyword%' AS Table2Field2Match
FROM table1
JOIN table2
ON table1.id = table2.tid
WHERE 
(table1.field1 LIKE 'keyword%' OR table1.field2 LIKE 'keyword%')
AND
(table2.field1 LIKE 'keyword%' OR table2.field2 LIKE 'keyword%')
ORDER BY table1.id ASC;

#4

In your loop you can do this :

在你的循环中，你可以这样做:

Table X
Field1      Field2
-------------------
boat        caca
hello       boat

$Keyword = 'boat';
while($Res = blabla){
    if(strpos($Res['Field1'], $Keyword) !== false){
        echo 'String found in Field 1<br />';
    }

    if(strpos($Res['Field2'], $Keyword) !== false){
        echo 'String found in Field 2<br />';
    }
}

// Will output
String found in Field 1<br />
String found in Field 2<br />

#1