Use where clause with Like in codeigniter

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.

It is returning the records base on the Keywords that i am passing in the query(Return all other users record based on keywords,Not base on the recruiter_id). It must show the search results related to the particular user. — user2524013, Jun 29 '13 at 22:27

score 0 · Answer 1 · answered Jun 30 '13 at 05:35

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

score -1 · Answer 2 · edited May 26 '15 at 12:08

-1

Try this:

function select_where_in($field,$value,$table)
    {
        $this->db->select("*");
        $this->db->where_in($field, $value);
        $get_data=$this->db->get($table);
        return $get_data->result();
    }

edited May 26 '15 at 12:08

kenorb

155,785
88
678
743

answered May 26 '15 at 11:45

testing

1

Use where clause with Like in codeigniter

2 Answers2