1

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";
starlings
  • 411
  • 6
  • 12

2 Answers2

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();
Vickel
  • 7,879
  • 6
  • 35
  • 56
0

Try this:

$this->db->select('m.*');
$this->db->select('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');
user2398907
  • 64
  • 9
  • '$this->db->where('c.LANG', 'EN');' does not work. this line return always first language in db – starlings Apr 18 '20 at 22:46
  • please explain why this code will produce a different SQL query than in the OP? You can `echo $this->db->query();` and will see that there is no difference; – Vickel Apr 18 '20 at 22:55