MySQL using AND in a single column

Question

I'm working on a tag system where an array (of tags) is queried and all rows with the same tags in the Tag column are selected.

The issue is that I used the IN condition which is a type of an OR function, which selected all the rows with the same tags as opposed to narrowing them down, for example.

Instead of narrowing down an image with tags like 'sun' and 'landscape' it would select all images with those tags.

What I'm looking for is an AND version of IN () or a substitute that can work with arrays.

This is just an example. In reality, the user can add as many tags as they want

+----+---------+---------+
| ID | ImageID |   Tag   |
+----+---------+---------+
| 1  |   2     |   sun   |
+----+---------+---------+
| 2  |   12    |landscape|
+----+---------+---------+
| 3  |   15    |  field  |
+----+---------+---------+
| 4  |   15    |landscape|
+----+---------+---------+

My code

$tag = $_POST['tag'];
    $tag = preg_split("#/#", $tag);
    $tag = implode("', '", $tag);

    $query = "SELECT * FROM ComicStripTags WHERE `Tag` IN ('$tag')";
    $result = mysqli_query($link, $query);
    while ($row = mysqli_fetch_array($result)){
        $ID[] = $row['ImageID'];
    }

Thanks

P.S. I'm not working in SQL, im working in PHP

I'm not aware of an option like that. You can probably use `GROUP_CONCAT` and `WHERE LIKE`, but that might get unwieldy. — aynber, Oct 10 '17 at 20:58
Correct me if I'm wrong, but basically you're looking for an ImageID that has both the tags of `landscape` and `field`, so you'd only get #15. If you did both `sun` and `landscape`, you'd get no results. — aynber, Oct 10 '17 at 21:00
We are always glad to help and support new coders but ***you need to help yourself first. :-)*** After [**doing more research**](https://meta.stackoverflow.com/q/261592/1011527) if you have a problem **post what you've tried** with a **clear explanation of what isn't working** and provide [a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). Read [How to Ask](http://stackoverflow.com/help/how-to-ask) a good question. Be sure to [take the tour](http://stackoverflow.com/tour) and read [this](https://meta.stackoverflow.com/q/347937/1011527). — Jay Blanchard, Oct 10 '17 at 21:00
Based on your sample data if you want all images with sun and landscape a select distinct(ImageID) from table where tag IN('sun','landscape') would select all images with those tags, and give you a list of single image ID's. If you had a single field with Tag = "sun,landscape" then you would have to use "like" — Severun, Oct 10 '17 at 21:02
@aynber That was just an example. Im trying to make this fully automatic. This code is going to my website where users can upload images and im trying to create a tag search functionality. — AnotherAccount, Oct 10 '17 at 21:02
If one image can have multiple tags, you should create another tables with tags and one multi to one containing image id and tag id. Then you will be able to to list all images with two tags. — Marcin Mrugas, Oct 10 '17 at 21:13
@MarcinMrugas The thing is that that's just an example. In reality the user can add as many tags as they want — AnotherAccount, Oct 10 '17 at 21:30

Thiago Barcala · Accepted Answer · 2017-10-10T21:40:54.097

0

You can do it using inner joins:

select * from TAGS a
inner join TAGS b
on a.ImageID = b.ImageID
where a.tag = 'field'
and b.tag = 'landscape'

According to your PHP code:

$joins = [];
$conditions = [];

foreach (preg_split("#/#", $_POST['tag']) as $index => $tag) {
    $alias = "ComicStripTags_$index";
    $joins[] = "ComicStripTags AS $alias" . ($index > 0 ? " ON ComicStripTags_0.ImageID = $alias.ImageID" : '');
    $conditions[] = "$alias.Tag = '$tag'";
}

$query = sprintf("SELECT * FROM %s WHERE %s", implode(' INNER JOIN ', $joins), implode(' AND ', $conditions));

edited Oct 10 '17 at 21:40

answered Oct 10 '17 at 21:05

Thiago Barcala

6,463
2
20
23

I don't understand what you mean by a.ID and b.ID. BTW the table isn't limited to only two tags. The user can add as many tags as they want – AnotherAccount Oct 10 '17 at 21:31
@AnotherAccount check my update – Thiago Barcala Oct 10 '17 at 21:41
1

IT WORKED!!!!!!!! I own you SO much! Thank you so much!! – AnotherAccount Oct 10 '17 at 21:45
You don't understand, I worked on this for half a day and it got so frustrating it gave me a headache! – AnotherAccount Oct 10 '17 at 21:47

score 0 · Answer 2 · answered Oct 10 '17 at 21:15

Assuming each ImageID can have only one of each tag, you can group by ImageID and select only rows where the count of tags equals the size of the input array.

SELECT ImageID FROM ComicStripTags
WHERE Tag IN ('landscape', 'field')
GROUP BY ImageID HAVING COUNT(Tag) = 2;

score 0 · Answer 3 · answered Oct 10 '17 at 21:20

0

Alternatively you can do some ranking which would support partial tag matches as well:

SELECT
  ImageID,
  group_concat(Tag) as 'matched_tags',
  count(*) as 'tag_matches'
FROM TAGS
WHERE Tag IN ( [list_of_tags] )
GROUP BY ImageID
HAVING tag_matches >= [minimum_number_of_tags]
ORDER BY tag_matches DESC

answered Oct 10 '17 at 21:20

Sammitch

30,782
7
50
77

Could you explain this? I don't understand Group By, Having, group_contact or what you meant by matched_tags or tag_matches. Thanks – AnotherAccount Oct 10 '17 at 21:41
http://www.mysqltutorial.org/mysql-aggregate-functions.aspx – Sammitch Oct 10 '17 at 22:22

MySQL using AND in a single column

3 Answers3