MySQL Find if 2 dates are between in date ranges for multiple rows

Question

I am using Codeigniter + MySQL + Active Record and i am building a project with time periods for room prices.

I have the below schema in my mysql table

Package_periods

Room_package_prices

A package_id can have many periods as you can see in the example 01-08-17 to 08-08-2017, 10-08-2017 to 17-08-2017 etc.

For a given searched period (let's say 02-08-2017 to 14-08-2017), how can i find the package_id that not only exceeds the searched period but also there is not even 1 day outside of the searched period?

I mean, the package_id periods (from and to through multiple mysql rows), should cover ALL the days of a searched period without leaving a single gap.

public function getFHotels($checkin = null, $checkout = null, $adults = null, $packageType = null, $package_id = null, $limit, $start, $lang_id) {
        $this->db->select('DISTINCT(hotels.hotel_id)')
                ->from('hotels')
                ->join('rooms', 'rooms.hotel_id=hotels.hotel_id')
                ->where('rooms.room_active', 1)
                ->where('hotels.hotel_active', 1)
                ->limit($limit, $start);

        if ($packageType) {
            $this->db->where('rooms.room_package_id', $packageType);
        }
        if ($package_id && $adults) {
            //if $package_id is given, do not search for checkin-checkout
            $this->db->join('room_package_prices', 'room_package_prices.room_id=rooms.room_id');
            $this->db->where('room_package_prices.package_period_id', $package_id);
            $this->db->where('room_package_prices.adults', $adults);
            $this->db->where('room_package_prices.price>', 0);
        } elseif ($checkin && $checkout && $adults) {
            //if $checkin and $checkout is given, search for the period_from and period_to
            //Here goes my actual question
        }

        $qry = $this->db->get();
        if ($qry->num_rows() > 0)
            return $qry->result_array();
        return FALSE;
    }

Answer 1

Ok so i managed to solve this by myself in a different way. I wanted to implement this functionality in my model function getFHotels but couldn't find a way to do that.

So i separated this functionality in my controller and created a new model function just for this purpose. Long story short, the idea was

1. Grab the desired searched period (checkin - checkout) from the client
2. Loop through each day from checkin to checkout and query for every day to the table, which package_id covers that day
3. Push the everyday results in array
4. Find the intersects of the array. If a package_id is in every key of the array, i have found it.

Here is the code:

        $begin = new DateTime($checkin);
        $end = new DateTime($checkout);
        $interval = DateInterval::createFromDateString('1 day');
        $period = new DatePeriod($begin, $interval, $end);
        $legit_packages = array();

        //loop through each day and ask the table if a package_id includes this day
        foreach ($period as $dt) {
            //add each day results in legit_day_packages array
            $legit_day_packages = array();
            $day = $dt->format("Y-m-d");
            //find the packages that include the day
            $periods_found = $this->hotel_model->findFPackagePeriodsPerDay($day);
            if ($periods_found) {
                //foreach found packages, array push in legit_day_packages the results
                foreach ($periods_found as $p_f_key => $p_f) {
                    array_push($legit_day_packages, $p_f['package_id']);
                }
            }
            //Push the legit_day_packages into $legit_packages array. This array will have 1 key per day, containing the package_ids, if found
            array_push($legit_packages, $legit_day_packages);
        }

        //Find the intersect package_id. In this was i exclude the 'gaps'. A package_id must appear in every key per day.
        $first = $legit_packages[0];
        for ($i = 1; $i < count($legit_packages); $i++) {
            $result = array_intersect($first, $legit_packages[$i]);
            $first = $result;
        }

        //the $result contains all the package_id that satisfies both the range and no 'gap' in the period.
        var_dump($result);

Model Function

public function findFPackagePeriodsPerDay($day) {
        $qry = $this->db->select('packages.package_id')
                ->from('package_periods')
                ->join('packages', 'packages.package_id=package_periods.package_id')
                ->where('packages.is_package_type', 1)
                ->where('package_periods.period_from<=', $day)
                ->where('package_periods.period_to>=', $day)
                ->get();
        if ($qry->num_rows() > 0)
            return $qry->result_array();
        return FALSE;
    }