Check if a period of time is included between two dates inserted in the form-CodePudding

Good morning Everyone, It's been a week I am looking for an answer and I cannot find it through the web. I don't think this question has been asked.

I am looking for a php request or mysql request to check if the date I entered in my FORM is available regarding the period of date already in my database

   | ID | ID_period | date_start |  date_end  |
   | 1  |   1       | 2022-01-31 | 2022-02-04 |
   | 2  |   2       | 2022-09-01 | 2022-10-30 |
   | 3  |   3       | 2022-11-01 | 2022-12-28 |
   | 4  |   4       | 2022-11-01 | 2022-03-31 |

As you can see here, I am on holiday through January 31th to February 4.

I would like to create a condition in php who says, ok, I cannot take vacation through february 1th to february 4th because I am already on vacation through january 31 to february 4.

This condition will check between two dates regarding what I inserted on my form. This verification checking will be placed on my MYSQL TABLE.

This condition will help me to stop the form to be sent because there is a conflict between the dates I inserted.

If I take some holidays thought the 6 of february to the 9, it will go through because I don't have dates between what I inserted.

I tried so many ways to do but I cannot find any solution. -

I started with this but I don't think I am on the right track

SELECT startDate, endDate FROM YourTable  WHERE '2012-10-25' between startDate and endDate

Please, someone can help me with this ? Thank you so so much

<?php
$resultdate1 = $enregverif9["start"];
$resultdate2= $enregverif9["end"];
$date_1 =date('d-m-Y', strtotime($date_1));
//echo $paymentDate; // echos today!
$holiday_start = date('d-m-Y', strtotime("$resultdate1"));
$holiday_End = date('d-m-Y', strtotime("$resultdate2"));
if (($date_1 >= $holiday_start) && ($date_2 <= $holiday_end)){
    echo "the dates are  between";
    exit();
}else{
    exit();
}
}
?>

CodePudding user response：

I have used an script like this in PDO. But the logic is the same. My logic is to verify all the existing schedules and compare with your new added schedule

$count=0;
$query = "SELECT * FROM events ORDER BY id";
$statement = $connect->prepare($query);
$statement->execute();
$result = $statement->fetchAll();
foreach($result as $row)
{ if (strtotime($row['start_event']) >= strtotime( $_POST['end']) OR strtotime($row['end_event'])<= strtotime( $_POST['start'])) {
}else {  $count=1;}
}

if ($count==0) {

 $query = "
 INSERT INTO events
 (title, start_event, end_event)
 VALUES (:title, :start_event, :end_event)
 ";
 $statement = $connect->prepare($query);
 $statement->execute(
  array(
   ':title'  => $_POST['title'],
   ':start_event' => $_POST['start'],
   ':end_event' => $_POST['end']
  )
 );
 echo "Added successfully";

} else {echo "Room occupied";     }

CodePudding user response：

First thing, BETWEEN operator does not work like that, it expects column name just after WHERE instead of direct value. SQL BETWEEN OPERATOR

If the result of below query is empty then we can assume that there's no holidays in between.

SELECT * FROM YourTable WHERE ID = 1 AND (date_end<={{FORM_INPUT_START_DATE}} AND date_start>={{FORM_INPUT_END_DATE}}) OR (date_start<={{FORM_INPUT_START_DATE}} AND date_end>={{FORM_INPUT_END_DATE}})