I am using codeigniter to build a rental website for a client. I am trying to return true only if no rental periods match my query. Basically there is a rental table with start date and end date.
My customer selects a start date from a datepicker and the end date is a set number of days after the start date.
So I want to find out if any rental items are being rented on those dates to verify wether the client can reserve the item. Here is what I have so far and I need to accomplish this with Codeigniters active record...
function check_product($periodLength) {
//This is the start time they chose from the picker
$startTime = $_POST['date'];
//The period length is a variable of days, so the end date would be their chosen start date plus a certain amount of days...
$endTime = strtotime('+'.$periodLength.'day', $startTime);
$this->db->where('productsId', $_POST['productId']);
//This is where I need help!!! What query would give me records that are NOT between the start and end dates that i'm wanting
$query = $this->db->get('rentals');
$data = array();
foreach ($query->result() as $row) {
$data[] = array(
'id' => $row->id,
'customerId' => $row->customerId,
'productsId' => $row->productsId,
'periodStart' => $row->periodStart,
'periodEnd' => $row->periodEnd,
'status' => $row->status,
'specialInstructions' => $row->specialInstructions,
'adminNotes' => $row->adminNotes
);
}
return $data;
}
Most of my problem is just in my head i'm sure but i need to figure out if my startdate to enddate period is already reserved.
Try this:
$startTime = $_POST['date'];
$endTime = strtotime('+'.$periodLength.'day', $startTime);
$this->db->where('productsId', $_POST['productId']);
$this->db->where(" $startTime NOT BETWEEN periodStart AND periodEnd AND $endTime NOT BETWEEN periodStart AND periodEnd OR ($startTime < periodStart AND $endTime > periodEnd) ");
//Since this is a complex where clause i would recommend
//to put enitre clause in single where statement rather than
//putting in different where statement.
//And it is upto codeigniter active record standards
$query = $this->db->get('rentals');