CODEWITHSUNDEEP
phpmysqlselectmatch
TheLearner
TheLearner

Reputation: 2863

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?

Upvotes: 1

Views: 242

Answers (2)

Toki
Toki

Reputation: 294

Using $search = ''.$search.'%' will show result, that matches the starting value.

Upvotes: 0

dave
dave

Reputation: 64657

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

Upvotes: 1

Related Questions