Codeigniter: Join 3 tables and display data in view

Question

So I have 3 tables I wish to join.

I am building an app i Codeigniter and I have 3 tables

Client:
-id
-phone_number
-hospital_id
-smc_status
-testing_center_id

Hospital
-id
-name

Testing_center
-id
-name

In the model,I have this:

public function get_clients()
    {
        if($slug === FALSE)
        {
            $this->db->select('clients.*');
            $this->db->from('clients');
            $this->db->join('hospital', 'clients.id = hospital.id');
            $this->db->join('testing_center', 'clients.id = testing_center.id');
            $query = $this->db->get();

            return $query->result_array();
        }

        $query = $this->db->get_where('clients');
        return $query->row_array();
    }

In the view I have:

<tbody>
    <?php foreach ($clients as $client_item): ?>
    <tr>
        <td><?php echo $client_item['phone_number'] ?></td>
        <td><?php echo $client_item['smc_status'] ?></td>
        <td><?php echo $client_item['hospital_id'] ?></td> //i wish to have the hospital name here
        <td><?php echo $client_item['testing_center_id'] ?></td> //i wish to have the testing center name here
        <td><?php echo $client_item['language'] ?></td>
        <td><a href="#">View</a></td>
    </tr>
    <?php endforeach ?>
</tbody>

But that is because I have failed to show the hospital name and the testing center name on the third and fourth td. How can I go about that? I tried a few techniques that just did not seem to work for some reason. Please advise

score 3 · Answer 1 · edited May 23 '17 at 11:55

3

You're only selecting the values from the clients table. You need to select the columns from the other tables as well

$this->db->select('clients.id, 
    clients.phone_number,
    clients.smc_status,
    clients.language,
    hospital.name AS hospital_name, 
    testing_center.name AS testing_center_name');

Then you can access them by

<?php echo $client_item['hospital_name'] ?>
<?php echo $client_item['testing_center_name'] ?>

EDIT: Also you shouldn't use SELECT *, which clients.* is doing. Updated my code.

edited May 23 '17 at 11:55

Community

1
1

answered Sep 16 '12 at 12:55

Niklas Modess

2,521
1
20
34

I have made the changes but I end up getting the HTTP Error 500. Why is that? – raybesiga Sep 16 '12 at 21:09
There are a number of different things that could have went wrong. Check your PHP log. – Niklas Modess Sep 16 '12 at 21:50
I am running it on my localhost and I cannot check any logs, but I simply changed to: $this->db->select('clients.id, clients.phone_number, clients.smc_status, clients.language, hospital.name AS hospital_name, testing_center.name AS testing_center_name'); $this->db->from('clients'); $this->db->join('hospital', 'hospital.id = clients.id'); $this->db->join('testing_center', 'testing_center.id = clients.id'); $query = $this->db->get(); return $query->result_array(); } $query = $this->db->get_where('clients', array('slug' => $slug)); return $query->row_array(); } } – raybesiga Sep 17 '12 at 03:46
I think I had mis-spelled something. It works perfectly now. Thanks so much! – raybesiga Sep 17 '12 at 04:21
I am trying to fill a form and push data back to the database into the different tables. How would I go about that? DO i first bind the form to the database such that it understands the joins or should i place input text similar to what is expected in the database? – raybesiga Sep 29 '12 at 09:55
Just to add to this solution - you can use the select multiple times. You can do: $this->db->select('clients.*'); $this->db->select('hospital.name as hospital_name'); $this->db->select('testing_center.id as testing_center_id'); – Stephen O'Flynn Mar 27 '13 at 18:23

score 0 · Answer 2 · answered Sep 16 '12 at 12:53

0

What happens if you try this:

 $this->db->join('hospital', 'hospital.id = clients.id');
 $this->db->join('testing_center', 'testing_center.id = clients.id');

instead of this:

 $this->db->join('hospital', 'clients.id = hospital.id');
 $this->db->join('testing_center', 'clients.id = testing_center.id');

also check

client*s* and client if they are the same everywhere

And also change as Nerd proposed: $this->db->select('clients.*'); to:

$this->db->select('*');

answered Sep 16 '12 at 12:53

Derfder

3,204
11
50
85

I have made all the necessary changes first the recommendations by Nerd, as well as yours but I end up getting the HTTP Error 500. Why is that? – raybesiga Sep 16 '12 at 21:10
Checked and I had made some spelling errors. Thanks for the advice! – raybesiga Sep 17 '12 at 04:23
I am trying to fill a form and push data back to the database into the different tables. How would I go about that? DO i first bind the form to the database such that it understands the joins or should i place input text similar to what is expected in the database? – raybesiga Sep 29 '12 at 09:56

score 0 · Answer 3 · answered Sep 18 '12 at 09:32

0

It sholud be like this

 $this->db->join('hospital', 'clients.hospital_id = hospital.id');
 $this->db->join('testing_center', 'clients.testing_center_id  = testing_center.id');

answered Sep 18 '12 at 09:32

GautamD31

28,552
10
64
85

There is an wrong with the joining the same column names in both tabls like ("hospital_id in clients,id in hospital") and ("testing_center_id in clients,id in testing_center") – GautamD31 Sep 18 '12 at 09:34

Codeigniter: Join 3 tables and display data in view

3 Answers3