Are you guys interested in yet another solution? It simular but I think
it's even easier if that's an incentive...
-In your table do put a datetime field, I'll call it lastMod. This will be
the "last updated" date and time (you need time here)
-In your form have this as a hidden field so it gets submitted with the rest
of the data.
-When you receive the data to update the record you have I presume the
record ID in $id and the last modified date timein $lastMod. Create a new
$now with today's date and the current time
Your SQL will look like:
UPDATE Foo
SET col1 = $col1,
col2 = $col2,
col3 = $col3,
lastMod = $now
WHERE recordID = $id
AND lastMod = $lastMod
As you can see the will succeed only if lastMod has not been changed. And
if it had been updated it will not fail (it's perfectly valid SQL) but will
not update any records.
-Now to check if you did update the record...
SELECT lastMod
FROM Foo
WHERE recordID = $id
And compare this to your $now value.
Or in MS SQL you can check the @@rowcount variable - (that's a MS SQL value)
it will be 1 - success or 0 - collision (if it's > 1 you've got duplicate
ID's).
This has some disadvantage in that your end used could go through all the
effort to update a record only to have their update fail - I suggest putting
a nice error message and refresh the values in the form with the current
ones.
It has the advantage of avoiding locking, setting flags, rolling back, you
only need to compare one field and more work for you - and it will always
work.
Good Luck,
Frank
On 2/1/02 12:42 PM, "[EMAIL PROTECTED]"
<[EMAIL PROTECTED]> wrote:
> From: "Peter Lovatt" <[EMAIL PROTECTED]>
> Date: Fri, 1 Feb 2002 08:09:56 -0000
> To: <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>
> Subject: RE: [PHP-DB] Ensuring users don't overwrite each other (NOT a newbie
> question)
>
>
> Hi
>
> Interesting problem! Systems would be so much easier to build if we didn't
> have to allow for users :)
>
> Two suggestions, depending on how you want the data dealt with.
>
> A table of rows in use, with a time stamp and an owner. When user1 opens the
> record, stamp it with owner and time. If user2 wants to use the record,
> check when it was 'locked' and apply a timeout based on how long it takes to
> edit. For example if the record was opened 3 mins ago, and the timeout is 5,
> the user2 gets a message saying 'Record in use try again in 2 minutes' If it
> was opened 6 minutes ago set the owner of the locked record to user2, and
> reset the timestamp.
>
> If / when user1 submits, refuse the update, and inform user1, and whatever
> handing you need after that.
>
> If no user2 has tried to open the record, then user1 can still submit,
> because they still own it, even if there is a timeout.
>
> If you are feeling flash maybe a JavaScript timer that pops up 1 minute
> before timeout and warns user1 to save (update record and reload for more
> editing)?
>
> Probably more hassle than its worth, but you could also take a snapshot of
> the data, when user1 starts, and if more than one user tries to edit the
> record, save the updates in a temp table, compare the updated record with
> the original snapshot, and do some sort of intelligent amalgamation.
>
> HTH
>
> Peter
>
>
>> -----Original Message-----
>> From: Oliver Cronk [mailto:[EMAIL PROTECTED]]
>> Sent: 31 January 2002 23:09
>> To: [EMAIL PROTECTED]
>> Subject: [PHP-DB] Ensuring users don't overwrite each other (NOT a
>> newbie question)
>>
>>
>>
>> Hi there, currently writing an e-CRM system for an intranet using PHP on
>> Win32 and MS-SQL. This system needs to be scalable but more importantly
>> there will be anything up to 400 users (unlikely, but the max
>> amount) using
>> the same records (updating information about customers etc) and I
>> worry that
>> whilst one user has a form open (via one of my PHP scripts) that another
>> user could also be making changes to the same record and if they post it
>> before the other one they could overwite each others changes. For info:
>> database is normalised to 3NF so that side of things should be okay.
>>
>> I have thought of a couple of solutions:
>>
>> Row Locking when a user has a record - and if another user wants
>> to use that
>> record PHP tells them its in use. But if the forst user doesn't make any
>> changes how will the db know to unlock the row and there might be
>> potential
>> deadlock issues. Also I'm not sure of the SQL for row locking
>> (do you use a
>> SELECT with a ROWLOCK hint?).
>>
>> Another idea was to have a log or temp table - that would get written into
>> when ever some opens a record but this has the same issues as the first
>> solution I think.
>>
>> An another idea is T-SQL and transactions but I'm not sure if that will
>> solve the problem (and I've never used T-SQL before - therefore
>> I'm not sure
>> of its capabilities)
>> eg:
>> When the script is started by the first user (to bring up the existing
>> record) perhaps a transaction is started (if they can persist between
>> batches?):
>>
>> $tranname = "@tran".$id;
>> $sqlstr = "TRANSACTION $tranname
>>
>> SELECT rows from CASES
>> WHERE id = $id
>> GO
>>
>> /* maybe find the date / time from a system table sp_something of the last
>> time the row was modified?? */
>>
>> START TRANSACTION $tranname
>> GO
>> ";
>>
>> But that probably won't work thinking about it (and looking at the stupid
>> senseless code I have written above!!!!) The transcation probably
>> need to be
>> around the update SQL doesn't it? And then do a rollback if it finds
>> another user has updated lately? And then reload the data and
>> send it back
>> to the form for the user to check (then they can update - after
>> checking the
>> other users data?)
>>
>> Anybody have a solution /views on this? Anybody had to fix a similar
>> problem? Or is all this paranoia (will the DB handle this problem on it
>> own? - I very much doubt that last comment!)
>>
>> Any help would be most appreciated, I don't need all of the PHP code just
>> the concepts will do (I have been using PHP/MS-SQL for a while) or some
>> example T-SQL if you think thats the solution I should go for.
>>
>> Thanks very much in advance...
>>
>> Oliver Cronk
>>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
