MYSQLI Query ORDER by most amount of matching characters

Question

I have created a search function on my project utilizing mysqli LIKE query.

My final hurdle is the ORDER in which it outputs.

Current Scenario:

if(isset($_GET['userInput']))
{
$search = $_GET['userInput'];
$search = explode(" ", $search);
if($search[0] != ''){
    $firstterm = $search[0];
}else{
    $firstterm = '@@@';
}
if($search[1] != ''){
    $secondterm = $search[1];
}else{
    $secondterm = '@@@';
}}

$contact = $mysqli->query("
SELECT * FROM contact WHERE 
f_name LIKE '%$firstterm%'
OR l_name LIKE '%$firstterm%'

OR f_name LIKE '%$secondterm%'
OR l_name LIKE '%$secondterm%'

order by id LIMIT 5");

My question is, is there a way to ORDER by the most amount of matching characters as apposed to id. The reason being if I type 'C Blogs' looking for 'Craig Blogs' the 'C' will register a hit on everyone of my contacts because of the email address '.[c]om'.

So if I can get it to ORDER by most amount of matching characters (the added amount from both explodes where there is more than 1) the top result should theoretically be 'Craig Blogs'.

I suggest you sort the results in PHP, using the `levenshtein` function to compare the search terms. — Barmar, Nov 20 '15 at 02:47
@Barmar Thank you for this suggestion, I will look in to it. If you could point me in the direction of it being used as an example that would be greatly appreciated. — Bjaeg, Nov 20 '15 at 02:55
No idea where to find examples. That's what search engines are for. Basically, you just want to use `usort`, and write a comparison function that compares `levenshtein($a)` with `levenshtein($b)`. — Barmar, Nov 20 '15 at 02:58
See http://stackoverflow.com/questions/13909885/how-to-add-levenshtein-function-in-mysql — Barmar, Nov 20 '15 at 03:00
Wow, thank you for taking the extra effort. When I have a working solution should I post it as the answer or could I somehow set your comment as the answer at that point? — Bjaeg, Nov 20 '15 at 03:03

Gordon Linoff · Accepted Answer · 2015-11-20T03:52:17.223

1

You can count the number of matches using +. In MySQL, a boolean expression is treated as a number with 1 being true and 0 being 1:

ORDER BY ((f_name LIKE '%$firstterm%') +
          (l_name LIKE '%$firstterm%') +
          (f_name LIKE '%$secondterm%') +
          (l_name LIKE '%$secondterm%')
         ) desc

EDIT:

If you want the number of matching characters, you can start with the length of the search terms. However, terms could appear more than once. So:

ORDER BY ((length(replace(f_name, 'firstterm', concat('firstterm', 'x'))) - length(f_name)) +
          (length(replace(l_name, 'firstterm', concat('firstterm', 'x'))) - length(l_name)) +
          (length(replace(f_name, 'secondterm', concat('secondterm', 'x'))) - length(f_name)) +
          (length(replace(l_name, 'secondterm', concat('secondterm', 'x'))) - length(l_name))
         ) desc

edited Nov 20 '15 at 03:52

answered Nov 20 '15 at 02:45

Gordon Linoff

1,242,037
58
646
786

1

That's the number of terms that matches, not the number of characters that match. – Barmar Nov 20 '15 at 02:46
@Barmar correct. It is still matching 2x terms (including the email '.com') and therefore c blogs still means it is ordering by id. – Bjaeg Nov 20 '15 at 02:52
@Gordon Linoff This is still not working it seems to be doing the same thing as your original submission. – Bjaeg Nov 20 '15 at 03:07
@Bjaeg . . . No. It should be counting the number of appearances of each term in each field and adding them together. That is the number of matching characters. – Gordon Linoff Nov 20 '15 at 03:10
@GordonLinoff I have tried your submission, I have tried changing it to the '$' infront of firstterm and secondterm I have tried it as this; '%$firstterm%', '%$secondterm%' etc. Am I supposed to replace the 'x' with something? – Bjaeg Nov 20 '15 at 03:32
@Bjaeg . . . No. Anything with one character should work. This construct counts the number of times that each term occurs in each of the fields. – Gordon Linoff Nov 20 '15 at 03:33

MYSQLI Query ORDER by most amount of matching characters

1 Answers1