CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
Ant100
Ant100

Reputation: 403

How to group 'like' with Codeigniter active records?

I'm building a search form with multiple options. The idea is to look for people with certain characteristics like career, name of last company job, years of experience, etc.

In the first part of the form you can enter a career you would like candidate to have, on second part you can enter one or more companies name. What I want is my query to look something like this:

SELECT *
FROM work_experience
WHERE position = 'some position'
AND (company LIKE 'company 1' OR company LIKE 'company 2', etc)

Problem is I don't know hoy many company names the user might enter, what I do is store every name in an array which I don't know how big it may be.

I tried using or_like() active record, but the problem was that it looked for people with 'positionx' OR people working at 'company x' which is not the result I want.

Is there a way to make this query using active records? Or even how can I make the query in simple SQL if I don't know the exact number of company names I'm searching for.

Thank you for your time and help, if something was not clear please let me know.

Upvotes: 2

Views: 1899

Answers (2)

Parag Tyagi
Parag Tyagi

Reputation: 8960

This is how it should be -


View:

<form action="/career/search/" method="POST">
      Position: <input type="text" name="position" />
      Company1: <input type="text" name="company[]" />
      Company2: <input type="text" name="company[]" />
      Company3: <input type="text" name="company[]" />
      ...
      ...
      CompanyN: <input type="text" name="company[]" />
</form>


Controller:

class Career extends CI_Controller
{
    public function __construct()
    {
        parent::__construct();
    }

    public function search()
    {
       $this->career_model->search_career($this->input->post());
    }
}


(A) Model (using where_in):

class Career_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }

    public function search_career($data)
    {
        $this->db->select('*');
        $this->db->where('position', $data['position']);
        $this->db->where_in('company', $data['company']);
        $query = $this->db->get('work_experience');

        echo "<pre>"; print_r($query->result());   // Result           
    }
}


(B) Model (using or_like):

class Career_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }

    public function search_career($data)
    {
        $this->db->select('*');
        $this->db->where('position', $data['position']);

        foreach($data['company'] as $company)
        {
            $this->db->or_like('company', $company);
        }
        $query = $this->db->get('work_experience');

        echo "<pre>"; print_r($query->result());   // Result           
    }
}


Edit:

foreach($data['company'] as $company)
{
    $wh[] = "`company` LIKE '".$company."'";
}
$where = "(".implode("or", $wh).")";
$this->db->where($where, NULL, FALSE);

Upvotes: 1

k10gaurav
k10gaurav

Reputation: 462

Try using this sequence

Controller Code

$getcompanyList=$this->input->post('company'); //post variable company from view
$data['companyList']=explode(',', $getcompanyList);
//Query for model
$getResult=$this->myModel->somefunction($data);

Model Code

function somefunction($data){
$this->db->select('*');
$this->db->where('position','some position');
$this->db->where_in('company',$data['companyList']);
//or $this->db->or_where_in('company',$data['companyList']);
$getResult=$this->db->get('work_experience');
}

Now if you doesn't get the desired result then just print the last query using $this->db->last_query() or do profiling of your application and check what sql is being generated.

Upvotes: 2

Related Questions