jQuery basic Typeahead implementation - not working when string matches first or last character

Question

I believe the issue involves the .split() method. This doesn't appear to be working when the characters are matching the beginning of the name or the end of the name.

Check out the JSFiddle here: http://jsfiddle.net/5uebxvex/

For reference, I'm using the Employees table from the Northwind database. For example, one of the employees is Andrew Fuller. If you begin by typing in andr it will bring up relevant results, but it will not modify the appearance of the results by adding bold, italic, and underline HTML formatting to the results. However, if you begin by typing in letters that match somewhere in the middle of the string, such as drew, then it brings up correct results and adds the HTML formatting. Also as you can see from the PHP below, you are able to type in the person's full name like rew ful and it will bring up the correct matching Employees. However, it will stop adding formatting once you add the character after the space. I'm really not sure why.

In addition to this problem, I've noticed that if you type in the input text field quickly, it will often bring you multiples of the same results. For example, type in andrew very quickly into the text field. Or try typing in uller into the text field quickly. It will bring up unique results until you get to the end where it will sometimes bring you multiples of the same results.

What's going on here? Can someone explain what the issue is?

Here's the PHP - northwind.php: (the issue does not reside in the PHP - please checkout the JSFiddle above)

<?php
header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *');

$db_user = 'user';
$db_pass = 'pass';
$db_name = 'name';
$db_host = 'host';

$conn = new mysqli($db_host, $db_user, $db_pass, $db_name);
if ($conn->connection_error) { die('Connection error: '. $conn->connection_error); }

$data = $_GET ? $_GET : $_POST;

if (strlen($data['name']) > 0) {
    $sql = '
        SELECT
            CONCAT(FirstName, " ", LastName) AS name,
            EmployeeID AS id
        FROM
            Employees
        WHERE
            FirstName LIKE "%'. $data['name'] .'%"
        OR
            LastName LIKE "%'. $data['name'] .'%"
        OR
            CONCAT(FirstName, " ", LastName) LIKE "%'. $data['name'] .'%"
    ';
    $result = $conn->query($sql) OR die('Query error: '. mysqli_error($conn));
    if ($result->num_rows > 0) {
        $json = array();
        while ($row = $result->fetch_assoc()) {
            $json[] = array('id' => $row['id'], 'name' => $row['name']);
        }
        print json_encode($json);
    }
    else { print '[]'; }
}

?>

Check out the JSFiddle above for the rest of the code. Thanks in advance for your insight on the issues listed above.

Answer 1

Please find the complete code here: http://jsfiddle.net

Won't modify the matched sub string of results

Problem: The appearance of matched sub string is not changed. The matched sub string should be encapsulated by <span class="highlight">.

Solution: This happens due to the case sensitivity when searching the sub string. This can be fixed with code below :

$(response).each(function(i, e) {
    q = $('#typeahead').val();
    //Find substring,encapsulate each substring with span.highlight.
    var match = e.name.replace(new RegExp(q,"i"), function(m){
        return "<span class='highlight'>"+m+"</span>"
    });
    $('#list').append('<li class="employeeID-'+ e.id +'">'+ match +'</li>');
});

Type in letters that match somewhere in the middle of the string

Problem: It looks like the expected result doesn't get returned if the query we typed in starts somewhere in the middle of the string (employee's name). For example : if we type in drew, Andrew Fuller will not be available in response data.

I think the problem resides in server code. CMIIW. I've briefly looked at your PHP code but can't find where the problem might reside. I am not able to test your PHP code at the moment.

Problem of multiple same result due to quick typing

This problem occurred as the result of previous AJAX request that still active. If we quickly type 'ann', there will be 3 Anne Dodsworth and 2 Andrew Fuller.

You need abort the previous AJAX request by using xhr.abort(). xhr is the previous AJAX request (saved in to a variable).

Please find the complete code here: http://jsfiddle.net

Answer 2

You have to trim the String on JavaScript, the value of Typehead in order to split the string you got from results, see a working example.

http://jsfiddle.net/5uebxvex/3/

And you have to trim your string on PHP.

When you're searching for rew ful on MySQL the query will check every name with starts with whatever followed by rew a space ful and whatever.

So when you pass rew full (space) on MySQL you will check about anyone which starts with whatever followed by rew(space)full(space) whatever and I'm pretty sure all your names are trimmed when you are inserting them.

So please update your code to this one :

<?php
header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *');

$db_user = 'user';
$db_pass = 'pass';
$db_name = 'name';
$db_host = 'host';

$conn = new mysqli($db_host, $db_user, $db_pass, $db_name);
if ($conn->connection_error) { die('Connection error: '. $conn->connection_error); }

$data = $_GET ? $_GET : $_POST;

if (strlen($data['name']) > 0) {
    $name = trim($data['name']);
    $sql = '
        SELECT
            CONCAT(FirstName, " ", LastName) AS name,
            EmployeeID AS id
        FROM
            Employees
        WHERE
            FirstName LIKE "%'. $name .'%"
        OR
            LastName LIKE "%'. $name .'%"
        OR
            CONCAT(FirstName, " ", LastName) LIKE "%'. $data['name'] .'%"
    ';
    $result = $conn->query($sql) OR die('Query error: '. mysqli_error($conn));
    if ($result->num_rows > 0) {
        $json = array();
        while ($row = $result->fetch_assoc()) {
            $json[] = array('id' => $row['id'], 'name' => $row['name']);
        }
        print json_encode($json);
    }
    else { print '[]'; }
}

?>

But using Like to compare string is not that efficient rather than using MySQL Full Text Search feature.

https://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

You have to make the field which contains the text as a full text index, and than you can perform queries like :

SELECT * FROM `peoples` WHERE MATCH (firstname) AGAINST ('*andrew*' IN BOOLEAN MODE);

Answer 3

Have you considered case sensitive vs case insensitive searching? If you fill the field with 'Andr' it gets highlighted, if you search for 'andr' it doesnt. Why? because 'Andrew' is not the same as 'andrew'. a and A are different chars, hence, will not match the same. Obviously your database ignores that fact and does a case insensitive search (https://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html: "The default character set and collation are latin1 and latin1_swedish_ci, so nonbinary string comparisons are case insensitive by default.") and also obvisously your .split function does differentiate between lowercase and uppercase. So if you look for the value of the input-field it is different than the value returned from your ajax-call. what you have to do now is the following:

• lowercase your searchstring and resultstring
• search for the position where the searchstring appears in the resultstring
• insert your formating at that position in the original resultstring and terminate your formating at searchstring.length in the original resultstring

the following snipped already works, but displays everything in lowercase, demonstrating, its a case-problem.

	$(document).ready(function() {
		$('#typeahead').on({
			input: function() {
				$('#list li').remove();
				$.ajax({
					url: 'http://smpl-php.com/jquery/test/northwind.php',
                    method: 'post',
					data: { 'name': $('#typeahead').val() },
					dataType: 'json',
					success: function(response) {
						$(response).each(function(i, e) {
							var match = e.name.toLowerCase()
                            				.split($('#typeahead').val().toLowerCase())
                            				.join('<span class="highlight">'+ $('#typeahead').val() +'</span>');
							$('#list').append('<li class="employeeID-'+ e.id +'">'+ match +'</li>');
						});
					},
					error: function(error) {}
				});
			}
		});
	});

.highlight {
	font-weight: bold;
	text-decoration: underline;
	font-style: italic;
}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
<input id="typeahead" type="text" />
<ul id="list"></ul>