How can I get sql to check multiple conditions before an update?

Question

I am working on a scheduling project using php, some javascript, and sql server 2008. A user can use the job form to update a scheduled job. The fields that can be altered are: the job number, the start time, the end time, the description, and the status of the job. The sql need to test for all of these things before an update.

1. Some of the problems I am having are:
   1. If the user increased the the end time (to a later time), it allows for an overlap in the scheduling
   2. If the user moves the start time back (to an earlier time), it allows for an overlap
   3. if the user picks a schedule date from the past, it gives a scheduling conflict with items scheduled in the future.

I have been stuck on this for two days and have went in multiple directions to solve these problems. This is what I currently have:

 <?php 
include_once("../php/functions.php");
    $conn = connect();
    $params = array
        (
            $jobNum         =   $_POST['jobNum'],
            $asset          =   $_POST['drp_asset'],
            $jobStatus      =   $_POST['drp_status'],
            $sDate          =   $_POST['startTime'],
            $eDate          =   $_POST['endTime'],
            $department     =   $_POST['drp_department'],
            $descrip        =   $_POST['txtarea_description'],
            $job            =   $_POST['jobid'],
            $asset2         =   $_POST['drp_asset']
        );
/********************************************
Check to see if the delete button was pressed
And if the pre-check warning was confirmed 
delete the record.
********************************************/
if (isset($_POST['updateDelete']))
{
    $tsql = "Delete from Calendar_view
            where JobID = '$job'";

    $stmt = sqlsrv_query($conn, $tsql);
    if ($stmt)
    {
        checkRows($stmt);
        returnTo($conn);
    }
    else
    {
        errMsg();
    }
}
/****************************************
If the times and/or asset have not changed 
update the job status
****************************************/
else
{
    $tsql_check ="select StartTime, EndTime, Asset
                    from  Calendar_View
                    where '$sDate' = StartTime and '$eDate' = EndTime and '$asset' = Asset";
    $stmt = sqlsrv_query($conn, $tsql_check);
    $rows = sqlsrv_has_rows($stmt);
    if($stmt)
    {
        if ($rows === true)
        {
            updateJobStatus($conn, $params);
        }

        else
        {
            timeChanges($conn, $params);
        }
    }
}

