Mysql php matches multiple keywords

Question

I have three tables that are currently structured as follows.

Table: Images
image_id
image_title
...

Table: Keywords
keyword_id
keyword

Table: Image_Keyword
image_id
keyword_id

In this structure, I can search if any images match any keywords using joins and / or operators, however I would like to be able to get images with several keywords, for example. "keyword = (" red "or" dress ") and" night "- this will return all images that were either" red "or" dressed up "in them along with the night.

Ideally, I want the user to be able to specify the AND and OR commands in the search box, so I have so far refused to create separate joins for each new keyword - however, I'm not sure how to continue structuring the query.

Currently, I have the following, without the implementation of "and":

SELECT i.* FROM images i
JOIN image_keyword ik ON i.id = ik.image_id
JOIN keywords k ON k.id = ik.keyword_id
WHERE k.keyword IN ('night','red') 

Any help on how to create the "and" part of this request would be greatly appreciated! Thank you,

Dan

// UPDATE

So, it looks like I will need to do this by creating connections for each AND request that I need to sort, but now I have an extension for the requirements ...

I have two other tables that follow the following structure

Table ImageData
id
image_id
caption_id
...

Table Caption
id
data (text)

( "", "" "" ), "" "", , , ( ) . , OR "keyword", , , , , , , , ​​ , OR - .

Answer 1

, -

• INNER JOIN OR.
• INNER JOIN ands.

-

SELECT i.* FROM images i
INNER JOIN image_keyword ik ON i.id = ik.image_id
INNER JOIN keywords kOR ON kOR.id = ik.keyword_id AND (kOR.keyword IN ('dress', 'red'))
INNER JOIN keywords kAND1 ON kAND1.id = ik.keyword_id AND kAND1.keyword = 'night'

PHP .

$orKeywords = arrya('dress', 'red', 'white');
$andKeywords = array('night', 'day');
$orJoin = '';
$andJoin = '';
if(count($orKeywords) > 0)
{
    $orCondition = "'".implode("', '", $orKeywords)."'";
    $orJoin = " INNER JOIN keywords kOR ON kOR.id = ik.keyword_id AND kOR.keyword IN ($orCondition) ";
}
if(count($andKeywords) > 0)
{
    $cnt = 1;
    foreach($andKeywords as $keyword)
    {
        $andJoin .= " INNER JOIN keywords kAND{$cnt} ON kAND{$cnt}.id = ik.keyword_id AND kAND{$cnt}.keyword = '$keyword' ";$cnt++;
    }
}
$sql = "SELECT i.* FROM images i
INNER JOIN image_keyword ik ON i.id = ik.image_id
$orJoin
$andJoin";

.

Answer 2

WHERE PHP script, :

<?php
$entered_keywords = array('night','red');
$logic = 'OR'; // or 'AND'
$sql_where = implode(' '.$logic.' ', "k.keyword='$entered_keywords'"); //don't forget the escaping here!
$sql = 'SELECT i.* FROM images i
JOIN image_keyword ik ON i.id = ik.image_id
JOIN keywords k ON k.id = ik.keyword_id
WHERE '.$sql_where;
?>