MySQL Query - Searching for multiple keywords

[aptitudedude]

Hello,

I have a two tables created to apply keywords to a given entry:

Table: File
FileID (int, pk, auto incr)
Name (varchar)

Table File_Keyword
KeywordID (int, pk, auto incr)
FileID (fk)
Keyword (varchar)

Given that I have a record in table File with 2 corresponding records in table File_Keyword assigning keywords "ABC" and "XYZ" to the given FileID.

What is a simple sql query to only return results if both strings are matched:

SELECT file.FileID WHERE file.FileID=file_keyword.FileID AND (file_keyword.Keyword LIKE '%ABC%' AND file_keyword.Keyword LIKE '%XYZ%');

Any assistance would be appreciated.

Thank you in advance.

[Calver]

Hello, I had a similar problem recently, and solved it like this...

SELECT A.FileID, A.Name FROM file AS A
WHERE EXISTS ( SELECT B.FileID FROM file_keyword AS B
WHERE A.FileID = B.FileID AND B.Keyword LIKE '%ABC%'
AND EXISTS   ( SELECT B.FileID FROM file_keyword AS B
WHERE A.FileID = B.FileID AND B.Keyword LIKE '%XYZ%'
))

I still don't know if there's an easier way to do it.

[Atli]

You could also try using joins:

SELECT f.FileID, f.Name 
FROM `File` AS f
INNER JOIN `File_Keyword` AS fk1
    ON f.`FileID` = fk1.`FileID`
    AND fk1.`Keyword` LIKE '%key1%'
INNER JOIN `File_Keyword` AS fk2
    ON f.`FileID` = fk2.`FileID`
    AND fk2.`Keyword` LIKE '%key2%'

There must be a more dynamic way tho. A way to check multiple keywords without hard-coding in a sub-query or a join for each of them.

Damn... now I won't be able to think about anything else until I find it xD

[Calver]

I never thought about joining the same table more than once. I think I can use that :)

It should be fairly straightforward to build the querystring with an array of search terms and a foreach loop through the array.

[Calver]

Here's my effort (still fairly new to PHP) ...

<?php
$searches = array ("saloon", "diesel", "blue");
$fileID = 123;
$count = 0;
$alias = "";
$sql = "SELECT f.FileID, f.Name FROM `File` AS f ";
foreach ($searches as $search) {
    $count ++;
    $alias = "fk".$count;
    $sql .= "INNER JOIN `File_Keyword` AS ".$alias
        ." ON f.`FileID` = ".$alias.".`FileID`
    AND ".$alias.".`Keyword` LIKE '%".$search."%' ";
}
$sql .= "WHERE f.`FileID` = ".$fileID;
echo ($sql);
?>

[Atli]

Ok, so I almost have this figured out.

SELECT f.`file_id`, f.`name`
FROM `file` AS f
WHERE f.`file_id` NOT IN(
    SELECT fi.`file_id` 
    FROM `file` AS fi
    INNER JOIN `file_keyword` AS fki
        ON fi.`file_id` = fki.`file_id`
    WHERE fki.`keyword` NOT IN('first', 'second', 'third')
);

There you can just list the keywords in the last NOT IN clause.

$keywords = array ("first", "second", "third");
$keyword_list = implode(", ", $keywords);

$sql = <<<SQL
SELECT f.`file_id`, f.`name`
FROM `file` AS f
WHERE f.`file_id` NOT IN(
    SELECT fi.`file_id` 
    FROM `file` AS fi
    INNER JOIN `file_keyword` AS fki
        ON fi.`file_id` = fki.`file_id`
    WHERE fki.`keyword` NOT IN({$keyword_list})
);
SQL;

echo $sql;

Only problem is, this returns files where all of it's keywords are in the list, but it doesn't have to match every keyword in the list.
For example, if I have created these rows:

INSERT INTO `file`(`name`) VALUES ('File1'), ('File2');

INSERT INTO `file_keyword`(`file_id`, `keyword`)
VALUES
    (1, 'first'), (1, 'second'),
    (2, 'third'), (2, 'fourth');

Running the above query, it would return 'File1', even tho it only matches two out of three keywords.