Loop in json that returns all records

Question

I’m developing a system, in it I do a search with ajax and it returns a json with the bank records, but I couldn’t draw up a loop in the PHP so that the json could return all records. Below follows my code:

view.php

<div class="form-group">
    <label class="col-sm-1 col-sm-1 control-label">Filtro</label>
    <div class="col-md-4">
        <select class="form-control m-b-10" name="id" charset="utf-8" onchange="busca();">
            <option>Selecione</option>
           <?php
            $select  = "SELECT * FROM pessoas"; 
            $result  = mysqli_query($conexao, $select);
            while($exibe = mysqli_fetch_assoc($result)){
                $id = $exibe['id'];
                echo '<option  charset="utf-8" value = '. $id . '>' . utf8_encode(strtolower($exibe['nome'])) . '</option>';
            }                                    
            mysqli_free_result($result); 
           ?>                                                                          
        </select> 
    </div>                                                         
</div>        
<div class="col-md-8">
    <label class="col-sm-8 col-sm-8 control-label" style="display: none;" id="vazio">Não foram encontrados resultados!</label>
</div>                                                                                             
<div class="panel-body table-responsive">
    <table class="table table-hover" id="tabela">
        <thead id="top">
        </thead>
<script>
  function busca(){
    var id         = document.getElementsByName("id")[0].value;
    var parametros = {
      method: "GET"
    };
    fetch("php/busca.php?id=" + id, parametros).then(function(resposta) {
      return resposta.json();
    }).then(function(retorno){
        console.log(retorno);                 
        if (retorno != 1){   
          document.getElementById("vazio").style.display = 'none';
          document.getElementById("top").style.display = 'none'; 
          document.getElementById("tabela").innerHTML      = "<thead><tbody><tr><th>Nome</th><tr><td>" + retorno.nome + "</td></tr></tbody></thead>";                                
        }else{
          document.getElementById("vazio").style.display = 'inline-block';
        }
    });            
  }
</script>

php search.

header('Content-Type: json/application'); 
$id      = $_GET['id'];
$select  = "SELECT * FROM dados WHERE ent_id = '$id'";
$resul   = mysqli_query($conexao, $select);

if ((mysqli_num_rows($resul) > 0)) {
    $mostrar = mysqli_fetch_assoc($resul);
    echo json_encode($mostrar);
}else{
    $mostrar = 1;
    echo json_encode($mostrar);
}

Answer 1

In.php search, you are only returning the first result. It has to loop as it does for select options and create an array (I chose to create only with the name, because in javascript you were only using the name and so are always less data to transfer):

header('Content-Type: json/application'); 
$id      = $_GET['id'];
$select  = "SELECT * FROM dados WHERE ent_id = '$id'";
$resul   = mysqli_query($conexao, $select);

$dados = array();
if ((mysqli_num_rows($resul) > 0)) {
    while($mostrar = mysqli_fetch_assoc($resul)){
         $dados[] = $mostrar['nome'];
    }                                    
    mysqli_free_result($resul); 
    echo json_encode($dados);
}else{
    $mostrar = 1;
    echo json_encode($mostrar);
}

Then in ajax, you have to make another loop.

<script>
  function busca(){
    var id         = document.getElementsByName("id")[0].value;
    var parametros = {
      method: "GET"
    };
    fetch("php/busca.php?id=" + id, parametros).then(function(resposta) {
      return resposta.json();
    }).then(function(retorno){
        console.log(retorno);                 
        if (retorno != 1){   
          document.getElementById("vazio").style.display = 'none';
          document.getElementById("top").style.display = 'none'; 
          var tabela = "<thead><tbody><tr><th>Nome</th></tr>";
          var arrayLength = retorno.length;
          var c = 0;
          for (var i = 0; i < arrayLength; i++) {
                if(c == 0)
                    tabela += "<tr><td>";
                tabela += retorno[i] + " ";
                if((c == 6) || (i == (arrayLength - 1))){
                    tabela += "</td></tr>";
                    c = 0;
                }
                else
                    c++;
          }
          tabela += "</thead>";
          document.getElementyById("tabela").innerHTML = tabela;                                
        }else{
          document.getElementById("vazio").style.display = 'inline-block';
        }
    });            
  }
</script>