yaragallamurali Posted February 5, 2015 Share Posted February 5, 2015 Hi i have a table called hospitails. the table structure is as below Hospitals:- Hospital_id Bigint name varchar(45) city_id bigint district_id bigint state_id bigint now i need to search hospitals based on name,city_id,district_id,state_id. the query must cntain all the 4 fields. when user does not provide any of the value all the hospitals with all the names from all the cites,districts,states has to be listed. when user provides partial name and ignores the rest of the three fileds, it has to provide all the hospitals that starts with that name from all cities,districts,states, when user ignores the other 3 fields and provides the city_id then all the hospitals should be listed from that city Like wise. for varchar columns if we apply 'like' then it does this kind of things but how to do this when there are bigint columns? I need this query. please help. Link to comment Share on other sites More sharing options...
justsomeguy Posted February 5, 2015 Share Posted February 5, 2015 You can probably convert the integers to strings and then use like, but that's not going to be good for performance. Are people actually typing in the city ID, or are they typing the city name? Link to comment Share on other sites More sharing options...
yaragallamurali Posted February 6, 2015 Author Share Posted February 6, 2015 city, district and state are drop downs. where id is the value and display name is the string. so when the form is submitted i get the id only at the back end. Link to comment Share on other sites More sharing options...
JamesB Posted February 6, 2015 Share Posted February 6, 2015 (edited) You don't need to use LIKE for those int checks, just do a = comparison. This untested code goes beyond your spec requirements, eg. allowing text filter at same time as district_id filter Edit: Just realized this thread isn't in the PHP section lol.. <?php$hospitalName = 'abcde';$cityId = 0;$districtId = -1;$stateId = 0; $where_ands = array();if($cityId != -1){$where_ands[] = 'city_id = '.$cityId;}if($districtId != -1){$where_ands[] = 'district_id = '.$districtId;}if($stateId != -1){$where_ands[] = 'state_id = '.$stateId;}if($hospitalName != ''){$where_ands[] = 'name LIKE %'.escape_for_like($hospitalName).'%';} $query = 'SELECT * FROM hospitals'.(count($where_ands) > 0 ? ' WHERE '.implode(' AND ', $where_ands) : ''); Edited February 6, 2015 by JamesB Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now