how to prevent overwriting of data for rows within a given range (StartTime/EndTime)

rama1200

I have one database table with all varchar and serveral columns; the most important being Monday_TimeStamp, Tuesday_TimeStamp...Friday_TimeStamp and StartTime(00:00:00....23:30:00) and EndTime(00:00:00....23:30:00).

I use a form to input some info to the database with something like this:

if ($submit && $StartTime <= $EndTime) {

if($Monday && !$monTimeStamp){
$sql = "UPDATE ScheduleRoom_New SET
Monday_TimeStamp='$TimeStamp',Monday_Room='$Room',Monday_Class='$Class',Monday_Section='$Section',
Monday_Teacher='$Teacher',Monday_Comments='$Comments' WHERE StartTime BETWEEN '$StartTime' AND '$EndTime'";
$result = mysql_query($sql);
echo "Monday added successfully to the database with confirmation number
$TimeStamp. ";}//end Monday if

...and like this for every day of the week...

it is supposed to search in the database for existing TimeStamps according to the chosen days and times and either complain if field is not free or update if field is available.

idialy, the script is supposed to prevent any updating if any of the fields within range is not empty; however, if for example a user had previously updated monday from 00:00:00 to 03:00:00 and i by mistake (or not) decide to update monday from 00:00:00 to 04:00:00, i'm able to overwrite his/her previous record.

how to prevent this from happening? i have looked into arrays, loops, etc. but have not been able to find a plausible solution.

Any ideas???? thanks.

bastien

first turn those columns to proper date-time columns, there are enough math function for date-time to make varchars irrelevant and more difficult to use than needed.

one solution I like to use is to write a function that is called whenever some enters the site. this function runs a db query and sets all varaibles after a certain timestamp to NULL and therefore is easily updatable.

So you could null out all the timestamps that are to be affected and simple change only those records that have a null timestamp in the date-time field