0

i have a very simple SQL which driving me crazy. I am working with Car Rental - so there is a FromDate and a ToDate (booked cars)

My MySQL loos like this:

id | FromDate | ToDate
-----------------------
24 | 01.10.2020 | 31.10.2020

so if a user choose a Date in between this, he should get a message that the car is already booked. What i have tried so far:

SELECT id FROM booked_cars WHERE ($fromdate BETWEEN date(FromDate) AND date(ToDate)) AND vhId = $vhid
SELECT id FROM booked_cars WHERE ($fromdate BETWEEN date(FromDate) AND date(ToDate) AND $todate BETWEEN date(FromDate) AND date(ToDate)) AND vhId = $vhid
SELECT id FROM booked_cars WHERE (date(FromDate) BETWEEN $fromdate AND $todate) AND vhId = $vhid

my $fromdate, $todate and $vhid comes with a POST-Form. The Values are

$fromdate = 03.10.2020
$todate = 07.10.2020
$vhid = 24

no matter what i try, my $statement->rowCount() is alway zero.

i am using PDO with prepared Statements. i just wrote it without for better reading.

$statement = $pdo->prepare("SELECT id FROM booked_cars WHERE (:fromdate BETWEEN date(FromDate) AND date(ToDate)) AND vhId = :vhid");
$statement->execute(array('fromdate' => $fromdate, 'vhid' => $vhid));
echo $statement->rowCount(); // Alway 0 with BETWEEN (when i select just the vhid, i got the result)
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Mirko Babic
  • 15
  • 3
  • 3
    Does this answer your question? [How do I query between two dates using MySQL?](https://stackoverflow.com/questions/3822648/how-do-i-query-between-two-dates-using-mysql) – James McClelland Oct 03 '20 at 14:02
  • no it doesn't. because my second Date isn't before the first @james – Mirko Babic Oct 03 '20 at 14:37
  • What format have FromDate & ToDate fields? Is they datetime? – Slava Rozhnev Oct 03 '20 at 15:57
  • Try the next one: ```$statement = $pdo->prepare("SELECT `id` FROM `booked_cars` WHERE `id` = {$vhid} AND ('{$fromdate}' BETWEEN `FromDate` AND '{$todate}' OR `FromDate` BETWEEN '{$fromdate}' AND '{$todate}')")``` – SpinyMan Oct 05 '20 at 12:48

1 Answers1

0

You need to use STR_TO_DATE here, most probably dates kept in database have a YYYY-mm-dd format.

$statement = $pdo->prepare("SELECT `id` FROM `booked_cars` WHERE `vhId` = :vhid AND STR_TO_DATE(:fromdate,'%d.%m.%Y') BETWEEN `FromDate` AND `ToDate`;");

In case fields FromDate and ToDate type is DATETIME:

$statement = $pdo->prepare("SELECT `id` FROM `booked_cars` WHERE `vhId` = :vhid AND STR_TO_DATE(:fromdate,'%d.%m.%Y') BETWEEN DATE(`FromDate`) AND DATE(`ToDate`);");

The above query is similar to the one used in this DB Fiddle example.

  • thank you peter. this way i got a result. BUT i even got a result when i select a date which isn't at the range f.e. 01.11.2020 - 05.11.2020. any suggestions ? – Mirko Babic Oct 03 '20 at 14:34
  • Before trying to figure out how you should do it in PHP, write the query out manually in a MySQL console and get that working. Then you can figure out how to do it inside of PHP. In this case you are only providing the "fromDate" in your query, you also need to use the "toDate" – James McClelland Oct 03 '20 at 15:39