phpdatecodeigniterquery-builderbetween

Codeigniter: Return true if dates are NOT between 2 values


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.


Solution

  • 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');