[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()
{
}