Codeigniter: Select from multiple tables

Question

How can I select rows from two or more tables?

I'm setting default fields for a form, and I need values from two tables...

My current code reads:

    $this->CI->db->select('*');
    $this->CI->db->from('user_profiles');
    $this->CI->db->where('user_id' , $id);
    $user = $this->CI->db->get();
    $user = $user->row_array();
    $this->CI->validation->set_default_value($user);

Phil Sturgeon · Accepted Answer · 2010-05-06 09:05:52Z

22

The example in the User Guide should explain this:

$this->db->select('*'); // <-- There is never any reason to write this line!
$this->db->from('blogs');
$this->db->join('comments', 'comments.id = blogs.id');

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

// Produces:
// SELECT * FROM blogs
// JOIN comments ON comments.id = blogs.id

See the whole thing under Active Record page in the User Guide.

answered May 6, 2010 at 9:05

Phil Sturgeon

30.8k12 gold badges82 silver badges118 bronze badges

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

2 Comments

John Rand Over a year ago

The example in the User Guide doesn't seem to produce a field from the second table. How do you do that?

Phil Sturgeon Over a year ago

* = everything, from all tables available. If it's not showing up you're doing something wrong. Also if you have two fields with the same name in different tables, then only one will show. You need to do foo as bar to get ->bar

Favio · Accepted Answer · 2010-05-06 08:37:03Z

12

Just add the other table to the "->from()" method. Something like:

 $this->db->select('t1.field, t2.field2')
          ->from('table1 AS t1, table2 AS t2')
          ->where('t1.id = t2.table1_id')
          ->where('t1.user_id', $user_id);

answered May 6, 2010 at 8:37

Favio

1,0091 gold badge8 silver badges19 bronze badges

3 Comments

Phil Sturgeon Over a year ago

That will produce broken syntax.

Favio Over a year ago

Not on my end. Can you elaborate?

Mint Over a year ago

That syntax is only supported in php5

John Rand · Accepted Answer · 2013-02-03 14:58:05Z

8

I think the question was not so much about joins as how to display values from two different tables - the User Guide doesn't seem to explain this.

Here's my take:

    $this->db->select('u.*, c.company, r.description');
    $this->db->from('users u, company c, roles r');
    $this->db->where('c.id = u.id_company');
    $this->db->where('r.permissions = u.permissions');
    $query = $this->db->get();

answered Feb 3, 2013 at 14:58

John Rand

1,0342 gold badges12 silver badges21 bronze badges

1 Comment

Adrian P. Over a year ago

You can do this if you need the record set: return $this->db->get()->result();

Kevin Brown · Accepted Answer · 2012-07-05 14:52:31Z

1

I think the syntax is incorrect. You need to select one record. I have two tables, and I have an id from one table transfer by parameter, and the relation of both tables.

edited Jul 5, 2012 at 14:52

Kevin Brown

12.7k34 gold badges100 silver badges156 bronze badges

answered Jul 5, 2012 at 8:48

Oto Gugeler

212 bronze badges

Comments

Håken Lid · Accepted Answer · 2017-01-21 01:35:37Z

1

Try this

   $this->db->select('*')
            ->from('student')
            ->where('student.roll_no',$id)
            ->join('student_details','student_details.roll_no = student.roll_no')
            ->join('course_details','course_details.roll_no = student.roll_no');
   $query = $this->db->get();
   return $query->row_array();

edited Jan 21, 2017 at 1:35

Håken Lid

23.2k10 gold badges58 silver badges73 bronze badges

answered Jun 13, 2016 at 13:32

Vivek Kumar Kushwaha

111 bronze badge

Comments

sekati · Accepted Answer · 2012-04-16 06:08:55Z

0

$SqlInfo="select a.name, b.data fromtable1 a, table2 b where a.id=b.a_id";
$query = $this->db->query($SqlInfo);

try this way, you can add a third table named as c and add an 'and' command to the sql command.

edited Apr 16, 2012 at 6:08

sekati

5355 silver badges12 bronze badges

answered May 5, 2010 at 15:32

Amine ABDALKHALKI

4453 silver badges15 bronze badges

3 Comments

Kevin Brown Over a year ago

I'm a major noob. Can you explain this a bit more?

talha2k Over a year ago

That's not an active record way of running queries.

Amine ABDALKHALKI Over a year ago

based on the documentation, yes it is a way to do JOIN operation in CodeIgniter codeigniter.com/user_guide/database/results.html

Abdelhakim Ezzahraoui · Accepted Answer · 2019-10-16 16:28:44Z

0

// Select From Table 1 All Fields, and From Table 2 one Field or more ....

$this->db->select('table1.*, table2.name');
    $this->db->from('table1, table2');
    $this->db->where('table2.category_id = table1.id');
    $this->db->where('table2.lang_id',$id); // your where with variable
    $query = $this->db->get();
    return $query->result();

answered Oct 16, 2019 at 16:28

Abdelhakim Ezzahraoui

4815 silver badges12 bronze badges

Collectives™ on Stack Overflow

Codeigniter: Select from multiple tables

7 Answers 7

2 Comments

3 Comments

1 Comment

Comments

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

3 Comments

1 Comment

Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related