Code that made the query:
$parametro = (isset($_GET['parametro'])) ? $_GET['parametro'] : '';
if($acao == 'consulta'):
$sql = "SELECT codigo,preco FROM produtos ";
$sql .= "WHERE descricao LIKE ? LIMIT 1";
try{
$query_select = $conecta->prepare($sql);
$query_select->bindValue(1, '%'.$parametro);
$query_select->execute();
$dados = $query_select->fetchAll(PDO::FETCH_OBJ);
}catch (PDOexception $error_select){
echo 'Erro ao selecionar: '.$error_select->getMessage();
}
$json = json_encode($dados);
echo $json;
endif;
Load query data into the “Código” and “Preço” fields:
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 ) {
$('#codigo').val(data[0].codigo);
$('#preco').val(data[0].preco);
}
});
}
}
and function to trigger autocomplete:
$( "#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.descricao );
carregarDados();
return false;
},
select: function( event, ui ) {
$("#busca").val( ui.item.descricao );
return false;
}
})
.autocomplete( "instance" )._renderItem = function(ul, item) {
return $( "<li>" ).append( "<strong>Código: </strong>" + item.codigo
+ " | <strong>Descrição: </strong>" + item.descricao
+ "<br>" ).appendTo(ul);
};
The query is performed based on the description of the search field and the return is displayed in the fields “Código” and “Preço”, however when there are two products with the same description and different codes the mentioned fields are not updated.
Illustration:
Code, explanation and execution found in the links:
https://www.youtube.com/watch?v=PPROnjv1fhI
http://www.devwilliam.com.br/php/autocomplete-com-jquery-ui-php-mysql