Problem with autocomplete

Question

Problem with autocomplete

Navigation

#1 by (5 votes)

2

Good evening, I'm trying to auto-complete an input via query in mysql database, but I'm having problems. I did all the code following a tutorial and even then it will not, nor will any errors appear to me. When I type, there are no options to complete. Here is 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 returnCustomer

<?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 to the bank is ok because I tested it separately, if you can, help me thanks.

php mysql jquery

asked by anonymous 13.09.2018 / 01:34

1 answer

PHP SQL Inner Join - Show name instead of ID number Filter through the href

score 5 · Accepted Answer

You're returning 2 json separated, that's one of the problems. Put everything in an array with only $data[] , and then generate JSON. To test, it is good to access the feature directly without AJAX, and see if the result is what you expect. Also, you should look at the PHP error log for additional problems. It has other problems like SQL Injection, plus a mix of different connection variables, and lack of error handling. It would be best not to accumulate so many problems at once so as not to delay development. (by Bacco).

That said, or rather, imported it, Hands On!

returnClient.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><linkrel="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><linkhref="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><formaction="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>

returnClient.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);