Change a select based on the selection of another select (from data in BD)

Question

I have the following problem:

In a form, I need to select a MANUFACTURER, and according to this selection, it shows the PRODUCTS linked to that manufacturer only.

I have the following table where I register the MANUFACTURER:

FABRICANTES:
id_fabricante | nome_fabricante
     1        |     CANON

And I have the CAMERAS table, where I link the MANUFACTURER to the camera model

CAMERAS:
id_camera | fabricantes_id_fabricante (chave estrangeira) | modelo_camera
1         |            CANON                              |   5D MARK II

Now that the tricky part comes in, I created the form where it pulls PHP into the database the registered manufacturers:

<?php
$fabricantes = listaFabricantes($conexao);
?>
   ...
    <tr>
    <td>Fabricante:</td>
    <td>
    <select name="fabricantes_id_fabricante" id="fabricantes_id_fabricante" class="form-control">
    <?php
foreach ($fabricantes as $fabricante):
?>
   <option value="<?= $fabricante['id_fabricante'] ?>">
    <?= $fabricante['nome_fabricante'] ?></br></option> 
    <?php
endforeach;
?>
   </td> 


    </tr>
    ... 

And then I created the form that lists the cameras:

<tr>
<td>Linha de Câmera:</td>
<td>
<select name="cameras_linhas_id_camera_linha" id="cameras_linhas_id_camera_linha" class="form-control">
<?php foreach($cameras_linhas as $camera_linha) :?>
<option value="<?=$camera_linha['id_camera_linha']?>">
<?=$camera_linha['nome_linha_camera']?></br></option> 
<?php endforeach?>
</td> 
</tr>

Only what I want to do now is when I select the CANON manufacturer it only appears the cameras that have relationship with that manufacturer, and not that list all the cameras.

The closest I could do was using Jquery as follows:

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js" ></script>
<script src="//ajax.googleapis.com/ajax/libs/jqueryui/1.10.2/jquery-ui.min.js"></script>‌​
<script>
$('#fabricantes_id_fabricante').change(function(){
   selection = $(this).val();    
   switch(selection)
   { 
       case '1':
           $('#cameras_linhas_id_camera_linha').show();
           break;
       default:
           $('#cameras_linhas_id_camera_linha').hide();
           break;
   }
});
</script>

But in this case above, it only works if the SELECTS options are created in the html form and not fetching from the database to link.

I know it’s a little long, but could someone help with an idea of how you could do this Jquery query in the bd to return the results or some other light?

It would be something like what they do as State versus City, where they click on a given state and only the respective cities appear, and this information is fetched from a database. I’ve been racking my brain for a few days now, so I came here.

Thanks in advance.

Answer 1

Hello,

What you need for your case is a one-page call on AJAX for example, receive the data you want to search.

When you change the option of your selectbox it makes the AJAX call to update the options of another selectbox:

HTML

<select id="fabricante">
    <option value="1">Canon</option>
    <option value="2">Nikon</option>
</select>

<select id="modelo"></select>

JS

$('select#fabricante').on("change", function(){
    $.ajax({
        url: example.php,
        type: 'GET',
        dataType: 'html',
        success: function (data) {
            $('#modelo').html(data);
        }
    });
});

Note: The File example.php is that it tries to replace the selectbox that already exists in html, you must create another select there with the new options that you will receive in AJAX GET.

Answer 2

I suggest you use AJAX, where it would look like this:

In the front-end:

<form>

<input name='fabricante' type='number'>

<select name='produto'>

</select>

<button>Enviar</button>

</form>

<p></p>

<script src='jquery.min.js'></script>

<script>
$(function(){


    $('input[name=fabricante]').keyup(function(){ 
        $('select').empty();
        var id = $('input[name=fabricante]').val();
        $.ajax({ // ajax
            type: "POST",
            url: "seleciona_produtos.php",
            data: { fabricante : id }, 
            success: function(result) {
                result = JSON.parse(result);
                console.debug(result);

                if(result.success) {
                    for (var i = 0; i < result.produtos.length; i++) {
                        $('select').append('<option value="' + result.produtos[i].id + '">' + result.produtos[i].nome + "</option>");
                    }
                } else {
                    $('p').text('nao encontrado');
                }

            }
        });
    });

});
</script>

I have a form, in it has the manufacturer (in your case it is a select) which is a number, where the ID of that manufacturer, by changing its value, I send a ajax one-page PHP, picking up the return, if there are products in it, add it to the select of the form, if not only say that did not find.

