mysql query for autocomplete not working

Question

I have following query which is used to autocomplete school names that user is searching. If $query = har

SELECT *, 
       CASE 
         WHEN text LIKE '$query' THEN 1 
         WHEN text LIKE '$query%' THEN 2 
         WHEN text LIKE '%$query%' THEN 3 
       end AS priority 
FROM   (SELECT b.school_name AS `text`, 
               'school'      AS `type`, 
               b.slug          AS `id`, 
               n.neighbourhood AS 'params' 
        FROM   schools b 
               LEFT JOIN school_addresses ba 
                      ON ( b.id = ba.school_id ) 
               LEFT JOIN neighbourhoods n 
                      ON ( ba.neighbourhood_id = n.id ) 
        WHERE  b.entity_status = 'active' 
               AND ba.city_id = '$city' 
               AND b.visibility != 'delisted' 
               AND (b.school_name LIKE '$query' 
                    OR b.school_name LIKE '$query%' 
                    OR b.school_name LIKE '%$query%') 
        UNION 
        SELECT tg.OPTION AS `text`, 
               'tags'    AS `type`, 
               tg.OPTION AS `id`, 
               tg.OPTION AS 'params' 
        FROM   tags t 
               LEFT JOIN tag_options tg 
                      ON ( t.id = tg.tag_id ) 
        WHERE  t.tag = 'Cuisines' 
               AND (tg.OPTION LIKE '$query' 
                    OR tg.OPTION LIKE '$query%' 
                    OR tg.OPTION LIKE '%$query%') 
        UNION 
        SELECT category   AS `text`, 
               'category' AS `type`, 
               category   AS `id`, 
               category   AS 'params' 
        FROM   categories 
        WHERE  category LIKE '$query' 
               OR category LIKE '$query%' 
               OR category LIKE '%$query%' 
        UNION 
        SELECT area   AS `text`, 
               'area' AS `type`, 
               id     AS `id`, 
               id     AS 'params' 
        FROM   areas 
        WHERE  city_id = '$city' 
               AND (area LIKE '$query' 
                    OR area LIKE '$query%' 
                    OR area LIKE '%$query%') 
        UNION 
        SELECT district    AS `text`, 
               'districts' AS `type`, 
               id          AS `id`, 
               id          AS 'params' 
        FROM   districts 
        WHERE  city_id = '$city' 
               AND (district LIKE '$query' 
                    OR district LIKE '$query%' 
                    OR district LIKE '%$query%') 
        UNION 
        SELECT neighbourhood   AS `text`, 
               'neighbourhood' AS `type`, 
               id              AS `id`, 
               id              AS 'params' 
        FROM   neighbourhoods 
        WHERE  city_id = '$city'
               AND (neighbourhood LIKE '$query' 
                    OR neighbourhood LIKE '$query%' 
                    OR neighbourhood LIKE '%$query%') 
        ) AS t1 
WHERE 1 
ORDER  BY priority
LIMIT  5

This is the result it 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 is how can i search using both "Name of the school" 'text' in above query and "Place of the school" 'params' in above query. Like if $query = 'harford ma' Then it should yield results like 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 with this for almost whole day now, with no results.

Logic-> This is an auto search functionality in my site. User can try to look for school names or cities of those schools. But user can also search both also. For example there is School iit in bombay, delhi, chennai. User can search like: "iit de" -> as soon as user type this it should auto complete and bring in iit Delhi at top search, then other iit locations. In total it should show 5 results.

Answer 1

Generally, you'll have to work on this from whatever programming language you are using to call this query.

You'll need to split the query into separate words, and sanitize it (to protect against SQL injection, and to remove % characters that may influence your "like" queries. Removing punctuation if you don't have it in the actual table would do this nicely).

Then you'll have to build the query dynamically, and use each of the words as a query term in each of your fields, for example:

           AND (b.school_name LIKE '%$queryWords[0]%' 
                OR b.school_name LIKE '%$queryWords[1]%' 
                OR b.school_name LIKE '%$queryWords[2]%') 

...and so on.

It's important to note that you don't need the condition in the WHERE to be LIKE 'x%' OR LIKE '%x' OR like '%x%'. This is redundant and will unnecessarily slow the query, as all of them are included in LIKE '%x%'. The only place where it makes a difference if the match is exact or not is in the expression where you construct the priority to order by, so each of the where conditions should be as I pointed above - just LIKE '%$word%' for each of the words.

Or you may decide that for school names you only test $word[0], for school places you only check $word[1] or so on. It depends if you believe people will enter queries such as ma harford or only harford ma.

The biggest challenge here is to construct the priority. I suggest making the priority higher the higher the number, not the lower as you did, as this will allow you to sum priorities for given fields. So use ORDER BY priority DESC.

The priority expression itself is going to be rather complex:

CASE WHEN text = '$queryWords[0]' OR text = '$queryWords[1]' THEN 3 
     WHEN text LIKE '$queryWords[0]%' OR text LIKE '$queryWords[1]%' THEN 2 
     WHEN text LIKE '%$queryWords[0]%' OR text LIKE '%$queryWords[1]%' THEN 1
     ELSE 0 
END 
+
CASE WHEN params = '$queryWords[0]' OR params = '$queryWords[1]' THEN 3 
     WHEN params LIKE '$queryWords[0]%' OR params LIKE '$queryWords[1]%' THEN 2 
     WHEN params LIKE '%$queryWords[0]%' OR params LIKE '%$queryWords[1]%' THEN 1
     ELSE 0 
END 
AS priority 

(Of course if there are more words then there will be longer OR parts in each WHEN).

If you want to give more weight to the school name than to the school place, then you should change this to:

CASE WHEN text = '$queryWords[0]' OR text = '$queryWords[1]' THEN 12 
     WHEN text LIKE '$queryWords[0]%' OR text LIKE '$queryWords[1]%' THEN 8
     WHEN text LIKE '%$queryWords[0]%' OR text LIKE '%$queryWords[1]%' THEN 4
     ELSE 0 
END 
+
CASE WHEN params = '$queryWords[0]' OR params = '$queryWords[1]' THEN 3 
     WHEN params LIKE '$queryWords[0]%' OR params LIKE '$queryWords[1]%' THEN 2 
     WHEN params LIKE '%$queryWords[0]%' OR params LIKE '%$queryWords[1]%' THEN 1
     ELSE 0 
END 
AS priority 

This is essentially building the priority as a base-4 number, so that matches on text, even in the least exact option, are always higher than matches on params, even in the best match. If you add another match criterion, multiply each of the numbers by 4 and add it at the end with 3,2,1.