CODEWITHSUNDEEP
joincodeigniter-3hmvc
J HON
J HON

Reputation: 13

Codeigniter3, How to Join two table to display at the same time,

I am new to CodeIgniter. I do only display my first table but I don't have idea to join and display my second table at the same time, I tried codeigniter3 guide $this->db->join(); but still I'm stuck in my project.

Controller:

function ez($id) {
        $this->load->model('Join_model');
        $data['results']= $this->Join_model->getalldata($id);
        $this->load->view('join_view',$data);
    }

Model:

function getalldata($id) {
        $this->db->where('id',$id);
        $query = $this->db->get('tickets');
        if($query){
            return $query->row();
            
        }
    }

views

 <div class="col-sm-4 invoice-col">
                 <b>Ticket ID:</b>&nbsp <?php echo $results->id; ?><br>
                  <b>Business Name:</b>&nbsp <?php echo $results->bus_name; ?><br>
                  <b>Full Address:</b>&nbsp <?php echo $results->address; ?><br>
                  <b>Owner Name:</b>&nbsp <?php echo $results->owner_name; ?><br>
                  <b>Phone Number:</b>&nbsp <?php echo $results->owner_number; ?><br>
                  <b>Point Person:</b>&nbsp <?php echo $results->pt_person; ?><br>
                  <b>Phone Number:</b>&nbsp <?php echo $results->pt_person_number; ?><br>
                </div>

table1

CREATE TABLE `tickets` (
  `id` int(11) NOT NULL,
  `as_name` varchar(50) NOT NULL,
  `create_date` datetime NOT NULL,
  `bus_name` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `owner_name` varchar(255) NOT NULL,
  `owner_number` int(100) NOT NULL
  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

table2

CREATE TABLE `tickets_reply` (
  `id` int(11) NOT NULL,
  `tickets_id` int(11) NOT NULL,
  `remarks` varchar(255) NOT NULL,
  `comment` varchar(255) NOT NULL
  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Upvotes: 0

Views: 62

Answers (1)

Angus McRitchie
Angus McRitchie

Reputation: 11

pt_person & pt_person_number referenced in your view file is not in any of the tables you referenced. However, I think this is what you're looking for.

function getalldata($id) {
    $this->db->select('t.id, t.bus_name, t.address, t.owner_name, t.owner_number'); // add more columns here
    $this->db->where('id', $id);
    $this->db->from('tickets t');
    $this->db->join('tickets_reply tr', 't.id = tr.tickets_id', 'left');
    return $this->db->get()->row_object();
}

Read more about the join method here https://codeigniter.com/userguide3/database/query_builder.html?highlight=join#CI_DB_query_builder::join

Upvotes: 0

Related Questions