JQuery basic Typeahead implementation - doesn't work when a string matches the first or last character

Question

I believe the problem is with the .split () method. This does not work when characters match the beginning of a name or the end of a name.

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

For reference, I am using the Employees table from the Northwind database. For example, one of the employees is Andrew Fuller . If you start with andr , this will produce the corresponding results, but it will not change the appearance of the results by adding bold, italics and underlining HTML formatting to the results. However, if you start by entering letters that match somewhere in the middle of the string, for example, drew , then it will return the correct results and add HTML formatting. In addition, as you can see from the PHP below, you can enter the full name of the person, for example rew ful , and it will display the correct matching employees. However, after adding a character, it will stop adding formatting. I really don't know why.

In addition to this problem, I noticed that if you quickly enter a text input field, this often leads to multiple results. For example, type andrew very quickly in the text box. Or try uller into the text box quickly. This will bring unique results until you get to the end, where it sometimes brings you multiples of the same results.

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

Here PHP is northwind.php : (the problem is not in PHP - please check 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. Thank you in advance for information on the issues listed above.

Answer 1

You can find the full code here: http://jsfiddle.net

Will not change the substring of the substring of results

Problem: The appearance of the matching line does not change. The corresponding string must be encapsulated <span class="highlight"> .

Decision. This is due to case sensitivity when searching for a substring. This can be fixed using the 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>'); }); 

Enter the letters that are somewhere in the middle of the line

Problem. It does not appear that the expected result is returned if the query we entered starts somewhere in the middle of the line (employee name). For example: if we enter drew , Andrew Fuller will not be available in the response data.

I think the problem is with the server code. CMIIW. I looked briefly at your PHP code, but cannot find where the problem might occur. I cannot check your PHP code at the moment.

Multiple Result Problem Due to Quick Input

This issue occurred because the previous AJAX request is still active. If we quickly type "ann," there will be 3 Ann Dodsworth and 2 Andrew Fuller.

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

You can find the full code here: http://jsfiddle.net

Answer 2

You must trim the string in JavaScript, the Typehead value to split the string obtained with the results, see working example.

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

And you need to trim the string in PHP.

When you do a rew ful search in MySQL, the query will check each name with a run with the one followed by rew, space, etc.

So, when you pass rew full (space) to MySQL, you will check everything that starts with something, followed by rew (space) full (space) , and I and I are sure all your names are truncated, when you insert them.

So please update your code to this:

 <?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 the Like to compare command is not so efficient, and does not use the MySQL full-text search function.

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

You must create a field that contains text as a full text index, and you can perform queries such as:

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

Answer 3

Have you considered case sensitivity and case insensitive? If you fill in the “Andr” field, it is highlighted; if you search for “andr”, it is not. What for? because “Andrey” is not the same as “Andrew”. a and A are different characters, therefore, will not match. Obviously, your database ignores this fact and makes it case insensitive ( https://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html : "The default character set and collation is latin1 and latin1_swedish_ci, so non-binary string comparisons are not case sensitive by default. "), and also, obviously, your .split function distinguishes between lowercase and uppercase. Therefore, if you are looking for the value of an input field, it is different from the value returned by your ajax call. Now you need to do the following:

• enter a line in the search bar and resultstring
• find the position where the search string appears in the result line
• insert your formatting at this position in the source line of the result and complete the formation in searchstring.length in the original line of the result

The following snapshot is already working, but displays everything in lower case, demonstrating its random 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>