Ad:

MySQL Discussion Thread
Unsolved
Views: 3107

Oct 30th, 2009

MySQL Query - Searching for multiple keywords

Expand Post »

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:

sql Syntax (Toggle Plain Text)
SELECT file.FileID WHERE file.FileID=file_keyword.FileID AND (file_keyword.Keyword LIKE '%ABC%' AND file_keyword.Keyword LIKE '%XYZ%');
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.

Last edited by peter_budo; Oct 30th, 2009 at 7:35 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks)

aptitudedude

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Oct 2009

Permalink

Oct 30th, 2009

Re: MySQL Query - Searching for multiple keywords

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

MySQL Syntax (Toggle Plain Text)
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%'
))
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.

Calver

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Sep 2009

Permalink

Oct 31st, 2009

Re: MySQL Query - Searching for multiple keywords

You could also try using joins:

sql Syntax (Toggle Plain Text)
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%'
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

Atli

Reputation Points: 93

Solved Threads: 70

Posting Pro

Offline

526 posts
since May 2007

Permalink

Oct 31st, 2009

Re: MySQL Query - Searching for multiple keywords

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

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Sep 2009

Permalink

Oct 31st, 2009

Re: MySQL Query - Searching for multiple keywords

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

MySQL Syntax (Toggle Plain Text)
<?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);
?>
<?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);
?>

Calver

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Sep 2009

Permalink

Oct 31st, 2009

Re: MySQL Query - Searching for multiple keywords

Ok, so I almost have this figured out.

sql Syntax (Toggle Plain Text)
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')
);
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.

php Syntax (Toggle Plain Text)
$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;
$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:

sql Syntax (Toggle Plain Text)
INSERT INTO `file`(`name`) VALUES ('File1'), ('File2');
 
INSERT INTO `file_keyword`(`file_id`, `keyword`)
VALUES
    (1, 'first'), (1, 'second'),
    (2, 'third'), (2, 'fourth');
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.

Last edited by Atli; Oct 31st, 2009 at 1:29 pm.

Atli

Reputation Points: 93

Solved Threads: 70

Posting Pro

Offline

526 posts
since May 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: Problems restoring mysql database backups

Next Thread in MySQL Forum Timeline: Stored Procedures Help please

DaniWeb Message

Cancel Changes

User Name
Password