I have a mySQL table of the form
entryID (PK), UserID, entryName
Each user (as defined by his userID) may create any number of entries in this table, but for each user, the entryName must be unique. I would like to allow users to modify all of their entryNames at once. As such, I will present the user with a form with multiple entryName fields which they can edit.
The trouble is when trying to commit this to the database. I can't just update row by row as the case:
1, 1, Entry1
2, 1, Entry2
becoming
1, 1, Entry2
2, 1, Entry3
would cause an error when trying to rename Entry1 to Entry2. Currently, I read all rows with the given UserID, then delete them, and recreate each row. This works. The trouble with that method, however, is that if a user manages to cause an error in my script, ALL of his entries get deleted... and lost. And this is a Bad Thing. How would one solve this?
This is a typical scenario for a TRANSACTION WITH ROLLBACK on EXCEPTION.
You can start here: http://dev.mysql.com/doc/refman/5.0/en/commit.html