joining three tables-mysql

Question

basically i have three table named class,rooms,hostel.this are my tables

class

id  name          daily_rent    mon_rent    facilities
11  reservation     200          3000      reservation
23  per1            100          1000        per1
24  per2            100          1000        per2
25  pamba1          100          1000        pamba1
26  pamba2          100          1000        pamba2

my table rooms looks like this

    id  rm_number  capacity     bed_no                   class  hostel  is_vaccant
    34  1            5        1A,1B,1C,1D,1E               23     7      1
    35  2            4        2A,2B,2C,2D                  24     7      1
    36  3            4        3A,3B,3C,3D                  25     10     0
    37  4            6        4A,4B,4C,4D,4E,4F            26     10     0
    38  5            3        5A,5B,5C                     23     7      1
    39  6            8        6A,6B,6C,6D,6E,6F,6G,6H      25     10     1

my hostel table looks like this

    id  user_id     name    type    place   address          phone    username      password
    7   78         periyar  Girls   tirur   malappuram                 periyar  periyarhostel
    10  81         pamba    Boys    tirur   malappuram  04942569878     pamba   pambahostel

when i use my code like this

Controller

    public function view_vaccancy()
    {
       $data['active_mn'] = 'view_vaccancy';
       if( ! $this->ion_auth->logged_in() || !$this->ion_auth->is_admin())
         {
          redirect('login');
         }      

    $data['class'] = $this->admin_model->get_all_vaccancy_list()->result();
    $data1=array();


    foreach ($data['class'] as $row) 
    {
        $bed = $this->admin_model->bed_by_class_admin($row->id)->row();
        $res = $this->admin_model->get_admitted_room_admin($row->id);
        $data1[] = $bed->capacity-$res;
    }
    $data['available'] = $data1;

    $data['bed'] = $bed = $this->admin_model->bed_by_class_admin($row->id)->row();

    $data['form_url'] = ''; 
    $data['plc_hold'] = 'Name / ID'; 
    $this->load->view('admin/view_vaccancy',$data);
}

my model looks like this

public function get_all_vaccancy_list()
{
    $this->db->select('class.*,rooms.class,hostel.name as h_name');
    $this->db->join('rooms','class.id=rooms.class','left outer');
    $this->db->join('hostel','hostel.id=rooms.hostel');
    $query=$this->db->get('class');
    return $query;


}

and im getting my output like this

SI no   Hostel    Class     Total Seat  No. of Vaccancy
  1     periyar   per1         12       8
  2     periyar   per2         12       4
  3     pamba     pamba1       12       8
  4     pamba     pamba2       12       2
  5     periyar   per1         12       8
  6     pamba     pamba1       12       8

but i should have to get it like this

 SI no  Hostel    Class     Total Seat  No. of Vaccancy
  1     periyar   per1         12           8
  2     periyar   per2         12           4
  3     pamba     pamba1       12           8
  4     pamba     pamba2       12           2

and my problem is at model any guess to solve this thanks in advance

Rax Weber · Accepted Answer · 2016-10-03 07:31:44Z

1

You need to group it.

$this->db->group_by('hostel, class');

answered Oct 3, 2016 at 7:31

Rax Weber

3,78121 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

joining three tables-mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related