Welcome Guest, Not a member yet? Register   Sign In
Active Record help..
#1

[eluser]DanTheMan[/eluser]
Hi all,

I am used to using active record, however I have become confused with a SQL query I want to use. I am confused because I have a number of constraints within a single where. The following is the SQL.

Code:
SELECT * FROM booking JOIN booking_rooms ON booking.booking_id = booking_rooms.booking_id
WHERE((arrival_date BETWEEN '2011-07-01' AND '2011-07-07')
OR (departure_date BETWEEN '2011-07-01' AND '2011-07-07')
OR (arrival_date < '2011-07-01' AND departure_date > '2011-07-01')
OR (arrival_date < '2011-07-01' AND arrival_date > '2011-07-07')) AND (room_id = 1)

Please could you advise me on the best way for such a query?

Thanks,
Dan
#2

[eluser]Armchair Samurai[/eluser]
It might be easier to just write out the query in SQL in this case, but here's how I would do it in CI's Active Record:

Code:
$start  = $this->db->escape($start);
$end    = $this->db->escape($end);
$sql    = "((arrival_date BETWEEN $start AND $end)
            OR (departure_date BETWEEN $start AND $end)
            OR (arrival_date < $start AND departure_date > $end)
            OR (arrival_date < $start AND arrival_date > $end))";

$this->db->join('booking_rooms', 'booking.booking_id = booking_rooms.booking_id');
$this->db->where($sql, NULL, FALSE);
$this->db->where('room_id', $num);

$query    = $this->db->get('booking');
#3

[eluser]DanTheMan[/eluser]
Hi,

Thanks for your help! This makes sense. I would normally just use SQL but I am trying to keep everything to the same practices throughout.

;-)




Theme © iAndrew 2016 - Forum software by © MyBB