[eluser]Bigil Michael[/eluser]
in my hotel reservation, first i want to check the availability of rooms. inorder to do that i generated a query given below, it print room categories
Code:
$result_item = $this->db->query("SELECT DISTINCT rc.category_name,rc.category_id
FROM rooms_category AS rc
INNER JOIN
(SELECT r.rooms_name, r.id, r.category_id
FROM rooms AS r
LEFT JOIN ( SELECT rr.rooms_id, rr.checkin_date, rr.checkout_date
FROM rooms_reservation AS rr
WHERE ((
rr.checkin_date
BETWEEN '$start_date'
AND '$end_date'
)
OR (
(rr.checkout_date)
BETWEEN '$start_date'
AND '$end_date'
)
OR((rr.checkin_date < '$start_date') AND ((rr.checkout_date) > '$end_date')
))
AND rr.status='1'
) AS ar ON r.id = ar.rooms_id WHERE ar.rooms_id is null AND r.status='1'
)AS ac ON ac.category_id = rc.category_id LIMIT $pgoffset,$limit");
return $result_item->result();
it is working fine
now my client's requirement is to print room categories with multiple rooms available.
now i changed my query like this
Code:
$result_item = $this->db->query("SELECT DISTINCT rc.category_name,rc.category_id
FROM rooms_category AS rc
INNER JOIN
(SELECT r.rooms_name, r.id, r.category_id
FROM rooms AS r
LEFT JOIN ( SELECT rr.rooms_id, rr.checkin_date, rr.checkout_date
FROM rooms_reservation AS rr
WHERE ((
rr.checkin_date
BETWEEN '$start_date'
AND '$end_date'
)
OR (
(rr.checkout_date)
BETWEEN '$start_date'
AND '$end_date'
)
OR((rr.checkin_date < '$start_date') AND ((rr.checkout_date) > '$end_date')
))
AND rr.status='1'
) AS ar ON r.id = ar.rooms_id WHERE ar.rooms_id is null AND r.status='1' AND (SELECT COUNT(r.category_id)>=2)
)AS ac ON ac.category_id = rc.category_id LIMIT $pgoffset,$limit");
return $result_item->result();
change is only added this line
AND (SELECT COUNT(r.category_id)>=2)
here 2 is just a static value for test
now the result is empty every time
if i use the code AND (SELECT COUNT(r.category_id)>=1) it will shows result
i think this count portion is not working....
can anyone help me to solve this problem?????
thanks in advance....
