Adding tagging to my php CMS

March 22, 2006 1:51 PM

Whats the best way to setup and implement a tagging/folksonomy system into a php content management system from scratch?

I use a custom php based content management system at my work and i'm wondering what what the best way to set up a tagging/folksonomy type system like the ones you see here and delisous, etc. i'm building it into my system by hand, but i'd really like some advice on where to start, such as table structure, useful functions, etc.

I appreciate any help, this has been a difficult thing to google for.

posted by yeahyeahyeahwhoo to computers & internet (12 comments total)

It's super easy actually.

You have a tags table in your db, and it should have fields for tag_name and parent_id at the very least. The parent_id corresponds to whatever you are associating tags with, be they single pages, blog posts, whatever.

Then you create a form to add tags however you want (flickr style, or make them like yahoo's myweb) and associate them with your parent_ids when adding new content or editing old content.

Finally, you just display them on the appropriate page where parent_id = foo and you grab all tag_name items with parent_id = foo.
posted by mathowie at 2:08 PM on March 22, 2006

Here's your answer.

There are some tricky bits because of the limitations of SQL. Tradeoffs, if you will. If you want to allow infinite tags per piece of content, you have to set up the database like so. If you can limit the number of tags, you can set it up like so. If the query has to be fast, you have to set up the database like so, if it doesn't, you can set it up like so. And so on.
posted by jellicle at 2:36 PM on March 22, 2006

Yikes. The "MySQLicious" schema in that link is just horrid. By all means do not pick that one. The "Toxi" one (which displays actual knowledge of how SQL is supposed to work) is your best bet. Don't let the fact that whoever wrote that summary is apparently database-clueless ("I couldn’t come up with an easy MySQL-query.." --WTF?) cloud your judgement.
posted by Rhomboid at 5:00 PM on March 22, 2006

Ditto Rhomboid. That person is an idiot; don't listen to them. There's no good reason to cram all the tags in one field. Parse the user input, ffs.

You shouldn't need to fulltext search the tags unless you are beholden to the whims of ridiculous people like librarians.
posted by beerbajay at 6:38 PM on March 22, 2006

The person who wrote that article is speaking.. :-)

It surely depends on what you are trying to do. If you are are in a hurry and you are sure you'll never get more than 1000 entries then I still think you should take the most simple "MySQLicious" approach. That is completely denormalized and yeah, not optimized by any mean (as Rhomboid said), it's the "quick and dirty" solution. You won't have many options. Building a tag cloud with this solution is quite difficult as you don't see how often a tag is used.

We recently added tags to our CMS and we took the "toxi" solution, this is 3NF, the way you learnt it in school. You can have multiword tags and everything you like. You have to deal with tag orphans. Overall you have to invest some time and thoughts into implementing your tag schema this way but it's worth it.

If you're building middle-scale application (up to 1 million items), performance wise you best pick the toxi or the mysqlicious solution with MySQL fulltext search.
If you want to build a bigger app, then you have to mix in non-RDBMS-systems like Lucene.

Yes, speaking about minus-queries (like "I want to see all posts tagged 'fruit' and 'vegetable' but not 'apple'") I wrote "I couldn't cdome up with an easy MySQL-query": I still cannot think of an easy way to solve this. I would have to do two separate queries one for the "wanted" tags and one for the "unwanted" tags and then would have to join these together. This would be very slow I guess.
Can you come up with a good query?
posted by philipp.keller at 11:30 PM on March 22, 2006

Yes, speaking about minus-queries (like "I want to see all posts tagged 'fruit' and 'vegetable' but not 'apple'") I wrote "I couldn't cdome up with an easy MySQL-query": I still cannot think of an easy way to solve this. I would have to do two separate queries one for the "wanted" tags and one for the "unwanted" tags and then would have to join these together. This would be very slow I guess.
Can you come up with a good query?

How about:

SELECT b.*FROM tagmap bt, bookmark b, tag tWHERE bt.tag_id = t.tag_idAND (t.name IN ('fruit', 'vegetable'))AND b.id = bt.bookmark_idAND t.tag_id NOT IN (SELECT tag_id FROM tag WHERE name = 'apple')GROUP BY b.idHAVING COUNT( b.id )=2

MySQL has had subqueries since 4.1, released 2003-04-09 -- nearly three years ago! The people that refuse to use "real" database features like subqueries in MySQL because they're stuck in the stone age of 3.whatever are the same people that give MySQL a bad name for being a toy database.
posted by Rhomboid at 12:03 AM on March 23, 2006

Rhomboid: Thanks! I confess I'm stuck in the stone age of 3.whatever and therefore confess that I'm "database-clueless" :-)
I'll add this to my article if you don't mind?
posted by philipp.keller at 12:08 AM on March 23, 2006

Hum, that is bogus... I spoke too soon.
posted by Rhomboid at 12:14 AM on March 23, 2006

I think this is closer to what you want, although I didn't test it:

SELECT b.*FROM bookmark b, tagmap bt, tag tWHERE b.id = bt.bookmark_id AND bt.tag_id = t.tag_idAND (t.name IN ('fruit', 'vegetable'))AND b.id NOT IN (SELECT id FROM bookmark b, tagmap bt, tag t                 WHERE b.id = bt.bookmark_id AND bt.tag_id = t.tag_id AND t.name = 'apple')GROUP BY b.idHAVING COUNT( b.id )=2

posted by Rhomboid at 12:24 AM on March 23, 2006

Might have to use "b.id" instead of just "id" in the SELECT in the subquery.
posted by Rhomboid at 12:26 AM on March 23, 2006

Yeah, this looks like it would work. I'll test it and then will add this to the article. Thanks for helping out!
posted by philipp.keller at 12:36 AM on March 23, 2006

i thought that article was awesome for someone like me that wasnt shat out of the womb knowing all there is to know about database schemas, thats for the help folks.

what i had drawn up previously was closer to the mysqlicious deal but i can really see the sexiness in the 3 tables. performance isnt a huge issue but i'd rather tackle it now.
posted by yeahyeahyeahwhoo at 5:51 AM on March 23, 2006