PHP + MYSQL autocomplete - returning ID

Question

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 recover the id of the same and save in the bank. I have tried every way but I can not recover the id, only the 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();

Wanted a way to complete the ID field with the data the user selects.

READY I WAS ABLE TO RESCUE THE ID SO MISSING POPULATE INSIDE HIDDEN knows how I can do it because adding straight to the result does not work MY JAVASCRIPT IS LIKE THIS 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

Answer 1

To return data from id and nome of the professional, we must inform the 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>
<script src="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>

php server.

$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 wasn’t from server.php to make it functional here on the site, but you should change the URL for the server.php

To test the php server., access https://www.example.com/server.php?query=A, if it is working, check if the URL on javascript is correct.