mysql query for autocomplete not working -

mysql query for autocomplete not working -

i have next query used autocomplete school names user searching. if $query = har

select *, case when text '$query' 1 when text '$query%' 2 when text '%$query%' 3 end priority (select b.school_name `text`, 'school' `type`, b.slug `id`, n.neighbourhood 'params' schools b left bring together school_addresses ba on ( b.id = ba.school_id ) left bring together neighbourhoods n on ( ba.neighbourhood_id = n.id ) b.entity_status = 'active' , ba.city_id = '$city' , b.visibility != 'delisted' , (b.school_name '$query' or b.school_name '$query%' or b.school_name '%$query%') union select tg.option `text`, 'tags' `type`, tg.option `id`, tg.option 'params' tags t left bring together tag_options tg on ( t.id = tg.tag_id ) t.tag = 'cuisines' , (tg.option '$query' or tg.option '$query%' or tg.option '%$query%') union select category `text`, 'category' `type`, category `id`, category 'params' categories category '$query' or category '$query%' or category '%$query%' union select area `text`, 'area' `type`, id `id`, id 'params' areas city_id = '$city' , (area '$query' or area '$query%' or area '%$query%') union select district `text`, 'districts' `type`, id `id`, id 'params' districts city_id = '$city' , (district '$query' or district '$query%' or district '%$query%') union select neighbourhood `text`, 'neighbourhood' `type`, id `id`, id 'params' neighbourhoods city_id = '$city' , (neighbourhood '$query' or neighbourhood '$query%' or neighbourhood '%$query%') ) t1 1 order priority limit 5

this result yields

'text' 'type' 'id' 'params' 'priority' harvard mba harv-ny-city new york 2 harcum mba har-pa pa 2 harford mba harf-md maryland 2

my question how can search using both "name of school" 'text' in above query , "place of school" 'params' in above query. if $query = 'harford ma' should yield results this:

'text' 'type' 'id' 'params' 'priority' harford mba harf-md maryland 2 harford mba harv-ny-city new york 2 harford mba har-pa pa 2

i been playing whole day now, no results.

logic-> auto search functionality in site. user can seek school names or cities of schools. user can search both also. illustration there school iit in bombay, delhi, chennai. user can search like: "iit de" -> user type should auto finish , bring in iit delhi @ top search, other iit locations. in total should show 5 results.

generally, you'll have work on whatever programming language using phone call query.

you'll need split query separate words, , sanitize (to protect against sql injection, , remove % characters may influence "like" queries. removing punctuation if don't have in actual table nicely).

then you'll have build query dynamically, , utilize each of words query term in each of fields, example:

, (b.school_name '%$querywords[0]%' or b.school_name '%$querywords[1]%' or b.school_name '%$querywords[2]%')

...and on.

it's of import note don't need status in where like 'x%' or '%x' or '%x%'. redundant , unnecessarily slow query, of them included in like '%x%'. place makes difference if match exact or not in look build priority order by, each of conditions should pointed above - like '%$word%' each of words.

or may decide school names test $word[0], school places check $word[1] or on. depends if believe people come in queries such ma harford or harford ma.

the biggest challenge here build priority. suggest making priority higher higher number, not lower did, allow sum priorities given fields. utilize order priority desc.

the priority look going rather complex:

case when text = '$querywords[0]' or text = '$querywords[1]' 3 when text '$querywords[0]%' or text '$querywords[1]%' 2 when text '%$querywords[0]%' or text '%$querywords[1]%' 1 else 0 end + case when params = '$querywords[0]' or params = '$querywords[1]' 3 when params '$querywords[0]%' or params '$querywords[1]%' 2 when params '%$querywords[0]%' or params '%$querywords[1]%' 1 else 0 end priority

(of course of study if there more words there longer or parts in each when).

if want give more weight school name school place, should alter to:

case when text = '$querywords[0]' or text = '$querywords[1]' 12 when text '$querywords[0]%' or text '$querywords[1]%' 8 when text '%$querywords[0]%' or text '%$querywords[1]%' 4 else 0 end + case when params = '$querywords[0]' or params = '$querywords[1]' 3 when params '$querywords[0]%' or params '$querywords[1]%' 2 when params '%$querywords[0]%' or params '%$querywords[1]%' 1 else 0 end priority

this building priority base-4 number, matches on text, in to the lowest degree exact option, higher matches on params, in best match. if add together match criterion, multiply each of numbers 4 , add together @ end 3,2,1.

mysql