Security by editing in the same record

John,
This schema requires two additional fields in the database table to implement record locking.

IsLocked - Yes/No field, LockTime - Datetime field, UserField - username.

Timeout is set to 20 minutes.
Use Edit page: Before display event on the Events tab to lock record.

Here is a sample:

global $strTableName,$strWhereClause,$conn;

To unlock the record use Before record updated event:

global $conn;

John,

Hi Jane,
I added the two fields.

The third one (UserField) is 'forumnaam', it's the one that contains the (current) user.
But when I log in and try to edit a record it says: "Fatal error: Call to undefined function customquery() in C:\blahblah\_leden_events.php on line 37"
The database field is called _leden.

Can you tell me what I need to change in you're sample?
regards,
John

Hi,
if you use PHPRunner 4.2 use db_query function instead of CustomQuery.

$rs = db_query("select IsLocked, LockTime, UserField from ".$strTableName." where ".$strWhereClause,$conn);

Hi Jane,

I tried your suggestion and it is chaging the value of IsLocked as well as updating the username in my table however it is not locking it?

I have replaced CURDATE() with NOW() in your below suggestion but couldn't figure out why everytime a different user edits the record the table gets updated but never goes to the lock part of the IF statement.

[codebox]//lock record here

$str = "update ".$strTableName." set IsLocked=1, LockTime=CURDATE(), UserField='".$_SESSION["UserID"]."' where ".$strWhereClause;

db_exec($str,$conn);[/codebox]
I would have expected that at least the record does not get updated whilst edited by another user but it did.

Anyone else got this to work correctly?

No, exactly the same here.
It doesn't lock and it does not change he status afterwards either.

For some stupid reason I cant upload the files to the demoserver and now I am stuck!
If anyone can help it would make my day a whole lot better.
regards,

John

HI Jane,

Don't we need to add the Username to the below test

if ($datatmp["IsLocked"]==1 && (strtotime("now")-strtotime($datatmp["LockTime"]))/60<20)
as we would want to deny updates to all users but the one currently editing the record.

Would we need to add something like && UserField != $_SESSION["UserID"]

For some reason I don't get the following message displayed even if I get it to deny updating when the record is being edited

echo "This record is in use by ".$datatmp["UserName"];
May be a test in the event before record updated is needed?
I am unclear as to when the code for before record updated and before display take effect?

many thanks

I finally uploaded the whole lot to the demo account.

So now it's time to just wait till Jane fix this problem.
I am sure she can and will.
regards,

John

I finally uploaded the whole lot to the demo account.

Please make sure you post the final solution, I would see this as being very useful for everyone if thats ok?

The last things I heard from Jane where these remarks:

you've forgot to add return true; to your "Before record updated" event:

-----------------------

global $conn;

$strUpdate = "update TableName set IsLocked=0, LockTime=now() where ".$where; db_exec($strUpdate,$conn); return true;

John,

But also this didn't help.
If a user edits a record you see the name of the user in the USERFIELD and the time in the LOCKTIME field. But there is no 0 or 1 in the ISLOCKED field.

Also no text that the record is in use.

Thereby would it be useful to also erase the time and user after the record is saved.

I tried a few things for a few hours but didn't get this to work.

I still think that the test should include && $datatmp["UserName"]==$_SESSION["UserID"]

If the record is locked, only the user locking it should be able to unlock it (set IsLocked to 0)

I got it to change the IsLocked value from 0 to 1 each time it is edited by a different user but it didn't stop other users from editing the record and for some reason the echo message never displayed even when teh record was not editable.
I am not sure we are using the right event as I wouldn't want to let the user edit the record then when trying to commit finds out the record is actually locked. It's better to NOT allow editing the record while it is locked by someone else. How can this be done?
Is there a way to pop up a message when the inline edit or edit button is selected?
I am sure Jane would be able to help (guess all the PHPR team is busy getting the latest beta release out)
Thanks oin advance Jane

Ok, I got it so far that it says: "The record is in use by .. (the user)".
But after return or update it is stil in use.

When I manually change IsLocked to '0' (unlocked) the text disappears but when I save the record the listpage still has it on locked!!

It does change the name of the user in UserField but after 'save' does not change the IsLocked field to '0'
Example:

The problem is probably in the code below.

[codebox]//Before record updated

//reset unlocked

global $conn;

$strUpdate = "update _kandidaten set IsLocked=0, LockTime=NOW() where ".$where;db_exec($strUpdate,$conn);return true;

db_exec($strUpdate,$conn);[/codebox]
Plus that the file is not really locked in the sense that no one else can change it while in use by another.
So there are to problems to fix:

• really lock the record when in use by another.
  
• change the IsLocked status back to '0' when leaving the record.
  I really hope Jane or Sergey can fix it.
  John

Jane asked me step by step what I want and what didn't work.
This is what I answered:
*Here is what I want:

