how to apply like on bigint columns

[yaragallamurali]

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.

 

 

[justsomeguy]

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?

[yaragallamurali]

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.

[JamesB]

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