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

Question

I have a database where information is stored from the year on. On the site I have 2 Selects, one for year and one for the files of that year, currently I do this with JS but I wanted to switch to mysql, so I wanted to know how to do for example if I select 2018 in the first select display in the second select only the files of that respective year. I don’t have much knowledge in this area, I googled yesterday, I tried some ideas, I can fill the fields with data but I could not do this filtering per year in a way Lum. I’ll take any help I can get. Ps. all information is stored in the same table.

Answer 1

pagina_qq_name.php

1. Required Jquery library

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

2. Script

   $(document).ready(function() {
     $('#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) {
               $('#arquivo').attr('disabled', 'disabled');
               if ($('#ano').val() !== 'ano') {
                  $('#arquivo').html('<option value="">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();
               }
           }
       });
     });
   
   });
   

3. 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();
   

4. 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>
   

php list.

$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();

                }
            }
        });
    });

});

O select file

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

Answer 2

You can use AJAX to request and return database values 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 above example, it will run the file "searchNoBanco.php" with the GET of the selected year in select (2018), which will search the respective year’s files in the database. The return of the file will be by the ajax.responseText variable.