+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: I need some help making a search function involving mulitple paramters (/showthread.php?tid=52612)
I need some help making a search function involving mulitple paramters - El Forum - 06-18-2012
[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()
{
}I need some help making a search function involving mulitple paramters - El Forum - 06-19-2012
[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));I need some help making a search function involving mulitple paramters - El Forum - 06-19-2012
[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!