* Ron McKeever > I thought update is for existing records and insert is to add new records?
That is correct. > My Scenario is if two users open an existing record with data > already in the fields. They then add some information to the > end of a field with data. They "add information", you say? As in "add new records"...? No, they don't add new records, and that may be your problem/confusion. > Now when they click update the last user/browser gets written to the db, > where the first users data is over written. You say they "click update", I assume that means they click a button labeled "update", and this makes your program/script execute an UPDATE statement against the database? Not an INSERT statement? > I thought innodb would help this.. No, it won't. > user a: user b: > pulls up record 5 pulls up record 5 > adds/appends line to field b adds/appends line to field b > clicks update first clicks update second > > user b's line is added/appended to record 5, user a's line is overwritten > not even seen. Yes, what would you expect? You could have seen user a's line if you checked before user b pushed 'update', right? The problem is that the user b update "does not know" that user a did an UPDATE _between_ the SELECT statement and the UPDATE statement of user b. As a result the update from user a is overwritten, as you have observed. You describe the update as "adds/appends line" to a field. It's still an update to a single field, as seen from the database. Take a look at your program/script, the relevant SQL probably says something like UPDATE ... SET field_b="$userinput", ... where $userinput comes from the multiline field. No append here... just assigning the user input to the field_b column. Now what you _could_ have is something like UPDATE ... SET field_b=CONCAT(field_b,"$userinput"), ... This would _append_ the user input to the existing content of the column, but it will probably not give you the result you are looking for. It may work if the user input is only the new lines, without the old. There are many possible ways around this problem: 1. Ignore it. Depending on the application, this may happen very seldom (or very often!), you may be in a position where you can ignore the few cases where user input is lost. 2. Avoid the problem by notifying a user that the record is beeing updated by a different user, preventing two users to edit the same record at the same time. May or may not be applicable in your case. 3. Use a checksum or a hidden copy of the original data and re-read the record to verify that the record is not changed while it was edited by the user. If it has, you can either reject the last change with a message, or if you feel brave, merge the changes of user b into the allready saved changes of user a. If the change is allways 'adding lines' this may be easy. 4. Normalize. Put the multi-line field in a separate table, for instance called 'textlines', with one line (varchar, hopefully there are no lines more than 255 characters...) for each row. Multiple users can append lines at the same time. In general, I would prefer the first or the last solution. :) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
