how can i create a tagging system using php and mysql?

Question

wondering how i can create a tagging system in php and with a mysql database, my initial thoughts were to create a row in the table where the articles are stored, named tags, and list the tags seperated by commas, but i am not sure how i can create a query that searches for matching tags, i dont want to have to query every article everytime someone clicks a tag. can anyone help?

I'd suggest reading up on "database normalization". A good start would be en.wikipedia.org/wiki/Database_normalization. — Marc B
– Marc B, Commented Mar 1, 2011 at 21:06

Joe Stefanelli · Accepted Answer · 2011-03-01 21:15:15Z

41

You're describing a many-to-many relationship between Articles and Tags. You'd want to use an intermediate junction table to resolve that relationship.

enter image description here

Now, to find all articles that match a selected tag:

SELECT a.article_title
    FROM Tag t
        INNER JOIN Article_Tag_Xref atx
            ON t.tag_id = atx.tag_id
        INNER JOIN Article a
            ON atx.article_id = a.article_id
    WHERE t.tag_name = 'SelectedTagName'

edited Mar 1, 2011 at 21:15

answered Mar 1, 2011 at 21:09

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

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

2 Comments

Samuel Over a year ago

What about finding all articles that match all tags of a given list?

PirateApp Over a year ago

seems like there are 3 ways to do this 1) denormalized table with all tags as part of main table 2) 2 tables with the main have item id and second having item id and tag where both item id and tag are indexed and 3) the structure you described

Mauro · Accepted Answer · 2011-03-01 21:08:15Z

2

tbl_articles has articleID, etc.

tbl_tags has tagID, etc.

tbl_taggedarticles has taggedArticleID, articleID, tagID

SELECT articleID FROM tbl_taggedarticles WHERE tagID = "searchedID"

answered Mar 1, 2011 at 21:08

Mauro

5995 silver badges16 bronze badges

Collectives™ on Stack Overflow

how can i create a tagging system using php and mysql?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related