In the back-end:

<?php 

$produtos = [
    ['id' => 1, 'fabricante' => 1, 'nome' => 'mouse'],
    ['id' => 2, 'fabricante' => 2, 'nome' => 'teclado'],
    ['id' => 3, 'fabricante' => 2, 'nome' => 'monitor'],
    ['id' => 4, 'fabricante' => 3, 'nome' => 'carregador'],
    ['id' => 5, 'fabricante' => 1, 'nome' => 'webcam'],
    ['id' => 6, 'fabricante' => 4, 'nome' => 'microfone'],
];

if(count($_POST) > 0) {

    $_POST['fabricante'] = (int)$_POST['fabricante'];
    $resultado = ['success' => true];


    foreach ($produtos as $value)
        if($value['fabricante'] == $_POST['fabricante'])
            $resultado['produtos'][] = $value;

    if(count($resultado) > 1)
        die(json_encode($resultado));
}

die(json_encode('[message : "erro", success: false]'));
?>

I have a array of products, which simulates all products in the database, after that I go through the array, looking at what theme that manufacturer (simulating the SELECT of SQL), I put the products of this manufacturer in a array results, finally return the result as JSON to the front-end that takes this result and puts it on select form.

But if you still prefer to leave all products hidden in HTML, use:

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

<form>
	<select name='fabricante'>
		<option value='1'>Fabricante 1</option>
		<option value='2'>Fabricante 2</option>
		<option value='3'>Fabricante 3</option>
	</select>

	<select name='produtos'>
	</select>

	<div class="hidden produtos-f1">
		<option value='11'>Mouse</option>
		<option value='31'>Teclado</option>
		<option value='41'>Monitor</option>
	</div>

	<div class="hidden produtos-f2">
		<option value='23'>Processador</option>
		<option value='43'>HD</option>
		<option value='12'>Memoria RAM</option>
		<option value='51'>Bateria</option>
	</div>

	<div class="hidden produtos-f3">
		<option value='1'>Notebook</option>
	</div>
</form>

<script>
$(function(){

	$('.hidden').hide();
  
  $('select[name=produtos]').html($('div.produtos-f1').html());
	

	$('select[name=fabricante]').change(function(){ 
		var id = $('select[name=fabricante]').val();

		$('select[name=produtos]').empty();
		
		$('select[name=produtos]').html($('div.produtos-f' + id).html());

	});
	
});
</script>

Answer 3

****I was able to solve it this way****

I set an example with states and cities because I believe it’s easier to understand what the problem was and what the solution is:

index php.

?>
<?php
        mysql_connect('localhost','root','');
        mysql_selectdb('banco_teste');

$rs = mysql_query("SELECT * FROM tbl_estados ORDER BY nome_estado");
?>

<html>
  <head>
    <title>Atualizando combos com jquery</title>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <script type="text/javascript" src="jquery-1.6.4.js"></script>
    <script type="text/javascript">
    $(document).ready(function(){
        $('#estado').change(function(){
            $('#cidade').load('listaCidades.php?estado='+$('#estado').val());
        });
    });
    </script>
  </head>
  <body>
  <h1>Atualizando combos com jquery</h1>
    <label>Estado:</label>
    <select name="estado" id="estado">
    <?php while($reg = mysql_fetch_object($rs)): ?>
        <option value="<?php echo $reg->id_estado ?>"><?php echo $reg->nome_estado ?></option>
    <?php endwhile; ?>
    </select>
    <br /><br />
    <div id="cidade"></div>
  </body>
</html>


<?php require_once $_SERVER["DOCUMENT_ROOT"] . "/admin/skin/footer/footer.php";?>

lists.php

 <?php 
  require_once $_SERVER["DOCUMENT_ROOT"] . "/admin/includes/connect.php";


$id_estado = $_GET['estado'];

$rs = mysqli_query($conexao,"SELECT * FROM tbl_cidades WHERE id_estado = '$id_estado' ORDER BY nome_cidade");

echo "<label>Cidades: </label><select name='cidade'>";
while($reg = mysqli_fetch_object($rs)){
    echo "<option value='$reg->id_cidade'>$reg->nome_cidade</option>";
}
echo "</select>";

?>

jquery-1.6.4.js
And I downloaded the following js file and saved in the folder that are the other files: http://code.jquery.com/jquery-1.6.4.js

Thanks for all your help!