Codeigniter query builder does not work correctly

Question

can someone explain the difference between these codes? Because the query builder does not give the correct result, but the other query gives the correct result.

What is the difference that I cannot see?

$this->db->select('m.*,c.COUNTRY_NAME');
$this->db->from('members m');
$this->db->join('country c','c.COUNTRY_ALPHA2_CODE = m.location', 'left');
$this->db->where('c.LANG', 'EN');

Query that gives the correct result

SELECT m.*,c.COUNTRY_NAME FROM members m LEFT JOIN country c ON c.COUNTRY_ALPHA2_CODE = m.location WHERE c.LANG = "EN"; 

Answer 1

To produce a complete query-string with CI, you need to add this line:

$query=$db->get(); to your approach.

the complete code would look like:

$this->db->select('m.*,c.COUNTRY_NAME');
$this->db->from('members m');
$this->db->join('country c','c.COUNTRY_ALPHA2_CODE = m.location', 'left');
$this->db->where('c.LANG', 'EN');
$query=$db->get();

after this line you can check the SQL string output with:

echo $this->db->query();

from here you continue with Generating Query Results for your views

response to comment:

'$this->db->where('c.LANG', 'EN');' does not work. this line return always first language in db

you need to place the language query into the join:

$this->db->select('m.*,c.COUNTRY_NAME');
$this->db->from('members m');
$this->db->join('country c','(c.COUNTRY_ALPHA2_CODE = m.location AND c.LANG=\'EN\')', 'left');
$query=$db->get();