NickName:Michel Ask DateTime:2014-05-16T05:06:27

Check mysql if date is between dates

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.

Copyright Notice：Content Author:「Michel」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/23688629/check-mysql-if-date-is-between-dates

Answers

Ethic Or Logics 2014-08-12T11:36:02

Hey Sorry for the late response. I have done something similar but for a bed and breakfast, Room reservation system.\n\nPlease kindly start by normalizing your database properly.\n\nyou can have three tables such as Car, Hire, or Person.\nyour car table, can have all the description of your vehicle. \nPerson table can have all the description of the person hiring the car. \nyour hire table will reference the car_id and person_id. It will also have you start_date and end_date.\nSo like this. \n\n\ncar table.\n\n\ncar_id (primary key),\ncar_color (text),\nCar_make(text)\n\n**\n\n\nPerson table\n\n\n**\nperson_id (primary key)\nperson_name (text)\nperson_telephone (text)\n\nHire table\nHire_Id(auto_increment)\nperson_id (foreign key)\ncar_id (foreign key)\nstart_date (date)\nend_date (date)\n\n$Query=\"SELECT r.*\nFROM car r\nWHERE r.car_id NOT IN (\nselect b.car_id \nfrom hire b\nwhere NOT (b.end_date< '2014-08-15' or b.start_date>'2014-08-12'))\norder by r.car_id;\"\n\nThis return all available cars",

Sammitch 2014-05-15T21:18:59

First you want to consolidate all of your query logic to find overlapping reservations into one query without unions. The below should return available cars for the specified date range.\n\nSELECT *\nFROM hire\nWHERE\n startdate NOT BETWEEN '{$date1}' AND '{$date2}' OR\n enddate NOT BETWEEN '{$date1}' AND '{$date2}' OR\n '{$date1}' NOT BETWEEN startdate AND enddate OR\n '{$date2}' NOT BETWEEN startdate AND enddate\n\n\nSecond, you cannot take shortcuts with logic statements like this:\n\nif ( $row && $row2 > 1 )\n\n\nIt must be written like:\n\nif ( $row > 1 && $row2 > 1 )\n\n\nThird, yadda yadda don't use mysql_*() functions. [See the pedantry in the comments on your question.]\n\nI recommend PDO. It supports more database systems than just mySQL, and MySQLi has some really strange syntax.",