如何从数据库验证开始日期和结束日期，以便它不会与时间冲突

问题描述：

Currently, I had some data in my database which was

I had do some action to take out the class_time. Which was later became

        $Starttimein=$_POST['startTime'];
        $Endtimein=$_POST['endTime'];

        $varstart=preg_split('/[\s:\s]+/', $Starttimein);
        $totvarstart=$varstart[0]*100+ $varstart[1];

        $varend=preg_split('/[\s:\s]+/', $Endtimein);
        $totvarend=$varend[0]*100+ $varend[1];

        $sql8="select * from class where sup_id='$idselect' ";
        $result8=mysqli_query($con,$sql8);

        while($row8=mysqli_fetch_assoc($result8))
        {
          $preclasstime=$row8['class_time'];


          $keywords = preg_split('/[\s,\s:\s-\s:\s]+/', $preclasstime);
          $day = $keywords[0];
          $tottimestart = $keywords[1]*100 + $keywords[2];
          $tottimeend = $keywords[3]*100 + $keywords[4];

          if($day==$_POST['csday'])
          {

            if((($totvarstart>= $tottimestart) && ($totvarstart<= $tottimeend)) || (($totvarend>= $tottimestart) && ($totvarend<= $tottimeend)))
            {
                  ?>
                <script type="text/javascript">
                    alert("Time crash");
                </script>
            <?php
            }

            else
            {
              echo "correct";
            }


          }
        }

The "day,starting and ending time" was separate using preg_split function. And I wish to validate if user select the same day, it will compare the input starting and ending time whether the input time crash with the time which store in database. For example: if user select Mon, 10.30-11.30, it will display a alert message say that cannot add class time as it is crash with previous time. I had tried using the if((($totvarstart>= $tottimestart) && ($totvarstart<= $tottimeend)) || (($totvarend>= $tottimestart) && ($totvarend<= $tottimeend))) to validate it, but cannot work.

答

Don't save these three values "day", "start time" and "end time" in one column, which you have to split later anyway. Keep them separated in three columns "dayofweek", "starttime" and "endtime". Use the TIME type for the latter so you can run comparison operators on them.

There are 6 ways how a time span can be arrange in relation to another time span. You can check this in the database to get only the rows which are in conflict with your input values. The checks should look like this:

[...]
WHERE
    dayOfWeek = :dayOfWeek AND
    (
        (:starttime < starttime AND starttime < :endtime) OR
        (:starttime < endtime AND endtime < :endtime) OR
        (starttime < :startime AND :endtime < endtime)
    )

Note: :starttime and :endtime are the bound variables from your prepared statement. Depending on your API you might need to use ? instead.

This way you don't need to read the whole database just to check if they are overlapping or not.

如何从数据库验证开始日期和结束日期，以便它不会与时间冲突

相关推荐