SELECT always returns the last record

Question

I’m trying to make it the moment I select an option in a select other fields are filled in using this information. I managed to do this partially, always selected the last record of the table I’m looking for, only that actually needs the record selected in option.

Another problem is the mask I do for the value does not hold when I change the value, I do not know how to use a mask that always remains when editing the value of the field.

If anyone knows what I’m doing wrong, I’d appreciate it if you could help!

<div class="form-group">
    <label for="exampleInputPassword1">Descrição</label>
    <select class="form-control m-b-10" required="required" name="pan_id" charset="utf-8" onchange="adiciona()">
        <option>Selecione a Descrição</option>
        <?php
            $select  = "SELECT * FROM p";
            $res  = mysqli_query($conexao, $select);
            while($mostra = mysqli_fetch_assoc($res)){
                $id     = $mostra['id'];
                echo '<option  charset="utf-8" value = '. $id . '>' . $mostra['descricao'] . '</option>';
            }
        ?>
    </select>
</div>

<?php 
    $select  = "SELECT  local, valor, tipo_valor FROM p WHERE id = '$id'"; 
    $resul  = mysqli_query($conexao, $select);

    while($mostrar = mysqli_fetch_assoc($resul)){
        $pan_valor   = $mostrar['valor'];
        $pan_local   = $mostrar['local'];
        $pan_tipo_valor = $mostrar['tipo_valor'];
    }                                    
?> 

<script>
    function adiciona(){
        var valor       = '<?php echo 'R$' . number_format($valor, 2, ',', '.');?>';
        var local       = '<?php echo $local; ?>';
        var vtipo_valor = '<?php echo $tipo_valor ?>';

        document.getElementById('valor').value = ''; 
        document.c.valor.value += valor;

        document.getElementById('local').value = '';
        document.c.local.value += local;

        switch(vtipo_valor) {
            case 'D':
            document.getElementById("tipo_valor").innerHTML = "<option selected>DIA</option><option>MÊS</option><option>ANO</option>";
            break;

            case 'M':
            document.getElementById("tipo_valor").innerHTML = "<option>DIA</option><option selected>MÊS</option><option>ANO</option>";
            break;

            case 'A':
            document.getElementById("tipo_valor").innerHTML = "<option>DIA</option><option>MÊS</option><option selected>ANO</option>";
        }
</script>

I’m trying to do it like this:

      function adiciona(){
          var xhttp = new XMLHttpRequest();
          var str   = document.getElementById('descricao');
          var desc  = str.value;
          xhttp.onreadystatechange = function() {
            if (this.readyState == 4 && this.status == 200) {
              document.getElementById("local").innerHTML = this.responseText;
              xhttp.open("GET", "php/busca.php?desc=" + desc, true);
              xhttp.send();
            }
          };

      }

In the search.php:

<?php
  $id      = $_GET['desc']; 
  $conexao = mysqli_connect('...', '', '', '');
  if(!$conexao){
    echo "<script> window.location.replace('../erro.html'); </script>"; 
}
$select  = "SELECT  local, valor, tipo_valor FROM p WHERE id = '$id'";
$resul  = mysqli_query($conexao, $select);
while($mostrar = mysqli_fetch_assoc($resul)){
    $valor      = $mostrar['valor'];
    $local      = $mostrar['local'];
    $tipo_valor = $mostrar['tipo_valor'];
}

Answer 1

Exactly the expected behavior is happening. To get the dynamic selection, either you make an ajax request to each selection of an option of your select (to be done a specific search by id), or you return all the contents of the variable $result as vector (unviable, due to exponential growth over time) to be manipulated by javascript.

Example

You can make an ajax request using the fetch api. See a small example adapted to your case.

Your current file:

<div class="form-group">
    <label for="exampleInputPassword1">Descrição</label>
    <select class="form-control m-b-10" required="required" name="pan_id" charset="utf-8" onchange="adiciona();">
        <option>Selecione a Descrição</option>
        <option  charset="utf-8" value ="1" >Primeiro id</option>;
        <option  charset="utf-8" value ="2" >Segundo id</option>;
        <option  charset="utf-8" value ="3" >Terceiro id</option>;    
    </select>
</div>

<!-- Coloque sua consulta no arquivo ajax.php-->

<!-- Campo para testar -->
<select id="tipo_valor"></select>

<script>
    //agora a funcao adiciona faz uma requisição ajax usando a api fetch
    function adiciona(){
        //obtem o valor do select com name igual a pan_id
        var id_selecionado = document.getElementsByName("pan_id")[0].value;

        var parametros = {
                method: 'GET'
        };

        //definie a url a ser acessada, e executa um callback na resposta da requisição
        fetch('ajax.php?id=' + id_selecionado, parametros).then(function(resposta) {
            return resposta.json();
        }).then(function(retorno){
            console.log(retorno);
            switch(retorno.tipo_valor) {
            case 'D':
            document.getElementById("tipo_valor").innerHTML = "<option selected>DIA</option><option>MÊS</option><option>ANO</option>";
            break;

            case 'M':
            document.getElementById("tipo_valor").innerHTML = "<option>DIA</option><option selected>MÊS</option><option>ANO</option>";
            break;

            case 'A':
            document.getElementById("tipo_valor").innerHTML = "<option>DIA</option><option>MÊS</option><option selected>ANO</option>";

            }
        });
    }
</script>

Note: there have been some changes in html to facilitate my debugging, undo.

And create a new php file in the same directory as the previous one.

ajax.php

<?php
header('Content-Type: json/application');
$id = $_GET['id'];

$select  = "SELECT  local, valor, tipo_valor FROM p WHERE id = '$id'"; 
$resul  = mysqli_query($conexao, $select);

//supondo que na sua tabela p  tenha o campo tipo_valor
$mostrar = mysqli_fetch_assoc($resul);

//desde que mostrar tenha $mostrar['tipo_valor'];
echo json_encode($mostrar);