function checkOverlaps($conn, $params)
{
    $sDate = $params[3];
    //$sDate = new DateTime($params[4]);
    //$sDate = date_format($sDate,'Y-m-d G:i:s');
    $eDate = $params[4];
    //$eDate = new DateTime($params[5]);
    //$eDate = date_format($eDate,'Y-m-d G:i:s');
    $asset = $params[1];

    $tsql_check ="select StartTime, EndTime
                    from  Calendar_View
                    where (('$sDate' < EndTime and '$sDate' >= StartTime) or ('$eDate' < EndTime and '$eDate' > StartTime)) and '$asset' = Asset";
    $stmt = sqlsrv_query($conn, $tsql_check);

    if ($stmt)
    {
        // If there is a match, there will be an overlap
        $rows = sqlsrv_has_rows($stmt);
        if ($rows === true)
        {
            checkRows($stmt);
        }
        //If there is no match then job is being moved
        //to an open spot
        else
        {
            updateJob($conn, $params);
        }
    }
}
/************************************
If the start time or end time have changed
/***********************************/
function timeChanges($conn, $params)
{
    $sDate = $params[3];
    $eDate = $params[4];
    $asset = $params[1];

    $tsql_timeCheck ="select StartTime, EndTime
                    from  Calendar_View
                    where (('$sDate' <= StartTime) or ('$eDate' <= EndTime)) and '$asset' = Asset"; 
    $stmt2 = sqlsrv_query($conn, $tsql_timeCheck);

    if ($stmt2 == true)
    {
        $rows = sqlsrv_has_rows($stmt2);
        if ($rows === true)
        {
            updateJobStatus($conn, $params);
            //updateJobStatus($conn, $params);
        }
        else
        {
            checkOverlaps($conn, $params);
        }
    }
}
function updateJob($conn, $params)
{
    $tsql = "UPDATE Calendar_View
                SET     JobNum = ?,
                        Asset = ?,
                        JobStatus =?,
                        StartTime =?,
                        EndTime =?,
                        Department = ?,
                        Description = ?
                WHERE   JobID = ?";

    $stmt = sqlsrv_query($conn, $tsql, $params);
    if ($stmt)
    {
        checkRows($stmt);
        returnTo($conn);
    }
        else
    {
        errMsg();
    }
}
/***************************************
Update job status
****************************************/
function updateJobStatus($conn, $params)
{
    $tsql = "UPDATE Calendar_View
                SET     JobNum = ?,
                        Asset = ?,
                        JobStatus =?,
                        StartTime =?,
                        EndTime =?,
                        Department = ?,
                        Description = ?
                WHERE   JobID = ? and Asset = ?";

    $stmt = sqlsrv_query($conn, $tsql, $params);
    if ($stmt)
    {
        checkRows($stmt);
        returnTo($conn);
    }
        else
    {
        errMsg();
    }
}
/**********************************
Return user to scheduling page they
were working on.
***********************************/
function returnTo($conn)
{
    sqlsrv_close($conn);
    echo "<meta http-equiv='refresh' content='1;URL=../pages/schedule2.php' />";
}
function errMsg()
{
    die( print_r(sqlsrv_errors(), true));
    exit;
}
/*************************************
Check if any rows were altered by the 
query. If so alert success, else alert
that there was a conflict (nothing done)
**************************************/
function checkRows($stmt)
{
    $rows_affected = sqlsrv_rows_affected($stmt);
    if ($rows_affected > 0)
    {
    echo "Job updated successfully!<br>";
    }
    else
    echo "There was a scheduling conflict.";
    echo "<meta http-equiv='refresh' content='2;URL=../pages/schedule2.php' />";
}
?>

Any and all help would be greatly appreciated!

Answer 1

The following solution works to check for all overlaps in scheduling.

<?php 
include_once("../php/functions.php");
    $conn = connect();

    $controlDate1   =   date_format(new DateTime($_POST['pickedDate']),'Y-m-d H:i:s');
    $controlDate2   =   date_format(new DateTime($_POST['pickedDate']),'Y-m-d 23:59:00');
    $jobNum         =   $_POST['jobNum'];
    $asset          =   $_POST['drp_asset'];
    $jobStatus      =   $_POST['drp_status'];
    $sDate          =   date_format(new DateTime($_POST['startTime']),'Y-m-d H:i:s');
    $eDate          =   date_format(new DateTime($_POST['endTime']),'Y-m-d H:i:s');
    $descrip        =   $_POST['txtarea_description'];
    $job            =   $_POST['jobid'];
