Okay so im quiet new to coding and im trying to do a car rental system and im stuck at the main page which has the search button which is a simple date picker , on the other side i have a database which contains current reservations made from other clients on X cars with X start date and X end date. Now here's the tricky part . I collected the search Start Date & Return date from inputed fields and also collected the Start & end date from Reservations database, i compared them both to check if inputed dates are bigger / smaller or equal to the Reservations one so i skip it because the client is looking for a car in the inputed search dates and if the dates are equal or inbetween we skip this car.

Now , lets say i think i have correctly checked the dates , on the reservations table i save the cars ID which is selected car and what i want to do is to show the details of available cars on the inputed dates , now the thing is that Reservations and Cars are 2 different tables also i dont know how to compare or check if selected car is on cars list and is it taken on all dates inputed or is it free . this is the part that im currently stuck .

Overall what i want to do if i wasnt clear enought.

Search Start / Return date - Show cars that are available on inputed dates - if they are not , dont show.

Here's My Code

<!--Car Cover-->
<?php 
  if(isset($_POST['submit']))
  {
    $startDate = $_POST['pickupDate'];
    $returnDate = $_POST['returnDate'];

    $startDate = new DateTime($startDate);
    $returnDate = new DateTime($returnDate);
    $startDate = $startDate->format("m-d");
    $returnDate = $returnDate->format("m-d");
    if(empty($startDate))
    {
      $_SESSION['ErrorMessage'] = "Pickup Date can't be empty";
      TakeTo("index");
    }
    elseif(empty($returnDate))
    {
      $_SESSION['ErrorMessage'] = "Return Date can't be empty";
      TakeTo("index");
    }
    elseif($startDate == $returnDate)
    {
      $_SESSION['ErrorMessage'] = "Cars can only be rented for 1 or more days";
      TakeTo("index");
    }
    elseif($returnDate <= $startDate)
    {
      $_SESSION['ErrorMessage'] = "Return date cannot be before Pickup Date";
      TakeTo("index");
    }
    echo "Search Start Date : $startDate - Search End Date : $returnDate <br>";
    $sql = "SELECT * FROM reservations";
    $stmt = $con->query($sql);

    $sql2 = "SELECT * FROM cars";
    $stmt2 = $con->query($sql2);
    while($info = $stmt->fetch())
    {
      $Start = new DateTime($info['start_date']);
      $End = new DateTime($info['end_date']);
      $Start = $Start->format("m-d");
      $End = $End->format("m-d");
      $selectedCar = $info['selected_car'];
      while($carInfo = $stmt2->fetch())
      {
      
      if($carID == $selectedCar)
      {
        continue;
      }
        $carID = $carInfo['id'];
      $carName = $carInfo['car_name'];
      $product_year = $carInfo['production_year'];
      $engine = $carInfo['engine'];
     // print( "Start is :$Start - End is :$End - StartDate is :$startDate - Return Date is :$returnDate<br>");

      echo "Car Name : $carName - product_year : $product_year - engine : $engine  - Start Date : $Start - End : $End  - Car ID : $carID<br>";
      }
      if($Start == $startDate || $End == $returnDate ||$startDate >= $Start && $startDate <= $End || $startDate <= $Start && $startDate <= $End)
      {
        continue;
      }
      
      
    }

  }
 ?>

CodePudding user response：

Assuming a car can only be picked up on the day after its return, you want:

select c.id,c.car_name,c.production_year,c.engine
from cars c
left join reservations r on
    r.selected_car=c.id and
    greatest(r.start_date,?) <= least(r.end_date,?)
where r.selected_car is null

(passing your desired start date and end date, as YYYY-MM-DD).

Note that you cannot detect overlap of two time ranges just by comparing end and start separately; you will miss cases where one range is entirely within the other.

The left join looks for reservations for each car that would make it unavailable; the where condition excludes cars where there is such a reservation.