How to fill a Select dropdown based on the value of another select, the data comes from the same table in db

Question

How to fill a Select dropdown based on the value of another select, the data comes from the same table in db

Navigation

#1 by (0 votes)
#2 by (-1 votes)

1

I have a database where information is stored from the year. In the site I have 2 Selects, one for year and one for the files of that year, I currently do this with JS but I wanted to move to mysql, hence I wanted to know how to do for example if I select 2018 on the first select display in the second select only the file of that year. I do not have much knowledge in this area, yesterday I made a googlada, tried some ideas, I can fill the fields with the data but I was not able to do this filtering for year alum way. I'll take any help. Ps. all information is stored in the same table.

php mysql javascript html

asked by anonymous 15.06.2018 / 15:00

2 answers

Doubts about list chained in C Modify an HTML element or a native browser function

score 0 · Answer 1

page_qq_name.php

Required Jquery library

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>

Script

$(document).ready(function(){$('#ano').on('change',function(){$.ajax({type:'POST',url:'lista_arquivos.php',data:{'ano':$('#ano').val()},//Antesdecarregarosregistros,mostraparaousuárioqueestá//sendocarregado.beforeSend:function(xhr){$('#arquivo').attr('disabled','disabled');if($('#ano').val()!=='ano'){$('#arquivo').html('<optionvalue="">Carregando...</option>');
            }else{
               $('#arquivo').html('<option value="">Arquivo</option>');
            }
        },
        // Após carregar, coloca a lista dentro do select de arquivos.
        // Após carregar, coloca a lista dentro do select de arquivos.
        success: function(data) {
            if ($('#ano').val() !== '') {
                // Adiciona o retorno no campo, habilita e da foco
                $('#arquivo').html('<option value="">Selecione</option>');
                $('#arquivo').append(data);
                $('#arquivo').removeAttr('disabled').focus();
            }
        }
    });
  });

});

PHP

$hostname="localhost";  
$username="USUARIO";  
$password="SENHA";  
$db = "nome_DB";  
$conexao = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);

  $sqlano = 'SELECT * FROM anos GROUP BY ano ORDER BY ano ASC';
  $resano = $conexao->prepare($sqlano);
  $resano->execute();
  $anos = $resano->fetchAll();

HTML

     <select name="ano" id="ano" required>
           <option value="ano">Ano</option>
         <?php foreach ($anos as $ano) { ?>
           <option value="<?php echo $ano['ano'] ?>"><?php echo $ano['ano'] ?></option>
         <?php } ?>
     </select>

        <select name="arquivo" id="arquivo" disabled required>
          <option value="">Arquivo</option>
        </select>

file_list.php

$hostname="localhost";  
$username="USUARIO";  
$password="SENHA";  
$db = "nome_DB";  

$conexao = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);

$postAno = $_POST['ano'];

$sql = "SELECT * FROM anos WHERE ano = '$postAno' ORDER BY ano ASC";
$resArquivo = $conexao->prepare($sql);
$resArquivo->execute();
$arquivos = $resArquivo->fetchAll();
?>

<?php foreach ($arquivos as $row) { ?>
    <option value="<?php echo $row['arquivo'] ?>"><?php echo $row['arquivo'] ?></option>
<?php } ?>

If you prefer to show the select of the files after selecting the year:

Script

$(document).ready(function() {
$("#arquivo").hide();
    $('#ano').on('change', function() {
        $.ajax({
            type: 'POST',
            url: 'lista_arquivos.php',
            data: {'ano': $('#ano').val()},
            // Antes de carregar os registros, mostra para o usuário que está
            // sendo carregado.
            beforeSend: function(xhr) {
                if ($('#ano').val() !== 'ano') {
                   $('#arquivo').html('<option value="">Carregando...</option>');
                }else{
                   $("#arquivo").hide();
                }
            },
            // Após carregar, coloca a lista dentro do select de arquivos.
            success: function(data) {
                if ($('#ano').val() !== 'ano') {
                    // Adiciona o retorno no campo, habilita e da foco
                    $('#arquivo').html('<option value="">Selecione</option>');
                    $('#arquivo').append(data);
                    $("#arquivo").show()
                    $( "#arquivo" ).focus();

                }
            }
        });
    });

});

The select file

<select name="arquivo" id="arquivo" style="display:none;">
    <option value="">Arquivo</option>
</select>

score -1 · Answer 2

You can use AJAX to make a request and return values from the database with JS:

ajax = new XMLHttpRequest();
ajax.onreadystatechange = function() {
    if ((ajax.readyState == 4) && (ajax.status == 200)) {
        document.getElementById("selectArquivos").value = ajax.responseText;
    }
}

ajax.open("GET","buscaNoBanco.php?ano=2018",true);
ajax.send();

In the example above, it will execute the file "searchBank.php" with the GET of the year selected in select (2018), which will search the database for the files of the respective year. The file will return the variable ajax.responseText.