CODEWITHSUNDEEP
activerecordselectcodeigniter
Kevin Brown
Kevin Brown

Reputation: 12650

Codeigniter: Select from multiple tables

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);

Upvotes: 9

Views: 77929

Answers (7)

Abdelhakim Ezzahraoui
Abdelhakim Ezzahraoui

Reputation: 481

// 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();

Upvotes: 0

Vivek Kumar Kushwaha
Vivek Kumar Kushwaha

Reputation: 11

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();

Upvotes: 1

John Rand
John Rand

Reputation: 1034

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();

Upvotes: 8

Oto Gugeler
Oto Gugeler

Reputation: 21

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.

Upvotes: 1

Amine ABDALKHALKI
Amine ABDALKHALKI

Reputation: 445

$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.

Upvotes: 0

Phil Sturgeon
Phil Sturgeon

Reputation: 30766

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.

Upvotes: 22

Favio
Favio

Reputation: 1009

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);

Upvotes: 12

Related Questions