Richard H. Nilsson, May 14, 2005
Abstract
It is frequently desireable to lock database records, effectively "checking" them out to a user for updating via HTML forms in a web application. This is called "consensual" locking; i.e., it is up to the editor application to respect the lock by first checking it before presenting the record in a form for editing. The commonly-used open source database server, MySQL, does not provide record-level locking, so a work-around must be devised using the API. The method described uses the PHP API in concert with on-page JavaScript features of newer browsers. Requires knowledge of, and availability of PHP/MySQL services on your host ISP's server.
The problem
When a user selects a record to edit, the page locks the record by setting a certain field ("Locked" in our example), which is normally a default "0", to something non-zero such as the authorized user's id, for example. When editing is complete, the on-page PHP code updates the record, resetting the lock to "0" in the process. Detecting the "0" allows another user to operate on the record.
Ordinarily, if the user closes the form window with a record "locked", it will stay locked, requiring an administrator to use database maintenance tools to directly reset the lock.
New Browsers to the Rescue
As an alternative to cluttering pages with warnings, and depending on users to follow instructions, the method given below makes use of the "onbeforeunload()" JavaScript event trap, originally invented for MSIE, and recently added to Mozilla/Firefox (vers. 1.7+).
The method detects an attempt to spuriously "leave" the page (other than by form control), and with judicious use of variables, either "emergency unlocks" the "checked-out" record, or ignores the unlock if the editor page has exited normally.
I use the variables "id" for the record ID, "tbl" as a container for the record's table name, and Submit and Cancel buttons in my forms. In its present form, this method only deals with one table and record at a time.
Javascript Detects Imminent Window Closure
First, place the following JavaScript/PHP detection of the "leaving" event in the script area of the <head> of the editor page. Notice that PHP can merge with JavaScript and is used to capture the id and tbl variables which are generated by PHP later in the page when populating the editor form. You can do this by virtue of the fact that PHP executes on the server before the browser sees the page:
<script type="text/javascript">
:
var id = "<?php echo $id;?>";
var tbl = "<?php echo $tbl;?>";
onUl = function()
{
if(id!="" && tbl!="")
{
// optional alert
alert("Please use CANCEL or SUBMIT next time.\nEmergency unlocking..."+id+" in "+tbl);
var url = "unlock.php?id="+id+"&tbl="+tbl;
window.location = url;
}
}
window.onbeforeunload = onUl;
:
</script>
Now, farther into the page we should already know the ID of the locked record retrieved for editing, and have its id in the PHP variable "$id". Now we also need the record's table name in "$tbl" so we set the variable just above the populated form. DO NOT do it as a hidden form field, because the next block of code will not be able to evacuate it - which is necessary to prevent unlocking during proper exits:
<?php
:
$tbl = "TableName";
:
?>
And, at the bottom of the form, use this code in your "Submit" and "Cancel" buttons to prevent triggering "unlock.php" if the form is exiting normally by submission or cancellation:
<form ......
:
<input type="hidden" name="why" value="edit" />
<input type="hidden" name="id" value="<?php echo $id; ?>" />
<input name="submit" type="submit" value=" Submit " onclick="javascript:tbl='';" />
<input name="cancel" type="submit" value=" Cancel " onclick="javascript:tbl='';" />
</form>
:
NOTE: In my case I trap the "Cancel" button just before the code responsible for updating the record, and call a record unlock function (see below as part of the unlock.php file) to release the record. Here is that code, for your reference:
<?php
if($_SESSION['WHO'] == "staff"
and !isset($edelete)
and isset($why)
and $why =="edit")
{
$id = $_POST["id"];
// detect if submitted by Cancel button
if($cancel != "")
{
// unlock and abort.
if(!unlock("TableName",$id))
{
echo "Could not unlock record ".$id.
"<br />Please contact site admin to free the record.<br />\n";
} else {
echo "Edit of record ".$id." cancelled.<br />\n";
}
?>
<form name="getout" action="#">
<input name="back" type="button" value=" OK "
onclick="if(window.history.length>2){window.history.go(-2);}
else {window.close();}" />
</form>
<?php
exit;
}
?>
File "Unlock" Function
Place include statements in the top of the page to bring in the following utility scripts, such as:
<php
include 'db.inc';
include 'error.inc';
include 'unlock.php';
?>
The 'unlock.php' utility script file contains this:
<?php
// script to unlock table records if browser window
// is inadvertantly closed.
// Set up to access the database
include '../inc/db.inc';
include '../inc/error.inc';
if (!($db = @ mysql_connect($hostName, $userName, $passWord)))
showError();
if (!( @ mysql_select_db($databaseName,$db)))
showError();
if(isset($id) and isset($tbl))
{
// attempt to unlock record if info is present
unlock($tbl,$id);
}
// function to unlock a record
function unlock($table,$id)
{ // lock this record while we work on it
$ulock = "UPDATE $table SET Locked='0' WHERE Id='$id'";
if (!($result = mysql_query($ulock)))
{
showError();
return false;
}
return true;
}
?>
The 'db.inc' file included above looks something like this...:
<?php
$hostName = "localhost";
$databaseName = "yourdatabase";
$userName = "username";
$passWord = "password";
?>
... and the 'error.inc' contains this:
<?php
function showError()
{
die("Error " . mysql_errno() . " : " . mysql_error());
}
?>
|