Mysql query for php tagging system

Question

I am trying to display articles with tags. The query below works perfect.

This is my query:

SELECT
    item.item_id,
    item.item_title,
    item.item_createdate,
    GROUP_CONCAT(tag.tag_name) AS TagName
FROM
    item
LEFT JOIN `item-tag` ON `item-tag`.item_id = item.item_id
LEFT JOIN tag ON tag.tag_id = `item-tag`.tag_id
    /*WHERE item.item_type = 1*/
GROUP BY
    item.item_id
ORDER BY
    item_createdate
DESC

The problem I am having starts when I filter the results. If I want to see the results for the "Tag 1" I add the where clause:

 WHERE tag.tag_name = 'Tag 1'

this removes the other tags from the GROUP_CONCAT and I get this result:

How can I get the tagged articles and also display the additional tags for that article?

Have you tried using WHERE tag.tag_name LIKE '%Tag 1%'? That way, it won't matter if there are multiple tag names as long as that one exists. — Zeke
– Zeke, Commented Nov 8, 2017 at 21:41
Sometimes it takes me a while to get LIKE to work as well, are you using prepared statements? — Zeke
– Zeke, Commented Nov 8, 2017 at 21:46
no, i am trying it on phpmyadmin. This works: WHERE tag.tag_name IN(SELECT tag_name FROM tag) but i might have too many tags. so it is not a good option — eMRe
– eMRe, Commented Nov 8, 2017 at 21:48
@Zeke This worked. I think the Select only returns the tag ids from the join instead of all. (SELECT tag_id FROM tag where tag.tag_id = item-tag.tag_id) — eMRe
– eMRe, Commented Nov 8, 2017 at 22:04

Martin Cook · Accepted Answer · 2017-11-08 22:39:24Z

1

Try:

WHERE EXISTS 
    (SELECT tag_id FROM tag 
    JOIN `item-tag` tmp ON tag.tag_id = tmp.tag_id 
    WHERE tmp.item_id = item.item_id AND tag.tag_name = 'Tag 1')

Note that filters on the tag_name don't work, because GROUP_CONCAT and GROUP BY logically generate a larger table, then concatenate results from this. Filtering out too early removes entries from this larger table, so the tags can't appear in the concatenation.

The alternate approach is to use your existing query as a subquery, and surround it with

SELECT * FROM (...existing query...) as foo
WHERE TagName LIKE '%Tag 1%'

edited Nov 8, 2017 at 22:39

answered Nov 8, 2017 at 21:39

Martin Cook

7596 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

eMRe Over a year ago

it is still displaying the articles tagged with 'Tag 1'

eMRe Over a year ago

this worked. WHERE tag.tag_id IN (SELECT tag_id FROM tag where tag.tag_id = item-tag.tag_id)

Martin Cook Over a year ago

@emre This won't be specific to 'Tag 1' though, surely? I have edited the answer to add a join

eMRe Over a year ago

yeah, it didn't work. I thought it did. Your one worked. will it be more efficient than WHERE tag.tag_name IN(SELECT tag_name FROM tag)

Martin Cook Over a year ago

If there are many tags for one item, then it could be more efficient, because the EXISTS version can return true as soon as one is found, rather than generating the complete list. In practice, I don't think you are going to have many tags called 'Tag 1' corresponding to the same item_id, so it doesn't matter

Tim Morton · Accepted Answer · 2017-11-08 22:35:49Z

1

SELECT
  item.item_id,
  item.item_title,
  item.item_createdate,
GROUP_CONCAT(tag.tag_name) AS TagName
FROM
  item
LEFT JOIN `item-tag` ON `item-tag`.item_id = item.item_id
LEFT JOIN tag ON tag.tag_id = `item-tag`.tag_id

--limit your existing query by only including item_ids with 'Tag 1'
WHERE item.item_id IN(
    SELECT `item-tag`.item_id
    FROM tag INNER JOIN `item-tag` ON tag.tag_id=`item-tag`.tag_id
    WHERE tag.tag_name=?
    )

GROUP BY
  item.item_id
ORDER BY
  item_createdate
DESC

Note that I used a placeholder which would be used in a prepared statement. For PHPMyAdmin, of course, just put the value in place of the ?

Personal preference-- I would name the table item_tag so that you don't have to use those pesky backslashes...

answered Nov 8, 2017 at 22:35

Tim Morton

2,6341 gold badge17 silver badges28 bronze badges

1 Comment

eMRe Over a year ago

thanks Tim. I wanted to use IN clause but couldn't get it working. Your solution works perfect.

Collectives™ on Stack Overflow

Mysql query for php tagging system

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related