0

I'm working on a script for my work to rent bicycles, but i dont know how to check the availability of bicycles for an X period

Database looks like: 

CustomerID    BicycleID    FromDATE    TillDATE     
    43           12       2012-05-20  2012-05-23
    43           14       2012-05-20  2012-05-23
    44           12       2012-05-27  2012-05-30
    44           15       2012-05-27  2012-05-30

Is there a script what i can run before i make a new contract or reservation to check or the bicycle is available for a period of time?

Any help on this would be great, thanks!

j08691
  • 204,283
  • 31
  • 260
  • 272
Wessel s
  • 1
  • 4
  • Fyi, it's `bicycle`, not `bycicle`. Fix that asap or people who will deal with your code later will hate you for having to be careful to make a typo on purpose. – ThiefMaster May 20 '12 at 14:41
  • possible duplicate of [Overlapping date range MySQL](http://stackoverflow.com/questions/3667668/overlapping-date-range-mysql) – ThiefMaster May 20 '12 at 14:50

3 Answers3

1

If these are simple fixed term rentals it's simple for search availability between From and Till

Select count(*) from hiresTable
where $From < TillDATE
and $Till > FromDATE

If the rental terms are not fixed, this won't be accurate because two hires might be side by side inside the search range.

Justin Levene
  • 1,630
  • 19
  • 17
0

See Determine Whether Two Date Ranges Overlap:

To test if two periods overlap, use (StartDate1 <= EndDate2) and (StartDate2 <= EndDate1)

SELECT * FROM reservations WHERE ? <= TillDATE AND FromDATE <= ?

The first parameter in this query should be the start date of the new reservation, the second one the end date.

This will return you all reservations that collide with the new one.

Community
  • 1
  • 1
ThiefMaster
  • 310,957
  • 84
  • 592
  • 636
  • Can i use `date` for that or should i use `timestamp` ? – Wessel s May 20 '12 at 15:04
  • Any type where `<=` works properly can be used. Use `DATE` if you just store dates, `DATETIME` if you also want to include a time. Never use `TIMESTAMP` as this will be automatically bumped to the current time whenever the row is updated. See http://dev.mysql.com/doc/refman/5.1/en/datetime.html – ThiefMaster May 20 '12 at 15:05
  • when i use this sql it returns an error: "SELECT * FROM `reservations` WHERE ? <= `2012-05-24` AND `2012-05-20` <= ?" what am i doing wrong? – Wessel s May 20 '12 at 15:11
  • You can only use `?` when using PDO prepared statements. – ThiefMaster May 20 '12 at 15:11
0

I think if you could store dates in TIMESTAMP (UNIX) format, then you could easily use > < = operators to compare desired rental period and non-availability of that bicycle.

John
  • 2,461
  • 5
  • 21
  • 18
  • Storing UNIX timestamps in a database that has a `DATE` type is a bad idea (even though it's extremely common in the PHP world since pretty much every PHP application uses unix timestamps internally). – ThiefMaster May 20 '12 at 14:49
  • @ThiefMaster~ thanks, it would be interesting to me if you could explain why is that a bad idea. :) – John May 20 '12 at 14:52
  • 1
    You cannot use all the [date functions](http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html) MySQL provides you. This often results in people reimplementing them using PHP even though it could be completely done in the database. Most common is `WHERE col < '.(time() - 86400)` etc. – ThiefMaster May 20 '12 at 14:53
  • As you mentioned various PHP applications store dates in UNIX timestamp and use their own functions to manipulate time and date. – John May 20 '12 at 14:56