/*****************************************************
used for testing purposes
******************************************************
$checkVals = array(
            $controlDate1   =   $controlDate1,
            $controlDate2   =   $controlDate2,
            $jobNum         =   $_POST['jobNum'],
            $asset          =   $_POST['drp_asset'],
            $jobStatus      =   $_POST['drp_status'],
            $sDate          =   $sDate,
            $eDate          =   $eDate,
            $descrip        =   $_POST['txtarea_description'],
            $job            =   $_POST['jobid'],
            );
/***************************************************/
/********************************************
Check to see if the delete button was pressed
And if the pre-check warning was confirmed 
delete the record.
********************************************/
if (isset($_POST['updateDelete']))
{
    $tsql = "Delete from Calendar_view
            where JobID = '$job'";

    $stmt = sqlsrv_query($conn, $tsql);
    if ($stmt)
    {
        checkRows($stmt);
        returnTo($conn);
    }
    else
    {
        errMsg();
    }
}
/****************************************
If the times and/or asset have not changed 
update the job status, description
****************************************/
else
{
    $tsql_check ="SELECT JobNum, StartTime, EndTime, Description, Asset, JobStatus, JobID
                    from  Calendar_View
                    where JobID = '$job' and JobNum = '$jobNum' and StartTime = '$sDate' and EndTime = '$eDate' and Asset = '$asset'";
    $stmt = sqlsrv_query($conn, $tsql_check);
    $rows = sqlsrv_has_rows($stmt);
    if($stmt)
    {
        if ($rows === true)
        {
            $params = array($jobStatus,$descrip, $job, $asset);
            updateJobStatus($conn, $params);
        }

        else
        {
            $tsql_check ="SELECT JobNum, StartTime, EndTime, Description, Asset, JobStatus, JobID
                            from  Calendar_View
                            where (JobID != '$job' and 
                            (StartTime < '$controlDate2' and StartTime > '$controlDate1') and 
                            (('$sDate' < EndTime and '$sDate' >= StartTime) or ('$eDate' < EndTime and '$eDate' > StartTime) or ('$sDate' < StartTime and '$eDate' > EndTime)))
                            and Asset = '$asset'";
            $stmt = sqlsrv_query($conn, $tsql_check);
            $rows = sqlsrv_has_rows($stmt);
            if($stmt)
            {
                if ($rows === true)
                {
                    checkRows($stmt);
                }
                else
                {
                    //pass values to updateJob()
                    $params = array($jobNum,
                                    $asset,
                                    $jobStatus,
                                    $sDate,
                                    $eDate,
                                    $descrip,
                                    $job);
                    updateJob($conn, $params);
                }
            }
        }
    }
}
function updateJob($conn, $params)
{
    $tsql = "UPDATE Calendar_View
                SET     JobNum = ?,
                        Asset = ?,
                        JobStatus =?,
                        StartTime =?,
                        EndTime =?,
                        Description = ?
                WHERE   JobID = ?";

    $stmt = sqlsrv_query($conn, $tsql, $params);
    if ($stmt)
    {
        checkRows($stmt);
        returnTo($conn);
    }
        else
    {
        errMsg();
    }
}
/***************************************
Update job status
****************************************/
function updateJobStatus($conn, $params)
{
    $tsql = "UPDATE Calendar_View
                SET     JobStatus =?,
                        Description = ?
                WHERE   JobID = ? and Asset = ?";

    $stmt = sqlsrv_query($conn, $tsql, $params);
    if ($stmt)
    {
        checkRows($stmt);
        returnTo($conn);
    }
        else
    {
        errMsg();
    }
}
/**********************************
Return user to scheduling page they
were working on.
***********************************/
function returnTo($conn)
{
    sqlsrv_close($conn);
    echo "<meta http-equiv='refresh' content='1;URL=../pages/schedule2.php' />";
}
function errMsg()
{
    die( print_r(sqlsrv_errors(), true));
    exit;
}
/*************************************
Check if any rows were altered by the 
query. If so alert success, else alert
that there was a conflict (nothing done)
**************************************/
function checkRows($stmt)
{
    $rows_affected = sqlsrv_rows_affected($stmt);
    if ($rows_affected > 0)
    {
        echo "Job updated successfully!<br>";
    }
    else
    {
        echo "There was a scheduling conflict.";
    }
    echo "<meta http-equiv='refresh' content='2;URL=../pages/schedule2.php' />";
}
?>

Answer 2

I see a lot of queries to check if everything is alright... You could also use this approach:

• First create a mapping array like following:

  Array( 'key1' => 'val1',
     'key2' => 'val2',
     'key3' => 'val3',
     'key4' => 'val4');
  

• Make sure you put your post variables in this structure

• Make sure you perform a query for all the fields in the database that correspond to the mapping and put them into a new instance of the same structure

• Use array_diff_assoc to compare the difference

• If there's something different in the timings, perform some logic (probably querying the database) to see if the dates match

If you need help implementing this idea, I could help you, but try something for yourself first ;)

Answer 3

Until you find a more efficient solution, you could make the change in a transaction, then query the database to see if any of your conditions fail. If they do, you can just roll back.

I suppose to be really careful, you ought to query to see the number of failures before and after your update, then rollback if it fails. That way you can be sure your change is the change introducing the error.