• when a user edits a record it must be locked so taht others cannot edit it
  
  at the same time.
  
• a text, or rather a popup, must say who is editting the record at that
  
  time. So the other knows who to call by phone for example.
  
  After the popup I would like the screen return to the list after some
  
  seconds. This way it is not possible to edit the record when in use by
  
  another.
  
• after return to the list, the record must be unlocked and the time set
  
  to zero.
  What does not work:
  
• The record is not really locked in the sense that no one else can edit the
  
  record. When the record is in use you will get the text but tou can just
  
  edit and save the record!!
  
• After the edit the record is still in Locked mode. When you are the next
  
  one to edit the record it still says it is in use, and the text appears,
  
  but it is NOT in use!*
  John

spot on John! I am sure Jane will have a clever solution as usual.

if I may add:

when user1 is editing the record, a timeout should start to tick after which the records should be editable (IsLocked set to 0) to avoid locking a record forever if user1 edits the record then leaves without committing or cancelling his action.
Would be great to share the solution in the forum please.

Hi Hich,
And she did (like always).
I just post the codes as I use it ok?
You need to ad three fields to table you want to lock.

• Islocked (this is a yes/no field. I used TINYINT)
  
• LockTime (this is a DATETIME field)
  
• UserField (this is VARCHAR text field)
  [codebox]//Before record updated
  //reset unlocked
  
  global $conn;
  
  $strUpdate = "update _kandidaten set IsLocked=0, LockTime=NOW() where ".$where;db_exec($strUpdate,$conn);return true;
  
  db_exec($strUpdate,$conn);[/codebox]
  [codebox]// After record update
  //** Redirect to list page after 'save' ****
  
  header("Location: _kandidaten_list.php?a=return");
  
  exit();[/codebox]
  [codebox]// Edit page: Before display
  //** Lock record ****
  global $strTableName,$strWhereClause,$conn;
  
  $rs = db_query("select IsLocked, LockTime, UserField from ".$strTableName." where ".$strWhereClause,$conn);
  
  $datatmp = db_fetch_array($rs);
  
  if ($datatmp["IsLocked"]==1 && (strtotime("NOW")-strtotime($datatmp["LockTime"]))/60<20)
  
  {
  
  ?><script>
  
  alert("Deze kandidaat wordt momenteel bewerkt door <?php echo $datatmp["UserField"]?>");window.location.href='_kandidaten_list.php?a=return';
  
  </script><?php
  
  }
  
  else
  
  {
  //lock record here
  
  $str = "update ".$strTableName." set IsLocked=1, LockTime=NOW(), UserField='".$_SESSION["UserID"]."' where ".$strWhereClause;
  
  db_exec($str,$conn);
  
  }[/codebox]
  The only thing you have to change in the code is the TableName. The name of mine is '_kandidaten', the rest you can use as it is.
  A popup tells you you cannot edit the record because it is in use. So you cannot even enter the record!!
  
  I am a happy man.
  
  Some day I will ask Jane to marry me. But I am a bit afraid of Sergey :-)
  Thanks Jane for the code and the help, you're great.
  John

Cheers John,

Did you get the pop up message working fine? It doesn't work for me and doesn't display anything?

Can you please check if there is something missing around the message pop up pls?

Also when the record is locked and I try to update the record, the inline edit button disappears until I refresh the entire page again? do you get this behaviour as well?

I wish the record does not open for inline edit at all while it is locked.
PS: You can't have Jane all for yourself mate, bet you would get in trouble with many others apart from Sergey <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=38045&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />

She is great and such a helpful sweat heart no doubt!

Hi Hich,
I copied the code directly from my Eventpage so there is nothing to add or change.
I don't use the inline edit and therefore I didn't encounter your problem, sorry mate really would like to help.
Only problem I now have is the agenda (calendar). Probably did something wrong here.
John

Ok, now the record is locked, and I get the warning message.
But the problem is that if the user that locked the record doesn't save it (just click on the 'back to list' button), the record remains locked forever.
It should release the record when the user leaves that screen.

Hi Hich,

tried this too and didn't work, have been trying to debug..any thoughts? no popup message although the table for the IsLocked, LockTime & UserField seem to be updating correctly...hmm

Isildo,
record is available just after 20 minutes of locking time.
nix386,
Please publish your project on Demo Account and open a ticket at http://support.xlinesoft.com sending a URL to your pages along with instructions on reproducing this error.

I encountered the same problem with going back to the list while NOT saving.
Is it not possible to lock the record for as long it is in use and after leaving the edit field (saved or not) just unlock it for every one to use?
John
P.S. for the time being I just took the 'back to list' button away <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=38119&image=1&table=forumreplies' class='bbcemoticon' alt='<<' />

I encountered the same problem with going back to the list while NOT saving.