+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: sql to filter data from dropdown boxes (/thread-27339.html)
sql to filter data from dropdown boxes - El Forum - 02-08-2010
[eluser]max123[/eluser]
I have 2 dropdown boxes called country and category. Default value for Both text boxes is 'All'. I have a problem with writing the query.
If a country is selected(eg:USA) but All from category then USA results should display without considering the category. If a category is selected (eg: phone) but All from country then results about phone should display without considering the country
How can do this. How can I write the sql query
Thanx
sql to filter data from dropdown boxes - El Forum - 02-08-2010
[eluser]richzilla[/eluser]
Im not sure i entirely understand your question but ill give it a shot anyway.
Your looking for a way to add a search function into a site, and add 'default' values into your dropdown boxes that don't necessarily appear in your database? The first bit is relatively easy. In your controller simply add an extra arbitrary value onto your array of values. 0 or -1 is a good idea as it doesn't need to be changed if you find yourself getting more results than you anticipated. (the -1 idea came from facebook). The sql query itself is a bit more complicated. In your model if you check that the returned value does not equal your default value, then add the relavant 'where' clause onto your query, and build it as a string. I wrote a very similar function for a project we were working on:
Code:
function db_search ($search_array)
{
$start_date = $search_array['start_date'];
$end_date = $search_array['end_date'];
$query_string = "SELECT issues.*, assignments.assignment_user_id, priorities.priority_code, status_list.status_code
FROM issues
LEFT JOIN assignments ON issues.issue_id = assignments.assignment_issue_id
LEFT JOIN priorities ON issues.issue_priority_id = priorities.priority_id
LEFT JOIN status_list ON issues.issue_status_id = status_list.status_id
WHERE issue_raised > ".mysql_real_escape_string($start_date) ."
AND issue_raised < ". mysql_real_escape_string($end_date);
if($search_array['raised_by'] != 0)
{
$query_string .= " AND issue_raised_by = ".mysql_real_escape_string($search_array['raised_by']);
}
if($search_array['open_closed'] == 1)
{
$query_string .= " AND issue_closed_by IS NULL";
}
elseif($search_array['open_closed'] == 2)
{
$query_string .= " AND issue_closed_by IS NOT NULL";
}
if($search_array['priority'] != 0)
{
$query_string .= " AND issue_priority_id = ".$search_array['priority'];
}
if($search_array['status'] != 0)
{
$query_string .= " AND issue_status_id = ".$search_array['status'];
}
if($search_array['assigned'] != 0)
{
$query_string .= " AND assignments.assignment_user_id = ".$search_array['assigned'];
}
$result = $this->db->query($query_string);
$data = $result->result();
return $data;
}sql to filter data from dropdown boxes - El Forum - 02-09-2010
[eluser]max123[/eluser]
This is my code. The conditions inside the if statements are not working. It means the filtering doesn't happen
function search_vacancies($data)
{
$key_word=$data['key_word'];
echo $category=$data['category'];
$city_state=$data['city_state'];
$sql = " SELECT tt_vac_vacancies.s_vac_designation,tt_rec_recruiters.s_rec_company_name,city_states.city_name,countries.country_name, tt_vac_vacancies.end_date, tt_vac_vacancies.i_vac_ID FROM tt_vac_vacancies
LEFT JOIN tt_rec_recruiters ON tt_vac_vacancies.l_vac_account_ID = tt_rec_recruiters.l_rec_ID
LEFT JOIN city_states ON tt_vac_vacancies.city_id = city_states.city_id
LEFT JOIN countries ON city_states.country_id = countries.country_id ";
if($category!=0)
$sql = $sql. "AND tt_vac_vacancies.l_vac_job_category = ". $category;
if($city_state!=0)
$sql = $sql. "AND tt_vac_vacancies.city_id = ". $city_state;
$query=$this->db->query($sql);
return $query;
}