CODEWITHSUNDEEP
phpmysql
OscarCy
OscarCy

Reputation: 55

Check if Vehicle is available in PHP

I have this table:

BookingID userID VehicleId FromDate   ToDate     Comments TotalPrice Status 
9         14     1015      2021-08-03 2021-08-08          258        0      
10        14     1015      2021-08-10 2021-08-12          129        0     
11        14     1015      2021-08-13 2021-08-14          129        0      
12        14     1015      2021-08-18 2021-08-23          129        0

And this PHP function:

function isBooked($VehicleID, $fromDate, $toDate) {
   $sql = "SELECT BookingID, VehicleID, FromDate, ToDate FROM tbl_bookings WHERE VehicleID = $VehicleID";

   $con = new Database_Connector();
   $result = $con->read_records($sql);
   $flag = 0;
   while ($row = mysqli_fetch_array($result) && $flag == 0)
   {
      $bFromDate = $row['FromDate'];
      $bToDate = $row['ToDate'];

      if (($fromDate >= $bFromDate) && ($toDate <= $bToDate)){
         $flag = 1;
      }
   }
      if ($flag==1) {
         echo "Not available";
      } else { echo "is Available"; }
}

What I need is on the given dates $fromDate and $toDate to check if they are records in the booking table on that specific date range.

Idealy given:

My code from what I can understand checks only the set of dates of each row. Hence I receive the wrong results. Anyone can propose a better solution? I'm open to totally different approaches.

UPDATE:

function isBooked($VehicleID, $fromDate, $toDate) {

    $sql= "SELECT CASE WHEN y.BookingID IS NOT NULL AND VehicleID=$VehicleID THEN '0' ELSE '1' END availability
           FROM (SELECT 1)x LEFT JOIN tbl_bookings y ON FromDate <= '$toDate' AND ToDate > '$fromDate'";
           echo $sql;
    $con = new Database_Connector();
    $result = $con->read_records($sql);
     while ($row = mysqli_fetch_array($result)){
         $variable = $row['availability'];
      }
    if ($variable == 0) {
      return 0;
    }elseif ($variable == 1) {
       return 1;
    }else {
      return "error";
    }
}

function addBooking ($VehicleID, $fromDate, $toDate, $message, $userID) {
   $status = 0;
   $days = (round ((strtotime($toDate) - strtotime($fromDate)) / (60 * 60 *24)))+1;
   $vPrice = getVehiclePrice($VehicleID);
   $TotalPrice = $vPrice * $days;
   $sql= "INSERT INTO tbl_bookings(BookingID, userID, VehicleId, FromDate, ToDate, Comments, TotalPrice, Status, PostingDate, LastUpdationDate) VALUES (DEFAULT, '$userID', '$VehicleID', '$fromDate', '$toDate', '$message', '$TotalPrice', '$status', DEFAULT, DEFAULT)";
    $con = new Database_Connector();
    $result = $con->execute_query($sql);
    if($result != FALSE){
        return "Booking submited successful";
    }else{
        return "Something went wrong. Please try again";
    }
}

function generateBookings($x) {
   $i=1;
   while ($i <= $x) {
      $days = mt_rand(1, 10);
      $daysText = '+ '. $days. ' days';
      $min_date = strtotime(date("Y-m-d"));
      $max_date = strtotime(date('2021-12-30'));
      $rand_start_date = date('Y-m-d', rand($min_date, $max_date));
      $rand_end_date = date('Y-m-d', strtotime($rand_start_date. $daysText));

      $rand_VehicleID = mt_rand(1001, 1020);
      $rand_UserID = mt_rand(17, 25);
      $message = "Lorem Ipsum Dollor";

      if (isBooked($rand_VehicleID, $rand_start_date, $rand_end_date) == 1 ) {
         addBooking ($rand_VehicleID, $rand_start_date, $rand_end_date, $message, $rand_UserID);
         $i = $i+1;
      }
   }
}

sqlfiddle: http://sqlfiddle.com/#!9/67c994/1/0

Upvotes: 1

Views: 545

Answers (2)

user16577935
user16577935

Reputation:

For instance:

DROP TABLE IF EXISTS OscarCy;

