Page 1 of 1

PHP/MYSQL - lock record for 5 minutes

Posted: Mon Dec 05, 2011 1:51 pm
by cjkeane
Hi everyone,

I have a script that runs to check if a user has a record open. If he does, their name, date and time are inserted into the db. If another user opens the same record, a message displays that the record is in use and the lock will be released in 5 minutes or less. The message displays correctly that the record is in use, however the user can still save to the same record. Does anyone have any ideas on how to resolve this issue? Thanks.

Code: Select all

<?php
	$id= $_GET['id'];

	$result = mysql_query("SELECT LockedDateTime, LockedBy FROM records WHERE id= '$id'");
	$found = mysql_num_rows($result);
	$rowfound = mysql_fetch_array( $result );
	$LockedByUser = $_SESSION['SESS_LAST_NAME'] . ", " . $_SESSION['SESS_FIRST_NAME'];

	$LockedBy = $rowfound['LockedBy'];
	$LockedDateTime = $rowfound['LockedDateTime'];
	//$LastUpdated = $rowfound['LastUpdated'];
	if ($LockedDateTime < date("Y-m-d H:i:s")) {
		mysql_query("UPDATE records SET LockedBy='$LockedByUser', LockedDateTime=now()+300 WHERE id= '$id'") or die(mysql_error()); 	
	} elseif ($LockedDateTime == 'IS NULL' || $LockedDateTime == '0000-00-00 00:00:00') { 
	//	echo " (Locked status: " . $LockedStatus . " in use by " . $LockedBy . ")"; 	
		$LockedByUser = $_SESSION['SESS_LAST_NAME'] . ", " . $_SESSION['SESS_FIRST_NAME'];
		mysql_query("UPDATE records SET LockedBy='$LockedByUser', LockedDateTime=now()+300 WHERE id= '$id'") or die(mysql_error()); 	

	}
	$resultStatus = mysql_query("SELECT Status FROM records WHERE id='$id'");
	$data = mysql_fetch_assoc($resultStatus);
  	//echo $data['Status'];
	if (!empty($LockedBy) && $LockedBy != $LockedByUser) {
			echo "<div style='color:red;' align='left'>This record is locked and in use by " . $LockedBy . ". The lock will expire in less than 5 mins. <br />Please refresh the page to display the updated data before making your changes. </div><br />";
	}

//the above code runs first. when the record is saved via a button, the below code runs

$result = mysql_query("SELECT LastUpdated, LastUpdatedBy, LockedDateTime, LockedBy FROM records WHERE id= '$id'");
	$found = mysql_num_rows($result);
	$rowfound = mysql_fetch_array( $result );
	
	$LockedBy = $rowfound['LockedBy'];
	$LockedDateTime = $rowfound['LockedDateTime'];
	$LockTimeRemaining = time() - $LockedDateTime;
	$LockedByUser = $_SESSION['SESS_LAST_NAME'] . ", " . $_SESSION['SESS_FIRST_NAME'];
	//$LastUpdated = $rowfound['LastUpdated'];
	if ($LockedDateTime != '0000-00-00 00:00:00' && $LockedBy != $LockedByUser) { 
	//	echo " (Locked status: " . $LockedStatus . " in use by " . $LockedBy . ")"; 	
		echo "<div style='color:red;' align='left'>This record is still in locked status by " . $LockedBy . ". The lock will expire shortly.</div><br />";
	} else {
		mysql_query("UPDATE records SET LockedBy='$LockedByUser', LockedDateTime=NOW()+300 WHERE id= '$id'") or die(mysql_error()); 	
		mysql_query("UPDATE records SET LastUpdated=NOW(), LastUpdatedBy='$LockedByUser' WHERE id= '$id'") or die(mysql_error()); 	

// update records table query runs after the above runs.
}

?>

Re: PHP/MYSQL - lock record for 5 minutes

Posted: Tue Dec 06, 2011 9:58 am
by maxx99
You have 2 messages informing that record is in use.

Is this if statement also correct?

Code: Select all

  if ($LockedDateTime != '0000-00-00 00:00:00' && $LockedBy != $LockedByUser) {
Or maybe this one displays the message?

Code: Select all

if (!empty($LockedBy) && $LockedBy != $LockedByUser) {

Re: PHP/MYSQL - lock record for 5 minutes

Posted: Tue Dec 06, 2011 10:06 am
by cjkeane
Thanks. I know I have 2 information messages. 1 displays immediately, the other displays upon saving. I figured it out last night. I had to re-write the code and omit parts of it to make it work.