I am needing to retrieve product availability through query. The CMS I'm on is MODX.
My database is like this:
CAMERAS
mid
model
sku
RENTALS
cid
mid (this corresponds to the mid from above)
start_date
end_date
My query is to locate the availability of cameras based on if they are 'rented'.
A user selects his/her start and end date, selects 'locate cameras' and the mysql query looks like this:
$a = $modx->newQuery('Cameras');
$a->leftJoin('Rentals','Rentals',array('Rentals.mid = Cameras.mid'));
$a->select(array('Rentals.start_date' <= $start_date(POST data from previous page)));
$a->select(array('Rentals.end_date' >= $end_date(POST data from previous page)));
$a->select(array('Cameras.*','Rentals.cid as customerid','Rentals.start_date as start'));
$availability = $modx->getCollection('Cameras',$a);
$ret = '<p>There are '. count($availability) . ' cameras available</p>';
I added 5 cameras into the database to test, and added one as rented, however the query continues to return 'there are 5 cameras available'. It's not picking up the camera that is 'booked'.
You should pick up cameras that have Rentals.startdate >= enddate Or Rentals.enddate <= startdate
The choice between <= or < depends on the semantics of startdate (being inclusive or not)