CODEWITHSUNDEEP
phpmysqlcodeigniter
learningbyexample
learningbyexample

Reputation: 1547

I'm trying to query from multiple tables usign active record class from codeigniter

I'm tryig to get the data that comes from the mysql query to use it later. I don't care if I have to output the data in a table with join clauses. I only need to be able to get a speficic part from it. But i want to get all the ones that are related or have relevance to each other.

this is made with the codeigniter MVC framework with php

example database with tables

I want to be able to access the data from each table with one query

function get_reg(){
    $this->db->select('*');
    $this->db->from('
                        tableA.*,
                        tableB.*,
                        tableC.*,
                        tableD.*
                    ');
    $this->db->where('tableA.name = tableB.name');
    $this->db->where('tableC.name = tableD.name');
    $this->db->where('tableA.name = tableD.name');
    $this->db->where('tableC.name = tableB.name');
    $query = $this->db->get();
    return $query->result_array();
}

something like this to then be accessed like so:

$this->load_model->get_reg() //and get what I want

I dont know if this is posible.

Upvotes: 1

Views: 37

Answers (1)

Jerry
Jerry

Reputation: 3608

To do that in a single query, you need to use JOIN syntax.

Your answer would look something like 

$this->db->from('tableA');
$this->db->join('tableB', 'tableA.name = tableB.name', 'LEFT'); // the type of join depends on the behavior you want
$this->db->join('tableC', 'tableA.name = tableC.name', 'LEFT');
$this->db->join('tableD', 'tableA.name = tableD.name', 'LEFT');
$query = $this->db->get();

The above joins all the tables together based on the name, which appears to be the same in all the tables. The type of join matters when some rows of the joined tables don't have a value for name.

You can read more in the docs

Upvotes: 0

Related Questions