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

Asked

Viewed 17,051 times

2

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.

  • To simplify can you put the HTML you have when the page loads? you already have both select with all options, or one of the select should change the options depending on the first?

  • I will post an example that I have using Ajax, should help you.

  • I answered a question with the same question: http://answall.com/questions/162062/validates%C3%A7%C3%a3o-de-dois-selects/162066#162066. The only difference is that in change ajax query in PHP to get the list of cameras and then create the second select.

  • @Sergio what I would like is for you to list the SELECT1 information and according to this select, the SELECT2 options appear. Like it’s a country-state relationship. I have the countries Brazil and Portugal in SELECT1, when I select Brazil, appears SP, RJ, etc. But I would like all this information to come from the database, without me having to create the options in the form.

  • 1

    Hi. Welcome to SOPT. Do not put "SOLVED" in the title, and create yourself an answer with your solution (instead of editing and putting it in the body of the question). This site is not a forum. If you haven’t done it yet, do the [tour] and read to [help], ok? :)

  • Do not put the answer within the question. Confuses and disturbs...

  • 1

    Ready, I put as an answer. Thanks for all your help!

  • Not at all! : ) You also don’t have to thank us in the body of the answers. The best way to thank someone who helped you is by voting for their answers. And also consider accepting an answer to indicate to future readers who helped you the most or actually solved your problem. Good luck!

Show 3 more comments

3 answers

2

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.

  • Thank you for the reply Antonio. This information from manufacturers and their respective models is already inserted and related in a table in the database. It is listed the manufacturers in the code I sent above, it is possible from this my database listing, get the related table data without having to create the options in the form?

  • Hello yes it is possible, through an INNER JOIN for example in the query, the best is to see the @ivcs example

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>

  • Thanks for the feedback. Let me ask another question, in this example, I list the MANUFACTURER ID and relate to the products of each manufacturer. But would this be possible just by pulling the relationship from a database table? Example, I already have in the bank a table that tells me that MANUFACTURER 1 manufactures MOUSE. Is it possible from this information in the database the SELECT 1 after listing the MANUFACTURER 1, show the MOUSE? Without having to do this array?

  • I didn’t quite understand your question, maybe the second way I put it is as you are talking, where I took the result of the products separated by manufacturers and for each manufacturer I created a hidden div with their products..

  • But using the first method, the array would be exchanged for a SELECT in the database, for example SELECT * FROM products WHERE manufacturer = $_POST['manufacturer']

  • The best option is the first, I will try here to exchange the array for select in the database and I already give a feedback to see if it worked. Thanks!

  • Actually, I already have the values in the database, I don’t want to fill them in an HTML. I want to select the values of the bank, and according to the selection I made in SELECT1 it show me the options of SELECT2.

  • Okay, anything you can ask.

  • Thanks for the help, I put as an answer how I solved the problem!

Show 2 more comments

0


****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!

  • Use of Ajax to work??

Browser other questions tagged

You are not signed in. Login or sign up in order to post.