![]() |
sql to filter data from dropdown boxes - Printable Version +- 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: sql to filter data from dropdown boxes (/showthread.php?tid=27339) |
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) 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; } |