CREATE TABLE OscarCy
(BookingID INT AUTO_INCREMENT PRIMARY KEY
,FromDate  DATE NOT NULL
,ToDate DATE NOT NULL
);

INSERT INTO OscarCy VALUES
( 9,'2021-08-03','2021-08-08'),
(10,'2021-08-10','2021-08-12'),
(11,'2021-08-13','2021-08-14'),
(12,'2021-08-18','2021-08-23');

...

SET @range_start = '2021-08-15';
SET @range_end = '2021-08-29';


SELECT CASE WHEN y.bookingid IS NOT NULL THEN 'Not Available' ELSE 'Available' END availability
 FROM (SELECT 1)x
 LEFT 
 JOIN OscarCy y
   ON fromdate <= @range_end
  AND todate > @range_start;

+---------------+
| availability  |
+---------------+
| Not Available |
+---------------+

SET @range_start = '2021-08-15';
SET @range_end = '2021-08-17';

SELECT CASE WHEN y.bookingid IS NOT NULL THEN 'Not Available' ELSE 'Available' END availability
 FROM (SELECT 1)x
 LEFT 
 JOIN OscarCy y
   ON fromdate <= @range_end
  AND todate > @range_start;

+--------------+
| availability |
+--------------+
| Available    |
+--------------+

Note that the only other answer thus far provided appears to use an incorrect comparison for overlaps. I'd comment, but I don't have the rep.

EDIT: With reference to your comment, I don't understand it. Here's the subset of data for VehicleId 1020, and where 'x' represents bookings that overlap with the give date range...

+-----------+--------+-----------+------------+------------+--------------------+------------+------+
| BookingID | userID | VehicleId | FromDate   | ToDate     | Comments           | TotalPrice | x    |
+-----------+--------+-----------+------------+------------+--------------------+------------+------+
|       296 | 22     |      1020 | 2021-08-11 | 2021-08-12 | Lorem Ipsum Dollor | 3000       |    0 |
|       313 | 24     |      1020 | 2021-08-31 | 2021-09-08 | Lorem Ipsum Dollor | 13500      |    0 |
|       326 | 17     |      1020 | 2021-11-12 | 2021-11-22 | Lorem Ipsum Dollor | 16500      |    1 |
|       335 | 21     |      1020 | 2021-09-20 | 2021-09-29 | Lorem Ipsum Dollor | 15000      |    0 |
+-----------+--------+-----------+------------+------------+--------------------+------------+------+

I can't see that there are any other 'overlaps' here.

Upvotes: 1

Will Jones
Will Jones

Reputation: 2191

First of all, you have a potential SQL injection vulnerability in your code:

//THIS IS DANGEROUS

$sql = "SELECT BookingID, VehicleID, FromDate, ToDate FROM tbl_bookings WHERE VehicleID = $VehicleID";
$con = new Database_Connector();
$result = $con->read_records($sql);

//DO SOMETHING LIKE THIS INSTEAD

$sql = "SELECT BookingID, VehicleID, FromDate, ToDate FROM tbl_bookings WHERE VehicleID = :vehicleID";
$con = new Database_Connector();
$result = $con->read_records($sql, ['vehicleID' => $VehicleID]);

If $VehicleID ends up as something like 1; DROP TABLE tbl_bookings;--, you could find yourself having a really, really bad day.
You should be parameterising your queries to protect against these kinds of (unfortunately exceedingly common) attacks.

To answer your actual question, I'd probably offload the date checking to the database, using something like this:

function isBooked($VehicleID, $fromDate, $toDate) {
    $sql = "SELECT BookingID, VehicleID, FromDate, ToDate FROM tbl_bookings WHERE VehicleID = :VehicleID and (FromDate >= :FromDate and ToDate <= :ToDate";
    $con = new Database_Connector();
    $results = $con->read_records($sql, ['VehicleID' => $VehicleID, 'FromDate' => $fromDate, 'ToDate' => $toDate]);
    if (empty($results)) {
        echo "Available";
    } else {
        echo "Not Available";
    }
}

I'm unsure what type $con->read_records returns, so I don't know if an empty() check would work. You may have to use $results->rowCount() === 0 instead.

Upvotes: 0

Related Questions