如果日期是在日期之间，请检查mysql

Question

i'm learning php at the moment and i wanted to make a car hire system to challenge myself a bit.

Now i am stuck with a validation question, i got 5 diffrent cars and want to check if the car is available at the dates the user submitted.

my database table got a startdate, enddate, car and a id

This is what i'm trying to get working:

if($_POST){


$date1 = $_POST["startdate"];
$date2 = $_POST["enddate"];
$car= $_POST["car"];

$qry1 = "SELECT * FROM hire WHERE startdate between '{$date1}' and '{$date2}'
UNION
SELECT * FROM hire WHERE enddate between '{$date1}' and '{$date2}'";

$qry2 = "SELECT * from hire WHERE car = '{$car}'";

$results = mysql_query($qry1, $bd);
$results2 = mysql_query($qry2, $bd);
$row  = mysql_num_rows($results);
$row2  = mysql_num_rows($results2);

if ($row && $row2 > 1 ){
   die("it twerks");
}

else {
  //do stuff\
}
}

now it says its always available.

EDIT

start and enddate are DATE fields format is Y-m-d.

Answer 1

Hey Sorry for the late response. I have done something similar but for a bed and breakfast, Room reservation system.

Please kindly start by normalizing your database properly.

you can have three tables such as Car, Hire, or Person. your car table, can have all the description of your vehicle. Person table can have all the description of the person hiring the car. your hire table will reference the car_id and person_id. It will also have you start_date and end_date. So like this.

• car table.

car_id (primary key), car_color (text), Car_make(text)

**

• Person table

** person_id (primary key) person_name (text) person_telephone (text)

Hire table Hire_Id(auto_increment) person_id (foreign key) car_id (foreign key) start_date (date) end_date (date)

$Query="SELECT r.* FROM car r WHERE r.car_id NOT IN ( select b.car_id from hire b where NOT (b.end_date< '2014-08-15' or b.start_date>'2014-08-12')) order by r.car_id;"

This return all available cars