Bdtjtk

I am right now working on a system that pulls records from a table in a database, that displays and manipulates this info in php. I want to lock certain records from being changed, if that specific record is in use.

So in this table; I have a 'Lock' field. Whenever I have a record that i want to edit, I want to display the information in a form, and once that is done, I want to update the Lock field to "in Use". When another user tries to access that file to edit it, the php file i am running to populate form, should first check if the Lock field is filled. They are then redirected and given a message if the Lock field is indeed filled.

At the moment, I am having some difficulty with 1: Updating and setting the Lock field, and 2: making sure that any user (either in another browser, or different computer) is denied access to the record.

For the moment this is my code:

<?php

require 'connection information.php';

$conn = mysqli_connection($server, $username, $password);

$FileID = 123;

$checkLock = "SELECT RecordLock FROM `dataTable` WHERE FileID = '".$FileID."'";

$LockResult = mysqli_query($conn, $checkLock);



if(!empty(mysqli_fetch_array($LockResult)){ // the Lock is anything but empty the user will be shown this message

 echo "this record is in use. try again later.";

}

else{// The lock is empty, they can edit the record

 $getDatabaseInfo = "SELECT * FROM dataTable WHERE FileID = '".$FileID."'";

 $databaseResults = mysqli_query($conn,$getDatabaseInfo);

 while($mysqli_fetch_array($databaseResults)){

 // this is where I auto populate/generate a form with all the information

 }

$SetLock = "UPDATE dataTable SET RecordLock='*User's ID*' WHERE FileID ='".$FileID."'";

mysqli_query($conn,$SetLock);

}

?>

Right now, I cannot manage to make the $SetLock query to pass, and truly edit the Lock field to "In Use".
Am I doing this right, is there a more elegant way of doing this?

Edit 1: added $conn, to Mysqli_query($SetLock);.
Edit 2: changed "Lock" queries to RecordLock.

The biggest problem I see a the moment is you are not tracking what user the table is locked for.

Chances are things will happen over multiple requests

• show record (1 request)


• save record (1 request)

This is the important because how state works for the client > server relationship.

Probably what's happening is you set that record to be locked, and now when you view it, it does exactly what you told it to do which is to echo this out:

 echo "this record is in use. try again later.";

The thing you are missing is this:

$checkLock = "SELECT Lock FROM dataTable WHERE FileID = '".$FileID."' AND user_id != $currentUserId";

How you get there I don't know, as I don't know the schema of your DB and you must add a user field in to track that data.

In any case you need this information when the user returns to save the edit, then you can exclude them from the query results and the table will "appear" unlocked for them (so they can edit it).

I don't see any other way you could do it without knowing that.

Just looking at the code, you are missing the $conn variable from your last call to the mysqli_query function.

There are more elegant solutions to locking records as explained by other people, so it may be worth looking in to them.