How to order the tables and relating columns when building a database query involving JOINs with CodeIgniter

Question

I am trying to join two tables and display there data in single table. I looked at the user guide that codeigniter has and I am not sure how this works.

$this->db->join();

What table should be first and what id key should be first. Can someone explain me more in detail about this please use examples if you can. I am trying to join credential table and tblanswers.

I have tried to code a function using this example:

$this->db->select('*');
$this->db->from('blogs');
$this->db->join('comments', 'comments.id = blogs.id');

$query = $this->db->get();

EDIT: Instead of using join method in CodeIgniter is it possible to use a simple function to retrieve the two table data separately? All I want is to echo the data from database table on to a html table in my website page to be displayed is it possible to write two get functions to retrieve two tables separately?

You can do that creating two methods on your model and each one does the query you want. — Fabio Antunes
– Fabio Antunes, Commented Mar 4, 2013 at 11:52
While "How to use CodeIgniter's join()" is an on-topic question, I don't actually see where your coding attempt is failing. What are you having trouble with? If you want a full MVC solution, we're going to need more details about database, and your desired output. — mickmackusa
– mickmackusa ♦, Commented Oct 24 at 2:43

Scott C Wilson · Accepted Answer · 2017-07-18 13:06:37Z

21

It doesn't matter what table is first... Simply:

<?php

$this->db->select('t1.name, t2.something, t3.another')
     ->from('table1 as t1')
     ->where('t1.id', $id)
     ->join('table2 as t2', 't1.id = t2.id', 'LEFT')
     ->join('table3 as t3', 't1.id = t3.id', 'LEFT')
     ->get();
?>

edited Jul 18, 2017 at 13:06

Scott C Wilson

19.7k11 gold badges64 silver badges88 bronze badges

answered Mar 4, 2013 at 11:35

rafciokk0

2361 silver badge5 bronze badges

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

Comments

Detroit Charan · Accepted Answer · 2018-06-09 06:37:36Z

here is how it works:

suppose we have two tables namely student, library.

note: but remember that one of the column should match if you want to use where condition/ here we assume that both tables have std_id column

Write the the select query as follows, in the brackets write what are all the things you want

note:write as shown below don't put quotes to each single one put it on whole at once.

*note: suppose we want name, phone_no. from student table and book_name form library table.*

      $this->db->select('name, phone_number, book_name');

Now write the from query and write one of the table name(No rule)
```
  $this->db->from('student');
```

Now join this with the another table with join query

  $this->db->join('library', 'students.std_id=library_std_id');

Now write the where condition that like you want book name form library table where std id=1(in practical you need to fetch this id from view/database)
```
  $this->db->where('std_id', 1);
  $q= $this->db->get();
```

That's it it's done now you can print and check the result.

Alessandro Minoccheri · Accepted Answer · 2013-03-04 11:34:55Z

1

$this->db->join('comments', 'comments.id = blogs.id');

With this line you tell: search me inside comments all comments with id equal blogs.id.

Usually is something like that I think:

$this->db->join('comments', 'comments.blogs_id = blogs.id');

You have to insert into your table a field named blogs_id (int value unisgned) because blogs can have more comments. Isn't important the position of first or second value

answered Mar 4, 2013 at 11:34

Alessandro Minoccheri

36.1k22 gold badges128 silver badges176 bronze badges

4 Comments

Edvinas Liutvaitis Over a year ago

so the join will only work if the two table data has the same id ? for example tblanswers has id 12 and credentials have id 12

Alessandro Minoccheri Over a year ago

in your case yes but I don't know for your database if is correct

Edvinas Liutvaitis Over a year ago

if the ids have be the same would there be an error duplicate's id ?

Edvinas Liutvaitis Over a year ago

yeap a tblanswer and credentials

Raja Sekhar Y · Accepted Answer · 2017-09-26 08:57:36Z

1

Hi this will work for joining two tables in codeIgnator.

$this->db->select("chat.id,chat.name,chat.email,chat.phone,chat.date,post.post");
      $this->db->from('chat');
      $this->db->join('post', 'chat.id = post.id');
      $query = $this->db->get();

    if($query->num_rows() != 0)
    {
        return $query->result();
    }
    else
    {
        return false;
    }

You can change to the query as you like do trail and error method to get your appropriate results.

edited Sep 26, 2017 at 8:57

answered Sep 23, 2017 at 12:14

Raja Sekhar Y

1232 silver badges13 bronze badges

Comments

Dragonthoughts · Accepted Answer · 2019-11-04 12:27:51Z

0

This is my code for joint many tables as possible.

I have 3 tables professeurs,publications and support.

public function toutesdonnées(){

    $this->db->select("*");
      $this->db->from('publication');
      $this->db->join('support', 'publication.idsup = support.idsup');
      $this->db->join('professeurs', 'publication.emailprof = professeurs.emailprof');
      $query = $this->db->get();

    if($query->num_rows() != 0)
    {
        return $query->result();
    }
    else
    {
        return false;
    }

edited Nov 4, 2019 at 12:27

Dragonthoughts

2,2348 gold badges27 silver badges28 bronze badges

answered Nov 4, 2019 at 11:30

Mbra Oliver

1

1 Comment

mickmackusa Oct 24 at 2:44

I never recommend returning false from a model method that ordinarily returns an array payload.

Collectives™ on Stack Overflow

How to order the tables and relating columns when building a database query involving JOINs with CodeIgniter

5 Answers 5

Comments

Comments

4 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related