query mysql autocomplete problem

Question

I am developing an app and, in one of the steps, I am using a mysql database to store code and product information. When the user enters the product code in an input field, he is searching throughout the code, but I would like him to search exactly as the user type, example:

• If the user type "home"

•Display marriage, marriage, etc.

• And no mating, etc.

The second problem is that I need the other product information to appear in input fields called id, but also not going.

HTML Select the product:

        <div id="infoProd"></div>
        <p><span class="labelFake">Codigo do produto:</span><input type="text" id="codigoItem" disabled></p>
        <p><span class="labelFake">Descrição:</span><input type="text" id="descricao" disabled></p>
        <p><span class="labelFake">Alíquota de IPI:</span><input type="text" id="aliqIPI" disabled></p>

php to connect the database and return the data in json

            // Recebe os parâmetros enviados via GET
            $acao = (isset($_GET['acao'])) ? $_GET['acao'] : '';
            $parametro = (isset($_GET['parametro'])) ? $_GET['parametro'] : '';

            // Configura uma conexão com o banco de dados
            $opcoes = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES UTF8');
            $conexao = new PDO("mysql:host=".SERVER."; dbname=".DBNAME, USER, PASSWORD, $opcoes);

            // Verifica se foi solicitado uma consulta para o autocomplete
            if($acao == 'autocomplete'):
                $where = (!empty($parametro)) ? 'WHERE codigo_item LIKE ?' : '';
            $sql = "SELECT codigo_item, descricao_item, aliq_ipi FROM base_prod " . $where;

            $stm = $conexao->prepare($sql);
            $stm->bindValue(1, '%'.$parametro.'%');
            $stm->execute();
            $dados = $stm->fetchAll(PDO::FETCH_OBJ);

            $json = json_encode($dados);
            echo $json;
            endif;

            // Verifica se foi solicitado uma consulta para preencher os campos do formulário
            if($acao == 'consulta'):
                $sql = "SELECT codigo_item, descricao_item, aliq_ipi FROM base_prod ";
            $sql .= "WHERE codigo_item LIKE ? LIMIT 1";

            $stm = $conexao->prepare($sql);
            $stm->bindValue(1, $parametro.'%');
            $stm->execute();
            $dados = $stm->fetchAll(PDO::FETCH_OBJ);

            $json = json_encode($dados);
            echo $json;
            endif;

and the javascript I used

$(Function() { // Assigns event and function to clear fields $('#search'). on('input', clear Fields);

                // Dispara o Autocomplete a partir do segundo caracter
                $( "#busca" ).autocomplete({
                    minLength: 2,
                    source: function( request, response ) {
                        $.ajax({
                            url: "consulta.php",
                            dataType: "json",
                            data: {
                                acao: 'autocomplete',
                                parametro: $('#busca').val()
                            },
                            success: function(data) {
                                response(data);
                            }
                        });
                    },
                    focus: function( event, ui ) {
                        $("#busca").val( ui.item.codigo_item );
                        carregarDados();
                        return false;
                    },
                    select: function( event, ui ) {
                        $("#busca").val( ui.item.codigo_item );
                        return false;
                    }
                })
                .autocomplete( "instance" )._renderItem = function( ul, item ) {
                    return $( "<li>" )
                    .append( item.codigo_item+ " — " + item.descricao_item )
                    .appendTo( ul );
                };

                // Função para carregar os dados da consulta nos respectivos campos
                function carregarDados(){
                    var busca = $('#busca').val();

                    if(busca != "" && busca.length >= 2){
                        $.ajax({
                            url: "consulta.php",
                            dataType: "json",   
                            data: {
                                acao: 'consulta',
                                parametro: $('#busca').val()
                            },
                            success: function( data ) {
                                $('#codigoItem').val(data[0].codigo_item);
                                $('#descricao').val(data[0].descricao_item);
                                $('#aliqIPI').val(data[0].aliq_ipi);
                            }
                        });
                    }
                }

                // Função para limpar os campos caso a busca esteja vazia

                function limpaCampos(){
                    var busca = $('#busca').val();
                    if(busca == ""){
                        $('#codigoItem').value('');
                        $('#busca').val('');
                        $('#descricao').val('')
                        $('#aliqIPI').val('');

                    }
                }
            });

Answer 1

First doubt. To return words starting with the search, you should not use the wildcard character % at first, because if you do, you will "say" to Mysql that you don’t "care" what the word starts, as long as the search appears somewhere in it. The % at the end indicates that you do not care how the word ends. It seems that is your case. So you should remove the first %.

$stm->bindValue(1, $parametro.'%');

The second question. I don’t know which lib you’re using for this autocomplete. But by analyzing the code, I imagine that in the event triggered when a value is selected, you have access to the other log data. Then, using Javascript or the jQuery lib, you can assign these values to the elements in this way:

...
select: function( event, ui ) {
  $("#codigoItem").val(ui.item.codigo_item);
  $("#descricao").val(ui.item.descricao_item);
  $("#aliqIPI").val(ui.item.aliq_ipi);
  //Código original, não sei para que serve.
  $("#busca").val( ui.item.codigo_item );
  return false;
}
....