mysql php match multiple keywords

I have three tables which are currently structured in the following way

Table: Images
image_id
image_title
...

Table: Keywords
keyword_id
keyword

Table: Image_Keyword
image_id
keyword_id

With this structure, I'm able to search if any images match any keywords using joins and or statements - however I would like to be able to retrieve images that have multiple keywords matches e.g. "keyword = ('red' or 'dress') and 'night'" - which would return all images that had either 'red' or 'dress' in them, alongside night.

Ideally I want to allow the user to be able to specify the AND and OR commands in the search box, which is why I have so far opted out of making separate joins for each new keyword - however I'm not sure how to proceed with the structuring of the query.

Currently I have the following, without the 'and' implementation:

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 go about creating the 'and' portion of this query would be greatly appreciated! Thanks kindly,

Dan

// UPDATE

So it looks as if I am going to have to do it by creating joins for each 'AND' request that I need to sort out - however I have an extension on the requirements now...

I have two other tables which follow the following structure

Table ImageData
id
image_id
caption_id
...

Table Caption
id
data (text)

In this instance, I would want to search for the keywords ('开发者_开发问答red','dress' and 'night'), using the same 'AND' and 'OR' capability as before, but also return the image if the text matches (using the same rules) in the caption data field. I would assume I potentially use an OR after the 'keyword' search, and then use a fulltext search on the caption, however I don't know if there is a cleaner way of combining the two, maybe even as two separate queries and then choosing the distinct results - which might allow for instances where the AND is successful in the keywords, and the OR is successful in the caption.

Any thoughts would be fantastic

Thanks again

I think what you will end up is this -

1. One INNER JOIN for all your ORs.
2. One INNER JOIN each for all your ands.

For example -

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 script would look something like.

$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";

You get the idea..

I would just generate the WHERE part of the query in PHP script, like this:

<?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;
?>