How to connect two SELECT (combobox) in PHP and do Auto Fill?

Question

I wonder how I connect two select. As if it had a category and a sub-category, but I want the sub-category to appear when I click on the category. I have two tables in bd a member and another exercise, when I choose my member he lists me all the exercises registered to that member.

Here, I’m showing my "category"

            $database = new Database();
            $db = $database->getConnection();
            $exercicio = new exercicio($db);

            $membro = new membro($db);
            $stmt = $membro->read();

        echo "<select class='form-control' name='membro_corpo'>";
            echo "<option>Select Membro...</option>";

            while ($row_category = $stmt->fetch(PDO::FETCH_ASSOC)){
                extract($row_category);
                echo "<option value='{$id_membro}'>{$nome_membro}</option>";
            }
    echo "</select>";

Now I want you to show me the "sub-categories" when I click in the category.

$stmt1 = $exercicio->read();

        echo "<select class='form-control' name='category_id'>";
            echo "<option>Select Exercicio...</option>";

            while ($row_exercicio = $stmt1->fetch(PDO::FETCH_ASSOC)){
                extract($row_exercicio);
                if($exercicio->id_membro_exer==$id_membro){
                    echo "<option value='$id_membro' selected";
                }else{
                    echo "<option value='$id_membro'";
                }
                echo "$nome_exer</option>";
            }

        echo "</select>";

Answer 1

The way I did the bottom is an example using ajax technology with jquery:

<div id="select_m">
 <label>Membros:</label>
  <select id="post_m" class='form-control' name='membro_corpo'>
  </select>
</div>
<div id="select_e">
  <label>Exercícios:</label>
    <select id="post_e" class='form-control' name='category_id'>
    </select>
</div>
<script src="https://code.jquery.com/jquery-latest.min.js"></script>
<script>
$(function() {
   var valor = 'all_members';
   carregarDados('ajax_select.php', valor, 'm','Selecionar membro');

    $('#select_e').hide();  

    $('#select_m select').on('change', function() {
      var valor = $(this).val();
      hideShowInputSubmit(valor, false, 'e');
      carregarDados('ajax_select.php', valor, 'e','Selecionar exercício');
    });

    $('#select_e select').on('change', function() {
      var valor = $(this).val();
      hideShowInputSubmit(valor, true, 'e');
    });

});

function hideShowInputSubmit(value, submeter, elementDisplay) {
     if (value == '') {
            $('#select_'+elementDisplay).hide();
         } else {
            $('#select_'+elementDisplay).show();
     }
     if (submeter) {
        //aqui você submete o valor final
        alert('Submeter form com valor: '+value);
     }
}

function carregarDados(url, valor_selecionado, id_select, default_title_value) {

    var data = {
             valor_selecionado:valor_selecionado,
             id_formulario:id_select
    };
    $.post(url,data,function(e) {
       var options = [];
       var default_option = '<option value="" selected>'
                            + default_title_value +
                            '</option>';

       var collection = jQuery.parseJSON(e);

       for (var i in collection) {
          options[i] = '<option value="'
                       + collection[i].id   + '">'
                       + collection[i].name + 
                       '</option>';
       }
       var opc = options.join("\n");
       var selects = [
               default_option, 
               opc
           ].join("");
       $('#post_'+id_select).html(selects);
    });    
}
</script>

In the archive ajax_select.php in PHP you have to return a JSON output from $_POST['valor_selecionado'] and $_POST['id_formulario'] (echo json_encode($seu_array)).

Let’s assume it’s something like that:

<?php
if($_POST) {

    if (isset($_POST['valor_selecionado'])) {
        $id = $_POST['valor_selecionado'];

        if (in_array($id, array('1','2', 'all_members'))
            && $_POST['id_formulario'] == 'm') {
          //membros
           $return = array(
              array('id'=>'1','name'=>'Luiz'),
              array('id'=>'2','name'=>'Fábio'),
            );
        }

        if (in_array($id, array('1','2','3'))
           && $_POST['id_formulario'] == 'e') {
           //exercícios
           $return = array(
              array('id'=>'1','name'=>'Exercício 1'),
              array('id'=>'2','name'=>'Exercício 2'),
              array('id'=>'3','name'=>'Exercício 3'),
            );
        }
    }
   echo json_encode($return);  
   exit();
}


?>

If you want to test, just put the code in PHP before HTML and save a file named "ajax_select.php" and run it.

Answer 2

The most practical way would be to use ajax, create a function in jquery:

(function($){
    $.subcategoria = function(id) {
        if(id != '') {
            $('select#subcategoria').html('<option value="">Carregando...</option>');
            $.post('subcategorias.php', { categoria: id }, function(data) { $('select#subcategoria').html(data); });
        } else {
            $('select#subcategoria').html('<option>Select Exercicio...</option>');
        }
    };
})(jQuery);

In your first select you add the function call:

<select class="form-control" name="membro_corpo" onchange="$.subcategoria(this.value);">

In the second select add the id subcategoria to use in function

<select class="form-control" name="category_id" id="subcategoria">

Finally, in your php file subcategoria.php (or as you wish to call) receive the id sent via POST and list the related subcategories:

$categoria = $_POST['categoria'];
$stmt1 = $exercicio->read($categoria); // função que liste as subcategorias relacionadas
while($row_exercicio = $stmt1->fetch(PDO::FETCH_ASSOC)) {
    echo "<option value=\"$id_membro\">$nome_exer</option>";
}

You can either print in the php file the tags already ready to be included in the select field or use json and print the object and mount the tags in jquery, this is just one of several ways.

I hope it helps, hug.