在codeigniter中使用where子句和Like

Question

I am working on a project. I am implementing the Search functionality in my System.

I will have to show the search record from two tables base on the current use login. I have tried the following code:

    function searchActivity($limit,$offset,$keyword1,$keyword2,$recruiter_id)
    {   
        $q=$this->db->select('*')->from('tbl_activity')->limit($limit,$offset);     
        $this->db->join('tbl_job', 'tbl_job.job_id = tbl_activity.job_id_fk', 'left outer');
        $this->db->order_by("activity_id", "ASC");          
        $this->db->like('job_title',$keyword1,'both');
        $this->db->or_like('job_title',$keyword2,'both');       
        $this->db->or_like('activity_subject',$keyword1,'both');
        $this->db->or_like('activity_subject',$keyword2,'both');        
        $this->db->or_like('activity_details',$keyword1,'both');
        $this->db->or_like('activity_details',$keyword2,'both');        
        $this->db->where('tbl_activity.recruiter_id_fk',$recruiter_id);             
        $ret['rows']=$q->get()->result();
return $ret;
}

I want to show search results based on the current user id, which is currently store in $recruiter.

Thanks in advance.

Answer 1

You have to group your all like conditions in the () and then add AND for all like conditions see below

function searchActivity($limit,$offset,$keyword1,$keyword2,$recruiter_id)
{    

   $query="SELECT * FROM tbl_activity 
    left outer tbl_job ON (tbl_job.job_id = tbl_activity.job_id_fk)
    WHERE tbl_activity.recruiter_id_fk=".$recruiter_id."  AND
    (job_title LIKE '%".$keyword1."%' OR job_title LIKE '%".$keyword2."%' OR
    activity_subject LIKE '%".$keyword1."%' OR activity_subject LIKE '%".$keyword2."%' OR
    activity_details LIKE '%".$keyword1."%' OR activity_detailsLIKE '%".$keyword2."%'
    )
    ORDER BY activity_id ASC LIMIT $limit,$offset";
   $ret['rows']= $this->db->query($query)->result();

 return $ret;
}

Your WHERE condition is effecting the recruiter_id_fk but you have the like conditions with the level like SELECT * FROM table WHERE id=1 OR keyword LIKE '%test%' OR keyword1 LIKE '%test%' it will give results the which are matched with the id as well as the matched with keyword if you want the results matched with keyword but in same id then you have to group the OR conditions with a parent AND operation like

SELECT * FROM table WHERE id=1 AND (keyword LIKE '%test%' OR keyword1 LIKE '%test%')

Hope it makes sense