Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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);
like image 719
Kevin Brown Avatar asked May 05 '10 15:05

Kevin Brown


People also ask

How do you retrieve data from multiple tables in MySQL without JOIN?

You can replace the JOIN keyword with a comma in the FROM clause. What do you do next? There's no ON keyword for you to state the joining condition as there would be when using JOIN , e.g., on which two columns you want to join the tables. In this method, you simply use a WHERE clause to do so.

How to write JOIN query in CodeIgniter?

$this->db->join() Multiple function calls can be made if you need several joins in one query. If you need a specific type of JOIN you can specify it via the third parameter of the function. Options are: left, right, outer, inner, left outer, right outer and full outer.

How do I JOIN ci4?

CodeIgniter 4 Query Builder join() method: inner This join() method produces the below MySQL query as returned by $db->getLastQuery(): MySQL default JOIN query. CodeIgniter 4 Query Builder join() method with the explicit inner keyword. MySQL INNER JOIN query.


3 Answers

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.

like image 156
Phil Sturgeon Avatar answered Oct 22 '22 16:10

Phil Sturgeon


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);
like image 28
Favio Avatar answered Oct 22 '22 17:10

Favio


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();
like image 8
John Rand Avatar answered Oct 22 '22 18:10

John Rand