Welcome Guest, Not a member yet? Register   Sign In
I need some help making a search function involving mulitple paramters
#1

[eluser]RMinor[/eluser]
I am trying to create a search feature into one of my projects. I have a website that allows employers to search for job seekers based on position desired, skills, city, and state. The skills are stored as a textarea in my database as outlined below.

Table: user
user_id, user_city, user_state, etc.

Table: user_resume
user_resume_skills, user_resume_user, etc.

How would I create one giant query to handle all of this and is that even the best way?

Here is what I have so far. The model is going to accept parameters and do all of the heavy lifting.

Search_model.php
Code:
class Search_model extends CI_Model
{

/**
     * Get an array of all state values from the database.
     * @return $dropdown array - the array of states and their values
     */
public function getJobSeekers()
{
  $sql = "SELECT * FROM user";
  $parameters = array();
  if ($this->input->post('position')) {
   $sql .= " INNER JOIN user_resume ON user_id = user_resume_user WHERE user_resume_position = ?";
   $parameters[] = $position;
   $where = TRUE;
  }
  if ($this->input->post('state')) {
   if ($where == TRUE) {
    $sql .= " AND user_state = ?";
    $parameters[] = $state;
   } else {
    $sql .= " WHERE user_state = ?";
    $parameters[] = $state;
    $where = TRUE;
   }
  }
  if ($this->input->post('city')) {
   if ($where == TRUE) {
    $sql .= " AND user_city = ?";
    $parameters[] = $city;
   } else {
    $sql .= " WHERE user_city = ?";
    $parameters[] = $city;
    $where = TRUE;
   }
  }
  if ($this->input->post('skills')) {
   $skills = $this->input->post('skills');
   $skill = explode(',', $skills);
   foreach ($skill as $value) {
    if ($where == TRUE) {
     $sql .= " AND user_resume_skills LIKE ?";
     $parameters[] = $value;
    } else {
     $sql .= " WHERE user_resume_skills LIKE ?";
     $parameters[] = $value;
     $where = TRUE;
    }
   }
  }
  $sql .= " LIMIT ?, ?";
  $parameters[] = $offset;
  $parameters[] = $limit;
  $query = $this->db->query($sql, array($parameters));
  $result = $query->result_array();
  return $result;
}

public function getTotalRows()
{
  
}
#2

[eluser]Matalina[/eluser]
I would use active record it will eliminate your need to write if else statements, instead you can just write an if post item exists then do this. No other if/else required.

Code:
public function getJobSeekers()
{
list($this->input->post());
if ($this->input->post('position')) {
  $this->db->join('user_resume','user_id = user_resume_user WHERE user_resume_position = '.$position);
}
if ($this->input->post('state')) {
    $this->db->where('user_state',$state);
}
if ($this->input->post('city')) {
    $this->db->where('user_city',$city);
}
if ($this->input->post('skills')) {
    $skill = explode(',', $skills);
    foreach ($skill as $value) {
   $this->db->like('user_resume_skills',$value);
    }
}
$this->db->limit($limit,$offset);
$query = $this->db->get('user');
$result = $query->result_array();
return $result;
}

You could also use this at the top:

Code:
list($this->input->post(NULL, TRUE));
#3

[eluser]RMinor[/eluser]
Thank you. I thought about using active record before, but I guess I will now. I am going to implement this code when I get home. Thanks again!




Theme © iAndrew 2016 - Forum software by © MyBB