Multiple Joins in Codeigniter

I'm new to building databases and I'm trying to do a JOIN based on a having three database tables.

Table A = ID, Name, etc
Table B = ID, Name, etc
Table C = ID, TableAId, TableBId

What I can't figure out is using active record how to make this selection. I'm trying to make as few requests as possible, but am getting stumped on how it should all be written without doing three separate calls.

Answers


$this->db->select('*');
$this->db->from('TableA AS A');// I use aliasing make joins easier
$this->db->join('TableC AS C', 'A.ID = C.TableAId', 'INNER');
$this->db->join('TableB AS B', 'B.ID = C.TableBId', 'INNER');
$result = $this->db->get();

The join function works like this: join('TableName', 'ON condition', 'Type of join');

The equivilent sql:

SELECT *
FROM TableA AS A
    INNER JOIN TableC AS C
    ON C.TableAId = A.ID
    INNER JOIN TableB AS B
    ON B.ID = C.ID

I found that writing the SQL first, testing it, then converting to the active record style minimizes error.


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

hopefully you get my example.

Just add another $this->db->join();

For complex queries you might be better off looking at an ORM such as doctrine


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

$this->db->from('table1');

$this->db->join('table2','table1.id=table2.id'); 

$this->db->join('table3','table2.id=table3.id');

$this->db->join('table4','table3.id=table4.id'); 

$this->db->join('table5','table5.id=table4.id');

$this->db->where('table5.id',$this->session->userdata('id'));//getting value from session and match the id of table5 and then show data

$data=$this->db->get()->result();//all data store in $data variable

if you want a flexible query you could use:

http://codeigniter.com/user_guide/database/results.html

which utilizes the following syntax $query = $this->db->query('SELECT * FROM my_table');

here is the query:

SELECT a.name as namea ,b.name as nameb FROM tablec c
JOIN tablea a ON a.ID = c.ID
JOIN tableb b ON b.ID = c.ID

you may want to read more about joins here

then go through your results in such a way:

$query = $this->db->query("YOUR QUERY");

foreach ($query->result_array() as $row)
{
   echo $row['namea'];
   echo $row['nameb'];
}

Need Your Help

What is the best way to get an element's tag name in JS?

javascript html prototypejs

I want to get the name of a certain tag (to tell whether it is a div/input/span/p/so on)

How can I generate a list of function dependencies in MATLAB?

function matlab dependencies code-analysis

In order to distribute a function I've written that depends on other functions I've written that have their own dependencies and so on without distributing every m-file I have ever written, I need to