Another way is to keep another unique value inside the table to be updated
and remember it.
When needed, I add a second unique column to the table (in my case a
char(64)) which is filled with the current timestamp and some md5 checksum.
I select this value before the update, pass it along with the HTML form,
and, before updating, I re-select the row to be updated and compare the
keys. If the comparision fails, the user is presented a warning message,
else I do the update (the user's data and a new generated stamp-value) with
the primary key _and_ the original stamp in the where clause. Then I check
if my new stamp made it to the table, or present another warning.

A sample:

Table atable:
id     int not null primary key
stamp  char(64) not null unique
avalue int

select id,stamp,avalue from atable where id=1   (select data for update)
build html form with "id" and "stamp" as hidden values

select stamp from atable where id=<the_id>
if stamp(form) != stamp(db)
  error (e.g. start from beginning)
  construct new_stamp
  update atable set avalue=<new>,stamp=<new_stamp> where id=<the_id> AND
  select stamp from atable where id=<the_id>
  if stamp(db) != <new_stamp>

It's a bit of work, but it had never let me down.


  Is what I use in the WHERE clause to update data
> -----Ursprüngliche Nachricht-----
> Von: Doug Semig [mailto:[EMAIL PROTECTED]]
> Gesendet: Mittwoch, 18. April 2001 20:48
> Betreff: Re: [PHP-DB] Concurrent update to database (PostgreSQL or
> MySQL) ??
> As others have mentioned, this is a tricky thing to do.
> What others haven't mentioned is a way to do it.
> Let's say you just showed the update form to two users.  It 
> contains the
> fields that they are allowed to update and the values in 
> those fields had
> been retreived from the database.
> Both users see a form like this:
> Today's High Temperature
> ------------------------
> City:                 Detroit
> Temp (degrees F):     47
>    ( Submit )
> The city field is not available for update (presumably the editor/user
> selected it off of a previous menu), and their task is to 
> update today's
> high temperature for Detroit.  One of the editors has 
> information that the
> high temperature reached 49 degrees F, and the other editor 
> has information
> that the high temperature has reached 50.  (Perhaps one of 
> the editors is
> overworked and hadn't gotten to enter the new high, or the 
> temperature is
> changing very quickly.)
> So one of the people enter 50 and the other enters 49.  Both 
> hit submit.
> What needs to be done right at that moment to protect it from 
> changing the
> value to 50 degrees (which is the correct, most recent, data) and then
> immediately changing the value to 49 degrees (which is now 
> out of date)?
> Basically, you have to pass the original value to the script 
> that is the
> ACTION of the HTML form.  This way, you have the original 
> value that may
> have been updated and the new value.
> The first thing the HTML form's ACTION script has to do is 
> get the record
> from the database again.  (Use SELECT ... FOR UPDATE if 
> available, so the
> RDBMS might lock the row.)  If the value you get from the 
> SELECT is the
> same as the original value, go ahead and run the UPDATE to 
> change the value
> to the new value.  If not, then generate and display a screen 
> telling the
> editor that the value has been updated by someone 
> can show the
> value and ask if they want to proceed with the update, but 
> that's all up to
> you and/or your interface designers.
> This is all similar to how folks design old fashioned screen-oriented
> database systems (like with CICS).
> Good luck,
> Doug
> At 01:47 PM 4/18/01 +0200, Nicolas Guilhot wrote:
> >Hi,
> >
> >How can I avoid two or more users to update the same record 
> at the same time
> >?
> >
> >Ideally, I would like to lock the record when a user open it for
> >modification. So if another user try to open the same record 
> he'll be warned
> >or get record values as read only. Is this possible and how 
> to do it with
> >PHP ?? How can I know that the user that has locked the 
> record has finished
> >with it, if he never commits his changes ? Is there an FAQ 
> about this ?
> >
> >Regards,
> >
> >Nicolas
> >
> -- 
> PHP Database Mailing List (
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
> To contact the list administrators, e-mail: 

PHP Database Mailing List (
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Reply via email to