CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
Mr.Web
Mr.Web

Reputation: 7154

Codeigniter, join of two tables with a WHERE clause

I've this code:

public function getAllAccess(){
    $this->db->select('accesscode');
    $this->db->where(array('chain_code' => '123');        
    $this->db->order_by('dateandtime', 'desc');
    $this->db->limit($this->config->item('access_limit'));
    return $this->db->get('accesstable')->result();
}

I need to join it with another table (codenamed table), I've to tell it this. Not really a literal query but what I want to achieve:

SELECT * accesscode, dateandtime FROM access table WHERE chain_code = '123' AND codenames.accselect_lista != 0

So basically accesstable has a column code which is a number, let us say 33, this number is also present in the codenames table; in this last table there is a field accselect_lista.

So I have to select only the accselect_lista != 0 and from there get the corrisponding accesstable rows where codenames are the ones selected in the codenames.

Upvotes: 1

Views: 5826

Answers (2)

Zoe
Zoe

Reputation: 2169

Good start! But I think you might be getting a few techniques mixed up here.

Firstly, there are two main ways to run multiple where queries. You can use an associative array (like you've started to do there).

$this->db->where(array('accesstable.chain_code' => '123', 'codenames.accselect_lista !=' => 0));

Note that I've appended the table name to each column. Also notice that you can add alternative operators if you include them in the same block as the column name.

Alternatively you can give each their own line. I prefer this method because I think its a bit easier to read. Both will accomplish the same thing.

$this->db->where('accesstable.chain_code', '123');
$this->db->where('codenames.accselect_lista !=', 0);

Active record will format the query with 'and' etc on its own.

The easiest way to add the join is to use from with join.

$this->db->from('accesstable');
$this->db->join('codenames', 'codenames.accselect_lista = accesstable.code');

When using from, you don't need to include the table name in get, so to run the query you can now just use something like:

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

Check out Codeigniter's Active Record documentation if you haven't already, it goes into a lot more detail with lots of examples.

Upvotes: 1

VBlades
VBlades

Reputation: 2251

Looking for this?

SELECT * 
FROM access_table a INNER JOIN codenames c ON
     a.chain_code = c.chain_code
WHERE a.chain_code = '123' AND
      c.accselect_lista != 0

It will bring up all columns from both tables for the specified criteria. The table and column names need to be exact, obviously.

Upvotes: 1

Related Questions