3

I have made a search function for finding staff members based on a first name search, here is the code for that.

search.php

<?php
include ('connect-db.php');
if (isset($_GET ['forename'])){
$forename = $_GET['forename'];

$forename_escaped = mysqli_real_escape_string ($db, $forename);
$userquery = mysqli_query($db, "SELECT * FROM staff WHERE forename = '$forename_escaped'");
if (mysqli_num_rows($userquery) == null ) echo ('NO RESULTS FOUND IN DATABASE');{
    ?>

But what I would like this function to do is to return results even when in the search box the first name was misspelt by a letter or only partially entered.

e.g. User input - Search: Jennif -->[SEARCH FUNCTION] Returns results for 'Jennifer' OR User input - Search: Jjennifer --> [SEARCH FUNCTION] Returns results for 'Jennifer'

How could I perform an operation like this? I was thinking of using a 'LIKE' function but so far haven't been able to figure it out.

cjhines
  • 1,148
  • 3
  • 16
  • 32
dev_py_uk
  • 418
  • 1
  • 5
  • 20
  • Glad you mentioned that actually I was just going through my pages a couple weeks ago adding parameters and completely forgot about this one. – dev_py_uk Oct 21 '15 at 08:38

2 Answers2

4

You want to find the difference between strings, also known as Levenshtein Distance.

https://en.wikipedia.org/wiki/Levenshtein_distance

For a implementation into mysql you might want to concider following post:

How to add levenshtein function in mysql?

Taken the Levenshtein implementation within the linked page your query should look like this:

$userquery = mysqli_query($db, "SELECT * FROM staff WHERE levenshtein(forename, '".$forename_escaped."') < 2");

This means the levenshtein distance is lower then 2.

Community
  • 1
  • 1
Jochen Schultz
  • 456
  • 5
  • 16
  • Okay, I've read up on Levenshtein distance and had a look over the other SO post about it you shared, My next question, How can that be executed in php within the current search function? – dev_py_uk Oct 21 '15 at 08:50
  • Thanks for the edit, last question though (I promise) I still don't see how/where I can add that code from the other link – dev_py_uk Oct 21 '15 at 09:03
  • You just need to copy and paste the code and execute it on your mysql server once. This way you add a function to mysql server. For more information on that you might want to read the documentation for stored procedures... https://dev.mysql.com/doc/refman/5.0/en/create-procedure.html – Jochen Schultz Oct 21 '15 at 09:06
  • Levenshtein is an expensive algorithm to run across a large number of records, so you will need to find a way to reduce the number of records you search for misspellings (e.g. pre-filter by dept). If performance becomes an issue you'll need to read up on [Levenshtein Automata](http://blog.notdot.net/2010/07/Damn-Cool-Algorithms-Levenshtein-Automata) (data structures for solving this problem efficiently — requires preindexing) – Peter Dixon-Moses Oct 23 '15 at 14:38
0

For anyone wanting a similar function to this with their search:

I used a Wildcard to allow the search to get results without an entire name typed in. here's what it looks like:

$userquery = mysqli_query($db, "SELECT * FROM staff WHERE forename LIKE '$forename_escaped%'");

Hope this helps people in the future.

dev_py_uk
  • 418
  • 1
  • 5
  • 20