Jquery autocomplete UI with PHP returns only whole words

Question

I’m in my first project with php + Mysql with a search input from words using Jquery UI. Every tutorial I’ve done, display the words as the user type, such as a substring. Thus:

Being 'as' a substring of ASp, bASic etc...

Already in my code does not occur like this. It only displays when it gives a match in the whole word.

For example, I start typing: Abac and nothing is displayed.

Only when I finish typing does the word appear:

I wanted to display all substrings with a minimum of three letters minLength: 3. What I’m missing?

my code index.php:

<input type="text" name="word" class="j_autocomplete" id="word_input">

(...)
// com o script lá embaixo
<script type="text/javascript">
  $(function() {
    // autocomplete
    $(".j_autocomplete").autocomplete({
      source: 'src/search.php',
      minLength: 3
    });
  });
</script>

the search.php is like this:

<?php
require('../config.php'); // conexão com o 'db' funcionando!

$return_arr = array();
$userInput = $_GET['term'];

$search = $mysql->prepare('SELECT * FROM `palavras` WHERE `palavra` LIKE ? ORDER BY palavra ASC');

$search->bind_param('s', $userInput);
$search->execute();

$getResult = $search->get_result();


while ($row = $getResult->fetch_assoc()) :

  $return_arr[] =  $row['palavra'];
endwhile;

echo json_encode($return_arr);

What I’m missing?

Answer 1

When using the LIKE operator to search for parts of the text you need to inform a 'joker', in the case of Mysql and in most relational databases you can use % as a joker.

For example, to search my name using like, I could use:

SELECT * FROM cliente WHERE nome LIKE 'JHO%'

Mysql will interpret this expression to return all clients whose name field starts with 'JHO', which can be JHONNY, JHONY, JHON.

In this case, you can consult in two ways:

$search = $mysql->prepare("SELECT * FROM `palavras` WHERE `palavra` LIKE '%?%' ORDER BY palavra ASC");

$search->bind_param('s', $userInput);
$search->execute();

or

$search = $mysql->prepare('SELECT * FROM `palavras` WHERE `palavra` LIKE ? ORDER BY palavra ASC');

$search->bind_param('s','%' . $userInput . '%');
$search->execute();

Put the % in before the filter, allows you to search for parts that may be at the beginning, middle and end of the text.