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

php mysql database-design tags tagging
mcbeav picture mcbeav · Mar 1, 2011 · Viewed 18k times · Source

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?

Answer

Joe Stefanelli picture Joe Stefanelli · Mar 1, 2011

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'

Related questions

Does VARCHAR size limit matter?

Possible Duplicate: Importance of varchar length in MySQL table When using VARCHAR (assuming this is the correct data type for a short string) does the size matter? If I set it to 20 characters, will that take up less space or …

Read More
Soft delete best practices (PHP/MySQL)

Problem In a web application dealing with products and orders, I want to maintain information and relationships between former employees (users) and the orders they handled. I want to maintain information and relationships between obsolete products and orders which include …

Read More
MySQL: Understanding mapping tables

When building a category navigation system for a business directory with a many to many relationship, I understand that it is good practise to create a mapping table. Category Table ( CategoryId, CategoryName ) Business Table ( BusinessId, BusinessName ) Category Mapping Table ( BusinessId, …

Read More