query query mysql autocomplete

0

I'm developing an app and in one of the steps I'm using a mySQL database to store code and product information. When the user enters the product code in an input field, he is searching the entire code, but would like it to search exactly as the user types, for example:

• If the user types "home"

• Show marriage, wedding, and so on.

• And no mating, etc.

The second problem is that I need the other product information to appear in input fields designated by id, but neither is going.

HTML             Select your 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 () {                     // Assign event and function to clean fields                     $ ('# search') on ('input', cleanCamps);

                // 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('');

                    }
                }
            });
    
asked by anonymous 14.01.2017 / 02:48

1 answer

3

First question. To return words that begin with the search, you should not use the % wildcard character at first, because if you do, it will "tell" you to MySQL that you do not "care" what the word starts, since the search will appear in some part of it. The % at the end indicates that you do not care how the word ends. Looks like your case. So you should remove the first % .

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

The second doubt. I do not know which lib you are 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 data in the registry. So by using JavaScript or the jQuery lib, you can assign these values to elements like this:

...
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;
}
....
    
14.01.2017 / 04:08