0

How to write this below query like a join condition in codeigniter 

$query=$this->db->query("select TypeID,verification_type from table_verify where TypeID not in(select verificationId from table_invistigate where Id=$id)");
          $result = $query->result();
        return $result;
vijay kumar
  • 287
  • 2
  • 8
  • 28

2 Answers2

0

Like this.first get array of verification ids then use this array in $this->db->where_not_in().

$this->db->select('verificationId');
$this->db->where('Id',$id);
$verificationIds = $this->db->get('table_invistigate')->result_array(); //array of   verificationIds 

foreach($verificationIds as $vid){
$ids[] = $vid['verificationId']; 
}


$this->db->select('TypeID,verification_type');
$this->db->where_not_in('TypeID',$ids);
$result = $this->db->get('table_verify')->result_array();
print_r($result);
Hikmat Sijapati
  • 6,869
  • 1
  • 9
  • 19
  • if you directly use `$this->db->query()` it does not prevent from sql injunction. try as above. – Hikmat Sijapati Jan 26 '17 at 08:53
  • i tried $verificationIds = $this->db->get('table_invistigate')->result_array(); it is returning array of array which is showing database error as where not in(array,array) – vijay kumar Jan 26 '17 at 09:41
  • duplicate of http://stackoverflow.com/questions/6047149/subquery-in-codeigniter-active-record "subquery in codeigniter active record" – qwertzman Jan 26 '17 at 10:49
0
    $this->db->select('table_verify.*,table_invistigate.verificationId');
    $this->db->from('table_verify');
    $this->db->join('table_invistigate',
   'table_verify.verificationId=table_invistigate.verificationId ');
    $this->db->where('table_verify.id',$id);
    $query = $this->db->get();
    return $query->result();
Parvez Ahmed
  • 650
  • 7
  • 16