Delete table entry if not in current input

Question

I have a form where users can enter tags like tag1,tag2,tag3. I explode that information and insert them into a table:tags one by one. Each tag in the table has a unique id (for referencing), a vid_id (for matching to a video), and a name (ex. tag1).

I want the behavior that if someone has those 3 tags in the table and then re-submits the form with tag1,tag2 for the same vid_id that tag3 will be removed from the table. I was just thinking of a way to do this most efficiently and I was thinking of running a select statement each iteration or just deleting all the tags for that vid_id and reinserting tag1,tag2 but that seems inefficient. Anyone got any good ideas?

$variable=explode(',',$_POST['tags']);
foreach($variable as $tag) {
    $id=md5(uniqid());
    if ($tag!=''){
        $sql="INSERT into tags (id,vid_id,name) VALUES (?,?,?)";
        $stmt16 = $conn->prepare($sql);
        $result=$stmt16->execute(array($id,$vid_id,$tag));
    }
}

Can tags for each video only be entered by one user or anyone? Are there any foreign key relations on id and/or vid_id? Do you want to be able to see previously submitted tags or there should be only one current set of tags for all videos? — AR.
Also, how you're getting an id variable? It seems like it's not an auto-increment value, right? — AR.
For now tags can only be entered by one person. There are no foreign keys. I just used id so in case, tags could be referenced individually. I only want one current set of tags for all videos. id is achieved as seen above by using md5(uniqid()) which is basically just a mixed up timestamp so it is unique. — Scarface
I think probably deleting all and the inserting anew would be the way to go. Alternately you can show user current tags with a checkbox or button next to each so they can delete it. I think having a primary key on video id and tag makes more sense, than adding an artificial key. That way you can run REPLACE INTO loop and don't worry about duplicates. It won't remove old tags though. — AR.
Yeah I threw unique ids on them. So delete * eh? You don't think it would be worth going through the trouble of finding a way to only delete tag3 I take it? This won't affect table optimization will it? I mean in a sense of overhead. Thanks again AR. — Scarface

Alex Weinstein Alex Weinstein · Accepted Answer · 2011-06-28T21:39:44

I don't fully understand the question. Pieces that might help you: WHERE NOT IN SQL syntax:

DELETE FROM tags     
WHERE tags.name NOT IN ('tag1', 'tag2', 'tag3')

and REPLACE statement: http://dev.mysql.com/doc/refman/5.0/en/replace.html

Delete table entry if not in current input

1 Answers