CODEWITHSUNDEEP
phpmysqljoin
Yasitha
Yasitha

Reputation: 911

mysql join two tables that one table is having multiple matching rows

I'm trying to join two tables. lets say first table is test1 and other one is test2 in test1 table I have fields HOTEL_ID NAME CITY COUNTRY

Then in the test2 table I have ROOM_ID HOTEL_ID ROOM_TYPE

so most of the time a hotel is having many type of rooms. so when I join these two tables using this query 

$this->db->like('city', $visitingPlace);
$this->db->or_like('country', $visitingPlace);
$this->db->or_like('name', $visitingPlace);
$this->db->limit($per_page, $page);
$this->db->select('*')
         ->from('test1')
         ->join('test2', 'test1.hotel_id = test2.hotel_id', 'left');
$query = $this->db->get();

it is repeatedly showing the hotel name for each room type matching to that hotel id but I want to show hotel name once with all the room types. how can I achieve this?

Thank You

Upvotes: 0

Views: 103

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

You use the same query as it is just add order by hotel id and in php use logic to show only once the hotel name for each group of rooms

$this->db->like('t1.city', $visitingPlace);
$this->db->or_like('t1.country', $visitingPlace);
$this->db->or_like('t1.name', $visitingPlace);
$this->db->limit($per_page, $page);
$this->db->select('t1.*,t2.ROOM_TYPE')
         ->from('test1 t1')
         ->join('test2 t2', 't1.hotel_id = t2.hotel_id', 'left')
->order_by('t1.hotel_id','ASC');

Fetch results from query

$results = $this->db->get()->result();

Loop through result store hotel id for each group of rooms and show only once

$currentParent = false;
foreach ($results as $r) {
    if ($currentParent != $r->hotel_id ) {
        echo '<h1>Hote Name ' . $r->name. '</h1>';
        $currentParent = $r->hotel_id;
    }
    echo '<p>' .$r->ROOM_TYPE. '</p>';
}

Sample output

<h1>Hote Name test1</h1>
    <p>room type 1</p>
    <p>room type 2</p>
<h1>Hote Name test 2</h1>
    <p> room type 3</p>

and so on

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

The question is tagged "mysql", so here is a MySQL solution:

select h.*, group_concat(distinct room_type) as RoomTypes
from test1 h left join
     test2 r
     on h.hotel_id = r.hotel_id
group by h.hotel_id;

For each hotel, this will produce a column with a list of the room types at the hotel.

Upvotes: 2

Related Questions