如何使SELECT ... WHERE IN用于AND而不仅仅是OR？

Question

I have 3 MySQL tables:

blog_posts // Contains blog posts
blog_tags // Contains tags
rel__blog_post_tags // Relates tags to a blog post (refering relation IDs)

This is my PHP code:

// I get tags from URL: /tag/tag1;tag2;tag3
$tags = explode(';', $options['req_tags']);
// Prepare for prepared SQL query
$in = str_repeat('?,', count($tags) - 1) . '?';
// Prepare SQL param array as I need to mix category ID with tags...
$sql_params = $tags;

// This SQL will select with IN (equals to OR but want to change to AND)
$sql_get_posts = '
    SELECT * FROM blog_posts
    WHERE category_id = ?
    AND post_id IN
        (SELECT post_id FROM rel__blog_post_tags
            WHERE tag_id IN (' . $in . '));';

// Prepare the query
$get_posts = $PDO->prepare($sql_get_posts);
// Add category ID to beginning of array of SQL params (in front of tags)
array_unshift($sql_params, $options['category_id']);

// Now execute and get blog posts to array
$get_posts->execute($sql_params);
$postdata = $get_posts->fetchAll();

?>

OK so my problem here is that I use "WHERE tag_id IN...". This will return any blog posts that matches:

...AND post_id IN (SELECT post_id FROM rel__blog_post_tags WHERE tag_id='tag1' OR tag_id='tag2' OR tag_id='tag3'... and so on.

So basically, I cannot narrow down my blog post results by adding more tags, which is what I am after.

How can I change this SQL query to make it so that it will be like the below, so that if I have a blog post with ID "foobar" and it has a connection with tag1 and tag2 in the database it will not show up, as I am looking for blog post with ID "foobar" that has tag tag1, tag2 AND tag3:

...AND post_id IN (SELECT post_id FROM rel__blog_post_tags WHERE tag_id='tag1' AND tag_id='tag2' AND tag_id='tag3'... And so on...

Answer 1

You can fix it by creating the set of "and tag_id=?" string using for loop. Like shown below:

for($i=0; $i<count($tags);$++){
if($i=0){
$condition="tag_id=$tags[$i]";
}
else{
$condition.="and tag_id=$tags[$i]";
}

}