php - 在 SQL 的 IN 语句中组合 LIKE？

Question

大家好，我有一个查询，当前从表单中获取关键字列表并返回前 5 个匹配的关联语句。它们位于两个不同的表中(关键字 和 问题)。我遇到的问题是，如果有人搜索 TURNS，则会返回结果，但如果您搜索 TURN，则没有结果。我知道这看起来很简单，但我正在处理的查询却不那么简单。这是我正在处理的查询:

SELECT p.*, pc.wordCount, pc.commonCount    
FROM Problem p INNER JOIN (
SELECT p.Problem_Identity, COUNT(k.Keyword_Identity) AS "wordCount",
SUM(k.Keyword_Common) as "commonCount"
FROM( SELECT * FROM Keyword ) k
INNER JOIN KeywordsInProblem kip ON (k.Keyword_Identity = kip.Keyword_Identity)
INNER JOIN Problem p ON (p.Problem_Identity = kip.Problem_Identity)
WHERE UCASE(k.Keyword_Word) IN(\''. strtoupper(implode("', '", $keyArr)) .'\')
GROUP BY p.Problem_Identity) pc
ON (p.Problem_Identity = pc.Problem_Identity) where pc.commonCount > 0
ORDER BY pc.wordCount DESC, pc.commonCount DESC LIMIT 5

PS:$keyArr 变量会分解表单输入语句，以便在数据库中使用它之前对其进行清理。

Answer 1

$likeCondition = "";
foreach($keyArr as $key)
{
    $likeCondition .= (strlen(trim($likeCondition)))?
           " OR UCASE(k.Keyword_Word) LIKE '%".strtoupper($key)."%' ":
           " WHERE UCASE(k.Keyword_Word) LIKE '%".strtoupper($key)."%' ";
}

$sql = "SELECT 
    p.*, 
    pc.wordCount, 
    pc.commonCount    
FROM 
    Problem p 
        INNER JOIN (
            SELECT 
                p.Problem_Identity, 
                COUNT(k.Keyword_Identity) AS wordCount,
                SUM(k.Keyword_Common) AS commonCount
            FROM(
                SELECT * FROM Keyword) k
                    INNER JOIN 
                        KeywordsInProblem kip 
                    ON 
                        (k.Keyword_Identity = kip.Keyword_Identity)
                    INNER JOIN 
                        Problem p 
                    ON 
                        (p.Problem_Identity = kip.Problem_Identity)
                ".$likeCondition."
        GROUP BY p.Problem_Identity) pc
        ON 
            (p.Problem_Identity = pc.Problem_Identity) 
        WHERE 
            pc.commonCount > 0
ORDER BY pc.wordCount DESC, pc.commonCount DESC LIMIT 5";