Problem with autocomplete

Asked

Viewed 394 times

0

Good evening, I’m trying to do an input with auto complete per query in mysql database, but I’m having problems. I did all the code following a tutorial and still will not, nor errors appear to me. When I type do not appear options to complete. Follow the code:

pg index

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Auto Complete</title>
<link rel="stylesheet" href="assets/js/jquery-ui.css" />
<script src="assets/js/jquery-3.3.1.min.js"></script>
<script src="assets/js/bootstrap.min.js"></script>
<script src="assets/js/jquery-ui.js"></script>


</head>
<body>
    <form action="t" method="POST" accept-charset="utf-8">


    <label>Cliente:</label>

    <input type="text" id="assunto" name="assunto">
 </form>

    <script type="text/javascript">
        $(document).ready(function(){
            $("#assunto").autocomplete({
                source: 'retornaCliente.php' 
            });
        });

    </script>
</body>
</html>

pg return

<?php 

include 'conexao.php';
$pdo = conectar();

$assunto = filter_input(INPUT_GET, 'term', FILTER_SANITIZE_STRING);

$buscar = $pdo->prepare("SELECT NOME_LOJA FROM lojas WHERE NOME_LOJA LIKE '%".$assunto."%' ORDER BY NOME_LOJA ASC ");
$buscar->execute();

    while ($result = $buscar->fetch(PDO::FETCH_ASSOC)) {
        $dados[] = $result['NOME_LOJA'];
    }
    echo json_encode($dados);

$assunto = filter_input(INPUT_GET, 'term', FILTER_SANITIZE_STRING);

//SQL para selecionar os registros
$result_msg_cont = "SELECT assunto FROM mensagens_contatos WHERE assunto LIKE '%".$assunto."%' ORDER BY assunto ASC LIMIT 7";

//Seleciona os registros
$resultado_msg_cont = $conn->prepare($result_msg_cont);
$resultado_msg_cont->execute();

while($row_msg_cont = $resultado_msg_cont->fetch(PDO::FETCH_ASSOC)){
    $data[] = $row_msg_cont['assunto'];
}

echo json_encode($data)

 ?>

the connection with the bank is ok because I tested separately, if anyone can help me thank you.

php jquery mysql

  • your connection string is $Pdo or $Conn?

  • Welcome to the site, be sure to mark an answer as it accepts if it solves your problem. See how in https://i.stack.Imgur.com/evLUR.png and why in https://pt.meta.stackoverflow.com/questions/1078/como-e-por-que-aceitar-uma-resposta/1079#1079

  • You are returning 2 separate json, this is one of the problems. Put everything together in one array, and then generate JSON. To test, it is good to access the resource directly without AJAX, and see if the result is what you expect. Also, you should look at the PHP error log to see additional problems. There are other problems like unnecessarily recovering the subject twice, and an SQL injection problem, plus a mix of different connection variables, and lack of error handling. It would be better not to accumulate so many little problems at once so as not to delay development.

1 answer

4


You are returning 2 separate json, that’s one of the problems. Put it all together in one array $data[], and then generate JSON. To test, it is good to access the resource directly without AJAX, and see if the result is what you expect. Also, you should look at the PHP error log to see additional problems. It has other problems such as SQL injection, besides a mix of different connection variables, and lack of error handling. It would be better not to accumulate so many problems at once so as not to delay development. (by Bacco).

That said, or rather, imported this, Hands to the Work!

returnCliente.php

<?php 

        $hostname="localhost";  
        $username="USUARIO";  
        $password="SENHA";  
        $db = "Nome_DB";  
        $pdo = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);

    $assunto = filter_input(INPUT_GET, 'term', FILTER_SANITIZE_STRING);


    $buscar = $pdo->prepare('SELECT NOME_LOJA FROM lojas WHERE NOME_LOJA LIKE ? ORDER BY NOME_LOJA ASC');
    $buscar->execute(array("%$assunto%"));

    while ($results = $buscar->fetch())
    {
        $data[] = $results['NOME_LOJA'];
    }


    //SQL para selecionar os registros
    $result_msg_cont = $pdo->prepare('SELECT assunto FROM mensagens_contatos WHERE assunto LIKE ? ORDER BY assunto ASC LIMIT 7');
    $result_msg_cont->execute(array("%$assunto%"));

    while ($row_msg_cont = $result_msg_cont->fetch())
    {
        $data[] = $row_msg_cont['assunto'];
    }


    echo json_encode($data);

?>

HTML

  ............
  .............
  <input type="text" id="assunto" name="assunto">
  </form>

    <script type="text/javascript">
        $(document).ready(function(){
            $("#assunto").autocomplete({
                source: 'retornaCliente.php' 
            });
        });

    </script>
    ...............
    ...............

HTML

<head>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
    <link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/base/jquery-ui.css" rel="stylesheet" type="text/css"/>
    <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/jquery-ui.min.js"></script>
</head>

<body>
<form action="t" method="POST" accept-charset="utf-8">
    <label>Cliente:</label>
    <input type="text" id="assunto" name="assunto" placeholder="minLength: 2">
</form>

<script type="text/javascript">
    $(document).ready(function(){
        $( "#assunto" ).autocomplete({
            minLength: 2,
            source: function( request, response ) {
                $.ajax({
                    url: "retornaCliente.php",
                    data: { query: request.term},
                    success: function(data){
                        response(data);
                    },
                    error: function(jqXHR, textStatus, errorThrown){
                        $("#myModalErro").modal('show');                       
                    },
                  dataType: 'json'
                });
            }
        });   
    });        

</script>

<div v id="myModalErro" class="modal fade bd-example-modal-sm" tabindex="-1" role="dialog" aria-labelledby="mySmallModalLabel" aria-hidden="true">
  <div class="modal-dialog modal-sm">
       <div class="modal-content">
            <div class="modal-header">
                <h4 class="modal-title">Erro </h4>
            </div>
            <div class="modal-body">
                <p>Ouve um erro ao enviar sua URL</p>
            </div>
            <div class="modal-footer"> 
                <button type="button" class="btn btn-danger waves-effect waves-light" data-dismiss="modal">Fechar</button>
            </div>
        </div>
  </div>
</div>

</body>

returnCliente.php

$hostname="localhost";  
$username="USUARIO";  
$password="SENHA";  
$db = "Nome_DB"; 

    try{
        $pdo = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
    }catch(PDOException $e){
        die();

    }

$assunto = filter_input(INPUT_GET, 'query', FILTER_SANITIZE_STRING);

$query = $pdo->prepare('SELECT region FROM tbl_regiones WHERE region LIKE ? group by region ORDER BY region ASC');
$query->execute(array("%$assunto%"));

while ($results = $query->fetch())
{
    $data[] = $results['region'];
}

$query = $pdo->prepare('SELECT contador FROM tbl_region WHERE region LIKE ? ORDER BY region ASC LIMIT 7');
$query->execute(array("%$assunto%"));

while ($results = $query->fetch())
{
    $data[] = $results['contador'];
}


echo json_encode($data);
  • 1

    @Bacco, used in full :)

  • 1

    @Bacco, I’m still contemplating the idea of putting everything in one array.

Browser other questions tagged php jquery mysql

You are not signed in. Login or sign up in order to post.