TheLearner TheLearner - 1 month ago 6
MySQL Question

Perform partial search on MySQL table when exact match may be available

I am running the following SQL statement from a PHP script:

SELECT PHONE, COALESCE(PREFERREDNAME, POPULARNAME) FROM distilled_contacts WHERE PHONE LIKE :phone LIMIT 6


As obvious, the statement returns the first 6 matches against the table in question. The value I'm binding to the
:phone
variable is goes something like this:

$search = '%'.$search.'%';


Where,
$search
could be any string of numerals. The wildcard characters ensure that a search on, say
918
, would return every record where the
PHONE
field contains
918
:


  1. 9180078961

  2. 9879189872

  3. 0098976918

  4. 918

  5. ...



My problem is what happens if there does exist an entry with the value that matches the search string exactly, in this case
918
(the 4th item in the list above). Since there's a
LIMIT 6
, only the first 6 entries would be retrieved which may or may not contain the one with the exact match. Is there a way to ensure the results always contain the record with the exact match, on top of the resulting list, should one be available?

Answer

You could use an order by to ensure the exact match is always on top:

ORDER BY CASE WHEN PHONE = :phone THEN 1 ELSE 2 END