PHP + MYSQL autocomplete - returning ID

Question

PHP + MYSQL autocomplete - returning ID

Navigation

#1 by (0 votes)

0

I'm developing an application for college, and I came across the following problem:

I have to search used autocomplete passing as parameter the name of the doctor, so retrieve the id from it and save it to the bank. I've tried it anyway but I can not retrieve the id , only autocomplete works.

Example

In PHP

<div class="form-group">
    <label>Profissional</label>

    <input type="text" name="prof" value="" class="form-control"   placeholder="Insira o nome do Profissional." id="txtProf" class="typeahead">
    <input type="hidden" name="idprof" value="" class="form-control"  id="idprof" >
</div>

<div class="form-group">
    <label >Procedimento</label>
    <input type="text" name="proced" value="" class="form-control"   placeholder="Insira o nome do Procedimento." id="txtProc" class="typeahead">
</div>

In JavaScript:

$(document).ready(function () {
    $('#txtProf').typeahead({
        source: function (query, result) {
            $.ajax({
                url: "../classes/db/server.php",
                data: 'query=' + query,            
                dataType: "json",
                type: "POST",
                success: function (data) {
                  result($.map(data, function (item) {
                    return item;
                    }));
                }
            });
        }
    });
});

And in the search file (server.php):

$keyword = strval($_POST['query']);
$search_param = "{$keyword}%";
$conn =new mysqli('localhost', 'root', '' , 'clinicanova');

$sql = $conn->prepare("SELECT pf.nome FROM tb_pessoa pf, tb_profissional prof
                         WHERE pf.id = prof.tb_pessoa_id
                         and prof.status = 'A'
                         and pf.nome LIKE ?");
$sql->bind_param("s",$search_param);            
$sql->execute();
$result = $sql->get_result();
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
    $countryResult[] = $row["nome"];
    }
    echo json_encode($countryResult);

}
$conn->close();

I wanted a way to complete the ID field with the data that the user selected.

SOON I GOT TO REDUCE THE ID SO MISSING TO GO WITHIN HIDDEN knows how I can do this because adding direct to the result does not work , MY JAVASCRIPT IS SO NOW:

<script>
$(document).ready(function () {
    $('#txtProf').typeahead({
        source: function (query, result) {
            $.ajax({
                url: "../classes/db/server.php",
                data: 'query=' + query,            
                dataType: "json",
                type: "POST",
                success: function (data) {
        result($.map(data, function (item) {
          return item.nome;
           $('#idprof').val(item.id);
                    }));
                }
            });
        }
    });
});

HOW DO I INSERT THIS "item.id" into an input

php javascript autocompletar

asked by anonymous 03.01.2018 / 02:56

1 answer

Can I create a cron to delete files with retroactive dates? GPIO Pins RPi 3 Python

score 0 · Accepted Answer

To return the data of id and nome of the professional, we have to inform in query sql these fields. Ex:

/* Aqui utilizarei uma lib do Typehead para auxiliar. Essa lib irá nos auxiliar com os requests */
var profissionais = new Bloodhound({
  datumTokenizer: Bloodhound.tokenizers.obj.whitespace('nome'),
  queryTokenizer: Bloodhound.tokenizers.whitespace,
  /* Aqui eu defino a URL do JSON e o termo. Nesse caso eu utilizarei GET e não POST */
  prefetch: 'https://api.myjson.com/bins/fn5wb', /* Substitua APENAS "https://api.myjson.com/bins/fn5wb". */
  remote: {
    url: 'https://api.myjson.com/bins/fn5wb?query=%query', /* Substitua APENAS "https://api.myjson.com/bins/fn5wb". */
    'wildcard': '%query'
  }
});

$('#lista-profissionais .typeahead').typeahead(null, {
  name: 'profissionais',
  display: 'nome',
  source: profissionais
});

/* Esse evento irá capturar o resultado do nome e ID */
$('#lista-profissionais .typeahead').bind('typeahead:select', function(ev, suggestion) {
  $("#idprof").val(suggestion.id);

  alert("Nome: " + suggestion.nome + "\nId: " + suggestion.id);
});

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script><scriptsrc="https://cdnjs.cloudflare.com/ajax/libs/typeahead.js/0.11.1/typeahead.bundle.min.js"></script>

<div id="lista-profissionais">
  <input type="hidden" name="idprof" value="" class="form-control" id="idprof">
  <input class="typeahead" type="text" placeholder="Informe o nome do profissional">
</div>

server.php

$keyword = strval($_GET['query']);

$conn = new mysqli('localhost', 'root', '', 'clinicanova');

/* Aqui informamos que devemos capturar o ID e o NOME do profissional. */
$sql = $conn->prepare("SELECT pf.id AS 'id', pf.nome AS 'nome' FROM tb_pessoa pf, tb_profissional prof
                         WHERE pf.id = prof.tb_pessoa_id
                         and prof.status = 'A'
                         and pf.nome LIKE ?");

$sql->bind_param("s", "{$keyword}%");            

$sql->execute();

$result = $sql->get_result();

$countryResult = [];

while($row = $result->fetch_assoc()) {

    /* Aqui montamos o array com os dados que serão exibidos */
    $countryResult[] = [
            "id" => $row["id"], //Verifique se esse index realmente existe.
            "nome" => $row["nome"], //Verifique se esse index realmente existe.
    ];
}

echo json_encode($countryResult);

$conn->close();

I added a json that was not from server.php to leave functional here on the site, but you should change the URL to server.php

To test server.php , visit link , if it is working, make sure the URL in javascript is correct.