Help with logic

I have a number of objects. They can be sorted into groups. When a user "drags" an object into a group, I INSERT a record into my db

INSERT INTO t1
  (group_id, item_id, project_id, user_id) 
VALUES 
  ($groupID, $itemID, $projectID, $userID)

There can be multiple objects in the same group and those objects can be moved from one group to another. When I move the objects into another group, I don't need to create another record, but rather only update group_id.

EDIT:

I think I need to do something like this afterall:

table structure
t1.id
t1.group_id
t1.item_id
t1.project_id
t1.user_id

if ("SELECT id, group_id FROM t1 
   WHERE item_id = $itemID
   AND project_id = $projectID
   AND user_id = $userID")) {

   // if found 
   UPDATE t1 
   SET group_id = $groupID
   WHERE id = $ID

} else {

   INSER开发者_JAVA技巧T INTO t1
   (group_id, item_id, project_id, user_id) 
   VALUES ($groupID, $itemID, $projectID, $userID)

It sounds like u should place a if statement when moving from a group to another group to update only the object being move. Say for instance if I am selecting an object from its initial(beginning) space I would add the insert statement. If I am moving an object that has already been grouped to another group I would add an update statement. Finally if I am taking an item out of all groups completely I would issue a delete statement that will remove the object from the table.

pseudo code

if object = new
{
INSERT INTO t1 (group_id, item_id, project_id, user_id) 
VALUES ($groupID, $itemID, $projectID, $userID)
}
if object = group
}
UPDATE t1 
SET group_id = $newgroupID
WHERE item_id = $itemID;
}
if object = banned
}
delete from t1 where item_id = $itemID
}

This sequence of code should be placed on each container that you are dragging your objects to so that it can check for the proper requirements.

You can do it in one query using insert ... on duplicate key update.

See http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html for details.

Assuming that item_id is a key, you can do

INSERT INTO t1
  (group_id, item_id, project_id, user_id) 
VALUES 
  ($groupID, $itemID, $projectID, $userID)
ON DUPLICATE KEY UPDATE group_id = $groupID

I suggest you to add a surrogate primary key so you can you can identify uniquely every row. Then you can make an update like this:

UPDATE t1 SET group_id = $newgroupid WHERE id = $objectid;