CODEWITHSUNDEEP
phpmysqlcodeigniter
user11831635
user11831635

Reputation:

mysql query to check a field exist in an array in codeigniter

I post an array of skills from the form and i need to fetch list of candidates list related to the elements in array in codeigniter.

Controller

  $data = array(
        "mode"          => $this->input->post("mode"),
        "designation"   => $this->input->post("designation"),
        "notice_period" => $this->input->post("notice_period"),
        "skill"         => $this->input->post("skills"),
        "cities"        => $this->input->post("city")
    );

    $this->load->model("search_model");
    $this->load->model("candidate_model");
    $data["candidate"] = $this->search_model->fetch_candidate($data);
    $data["designation"] = $this->candidate_model->fetch_designation();
    $this->load->view('candidate_profiles', $data);

model

function fetch_candidate($data)
{
    $mode = $data['mode'];
    $designation = $data['designation'];
    $notice_period = $data['notice_period'];
    $skill = $data['skill'];
    $skills = join(",",$skill);
    $city = $data['cities'];
    $query = $this->db->query("SELECT DISTINCT(U.user_id), U.firstname, U.lastname, U.username, U.usertype, U.email, U.phone, U.address, U.profile_image, C.name AS country,C.id AS countryid, S.name AS state,S.id AS stateid, A.housename, A.street, A.area, A.po, A.city, CJ.designation, CJ.resume, CJ.biography, CJ.hiring_mode, CJ.notice_period, CJ.current_CTC, CJ.expected_CTC, D.designation_id AS desig, D.designation AS desig_name
   FROM users AS U
   LEFT JOIN address AS A ON U.user_id=A.cand_id
   LEFT JOIN cand_job_details AS CJ ON CJ.cand_id=U.user_id
   LEFT JOIN designations AS D ON D.designation_id=CJ.designation
   LEFT JOIN countries AS C ON C.id=A.country
   LEFT JOIN states AS S ON S.id=A.state
   LEFT JOIN user_skills AS US ON U.user_id=US.user_id
   LEFT JOIN user_interested_cities AS UC ON U.user_id=UC.user_id
   WHERE D.designation LIKE '$designation' && (CJ.hiring_mode='2' || CJ.hiring_mode='$mode')  && CJ
   .notice_period<='$notice_period' && UC.city='$city' && US.skill IN(".implode(",", $skills).")
   ");
    return $query;
}

How to implement this on codeigniter? Thanks in advance

Upvotes: 1

Views: 215

Answers (2)

Aksen P
Aksen P

Reputation: 4599

You need try to add ' around each argument. After it should looks like IN ('PHP','AngularJS'), because it is a string value and datatype is varchar as well. 

$skills = "'";
foreach($skill as $item) {
            $skills .= $item."',";
}
$skills = trim($skills,",")

Upvotes: 1

Atural
Atural

Reputation: 5439

Codeigniter comes with a built in Querybuilder - i strongly suggest to use it, because it makes your life much easier, and protects you against SQL injections - which is clearly a problem in your case.

Try the following

function fetch_candidate($data)
{
    $this->db
        ->select('SELECT DISTINCT(U.user_id), U.firstname, U.lastname, U.username, U.usertype, U.email, U.phone, U.address, U.profile_image, C.name AS country,C.id AS countryid, S.name AS state,S.id AS stateid, A.housename, A.street, A.area, A.po, A.city, CJ.designation, CJ.resume, CJ.biography, CJ.hiring_mode, CJ.notice_period, CJ.current_CTC, CJ.expected_CTC, D.designation_id AS desig, D.designation AS desig_name')
        ->from('users U')
        ->join('address A', 'U.user_id=A.cand_id', 'left')
        ->join('cand_job_details CJ', 'CJ.cand_id=U.user_id', 'left')
        ->join('designations D', 'D.designation_id=CJ.designation', 'left')
        ->join('countries C', 'C.id=A.country', 'left')
        ->join('states S', 'S.id=A.state', 'left')
        ->join('user_skills US', 'U.user_id=US.user_id', 'left')
        ->join('user_interested_cities UC', 'U.user_id=UC.user_id', 'left');

    if (isset($data['designation']) && !empty($data['designation']))
    {
        $this->db->like('D.designation', $data['designation']);
    }   

    if (isset($data['mode']) && !empty($data['mode']))
    {
        $this->db
            ->group_start()
                ->where('CJ.hiring_mode', 2)
                ->or_where('CJ.hiring_mode', $data['mode'])
            ->group_end();
    }

    if (isset($data['notice_period']) && !empty($data['notice_period']))
    {
        $this->db->where('CJ.notice_period <=', $data['notice_period']);
    }

    if (isset($data['city']) && !empty($data['city']))
    {
        $this->db->where('UC.city', $data['city']);
    }

    if (isset($data['skills']) && is_array($data['skills']))
    {
        $this->db->where_in('US.skill',  $data['skills']);
    }

    return $this->db->get();
}

Upvotes: 1

Related Questions