0

I have coded this today and wanted to find all the data inside my database which is start_time and end_time
also date which is very easy for me to validate but when it comes to time... its really hard

this is what I want to do.
1. check all the data
2. validate the time
2.1 when there is no time like the user entered insert into database
2.2 but where there is a time like that or in between earlier reserved time that should be an error

well i have this code here and its almost done i know im missing something here

<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title></title>
    </head>
    <body>
<?php 
mysql_connect("localhost","root","");
mysql_select_db("windsor_reservation");

if(isset($_POST['reserve'])){


   echo  $stime = ($_POST['stime']);
   echo  '----';
   echo  $etime = ($_POST['etime']);
   echo  '<br>';
 $date = date("Y-m-d");
  if($stime < $etime){
 $q = mysql_query("SELECT * FROM time WHERE date='$date' AND stime>='$stime' AND etime<='$etime'");
 $count =  mysql_num_rows($q);
      if($count > 0){
    echo 'Time already taken choose another time please';
  }else{
   $new_all = mysql_query("SELECT * FROM time where date='$date'");
        while($get_all = mysql_fetch_assoc($new_all)){
          echo   $g_all_stime = $get_all['stime'];  
          echo "==";
          echo   $g_all_etime = $get_all['etime'];
            echo "<br>";       
            if($stime == $g_all_stime && $etime == $g_all_etime){
                echo 'There is already a time like yours'; 
                echo "<br>";
            }
            elseif($stime <= $g_all_stime && $etime >= $g_all_etime){
                echo 'cannot insert the same Start and End time with server';
                echo "<br>";
            }
            elseif($stime >= $g_all_stime && $etime <= $g_all_etime){
                echo 'the time must not be between the earlier reserve time';
                echo "<br>";
            }
            elseif($etime >= $g_all_stime && $stime <= $g_all_etime){
                echo 'the end time must not be the same as the start time and the end time must not 
                be the same with the end time';
                echo "<br>";
            }
            else{
                echo 'no other choice insert';
                echo "<br>";
                //should insert here if no errors
                //mysql_query("INSERT INTO time(date,stime,etime)values('$date','$stime','$etime')");                  
            }

        }
  }
  } else{
 echo 'do not insert';   

 }

 }


?>
        <form method="post">


        start<input type="time" name="stime">
        end<input type="time" name="etime"><br>
        <input type="submit" name="reserve" value="reserve">
    </form>
</body>

</html>

Any help would be great. BTW cant use any other format than varchar when comparing the time

Attachments database.png 49.47 KB data_database.png 26.57 KB
2
Contributors
2
Replies
12
Views
3 Years
Discussion Span
Last Post by joshuajames.delacruz
1

You seem to be looking for collisions.
new_start and new_end times may fall inside, outside or either side of existing start and end times - so you may need to test for these cases:

WHERE date = '$date' 
  AND
    ($new_start BETWEEN stime AND etime 
    OR 
     $new_end BETWEEN stime AND etime 
    OR
     $new_start <= stime AND $new_end >= etime) 

I think!

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.