Making a tag cloud with MySQL and PHP

I'm making a tag cloud. I have a table called tags with a row 'id' and row 'tag'.

Each row 'tag' will be a tag just like on this site - imagine a tag called 'foo' for this example.

Each row 'id' will be the id's of the webpages that are tagged something - 'foo', in this example.

So any arbitary row may look like:

foo | 3 6 16 39 43 58 38 12 55

How do I make a function that will check the entire table for existence of $tag, and if it exists - add a space and $id to the row 'id' for that tag. If the tag doesn't exi开发者_如何学JAVAst in my table, then add the tag, and add the id.

The code I have (half-PHP, half-pseudo) is as follows:

if($tag doesnt exist in table)
{
     mysql_query("INSERT INTO tags (tag, id) VALUES ('$tag', '$id'");
}
if($tag exists in table)
{
     mysql_query("...append somehow... $id . " " WHERE tag = '$tag'");
}

I just don't know what to put in the conditionals or how to append in SQL how I want. How can I do this?

There is a better way to achieve this functionality. Typically to implement a tagging system, you would have two tables, tags and item_tags (or some other name that makes sense in your system.) the tags table would contain the tag id and the tag name and the item_tags table would contain the tag id and the item_it (whatever item you are tagging's id). There will be 1 row per tag per item. So if an item has 3 tags there will be 3 rows in the item_tags table. This will simplify your application and likely reduce load on the database server.

If however, you need to do it the way you currently are, you would want to select the tags for the current item first, on every request. If there aren't any rows in the result set, you create a new row. Otherwise, you get the value from the row that is returned, concatenate the new tag id and then do an update. This method will result in at least two database calls per request, instead of one like the solution I proposed above.

hth

Why not just have multiple rows in the tags table? One per webpage that has the tag present?

Retrieve pages for a given tag:

SELECT id FROM tags WHERE tag = `my-tag`;

Adding a new one:

INSERT INTO tags (tag, id) VALUES ('my-tag', newWebpageID);

Removal of a webpage:

DELETE FROM tags WHERE id = webpageID;

Going with one table you can use a composite primary key or make a unique index with the ID and Tag.

So your results would be like: 1, "first tag", 1, "second tag", 1, "another tag". If you use the INSERT IGNORE it will silently ignore dupes. So one row for each tag per site ID.

You need three tables to achieve this tidily (i.e. with no duplication of data or abuse of fields):

Content (the things people write that have tags) Tags (a list of tags) ContentTags (a list of the tags as applied to content)

An example might be an article "abc" with the tags "foo" and "bar".

Content would have a row with a ContentID (1, for the example), and the Content (abc). Tags would have two rows: each with a TagID (1, 2 respectively) and a TagText (foo, bar) ContentTags would also have two rows with a ContentTagID (1, 2), a ContentID (1, 1) and a TagID (1, 2).

This way you can have loads of different tags (stored in the Tags table) and link them to articles (with entries in ContentTags).

To insert a new tag you would add it to the Tags table, and then add an entry into ContentTags linking the tag you just added to the article you just added.

To check if a tag exists for a new article, run a query on Tags (e.g. "SELECT TagID FROM Tags WHERE TagText=$tagText"). If you get a result, the tag exists, and you just need to add an entry into ContentTags with the TagID you've just retrieved and the ArticleID of the article you've just added.

Hope this is of some help,