开发标记系统 - 如何使用新标记更新当前标记？

Question

I am designing a Tagging system for my site(a customized blog). These are my source codes

<b>Please enter your tags, separating each one with a comma.<b><br>
<input type="text" name="tags" size=50>

$taginput = $_POST["tags"];
$tagarray = explode(",",$taginput);
for($i=0;$i<count($tagarray);$i++){
$usetag = mysql_real_escape_string(stripslashes(ltrim(rtrim($tagarray[$i]))));
if($usetag == "") continue;
$query = "INSERT INTO tags (item_id,tag) VALUES ($itemid,'$usetag')";
mysql_query($query);
}

CREATE TABLE `tags` (
`uid` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`item_id` INT( 11 ) NOT NULL,
`tag` VARCHAR( 50 ) NOT NULL
) ENGINE = MYISAM ;

Where item_id is the blog post id and tag is the tag name

Now Im looking for a solution on how if I update a post with tags.. how can i delete the present tags and replace it with the new tags? and how can i delete some specific present tags and replacing it with new ones?

Like this,

Tags: shirt, apparel, arrivals replaced by Tags: t-shirts, old, sale

and

Tags: shirt, apparel, arrivals to Tags: shirt, old, arrivals

I also want to prevent overpopulating the tags tables with duplicate tag names if possible. Please help..

Answer 1

To prevent duplicate tags you need another table for the many to many relationship

create table `items_tags` (
    `item_id` int(10) unsigned not null default 0,
    `tag_id` int(10) unsigned not null default 0,
    primary key (`post_id`, `tag_id`),
)

create table `tags` (
    `id` int(10) unsigned not null auto_increment,
    `tag` varchar(100) not null default '',
    primary key (`id`),
    unique key (`tag`)
)

ps - an unsigned int is only 10 characters in length, not 11.

I'm not gonna type out all the code for you but when the form is submitted you "INSERT IGNORE" on the tags table, select the id's where tag IN( ), delete from items_tags where item_id = and finally insert into items_tags a record for each tag id with the current item id.

I suggest you start learning pdo. It make things much easier. You should also have foreign key constraints with cascade on delete so when an item or tag is deleted it's references in the items_tags table are also deleted.

To retrieve all tags for a post is simple:

select i.*, t.* from items as i left join items_tags as it on (it.item_id = i.id) left join tags as t on (it.tag_id = t.id) where i.id = <current item id>

Hope that helps.