Load a select depending on another with php

Question

Load a select depending on another with php

Navigation

#1 by (2 votes)

1

I want to load my select where I show the registered programs and load in another select the subjects associated with the program that I chose immediately I am working with the php myadmin database (It should be clear that I have not done it yet because I do not know how to do it)

A continuation of the code of the consultations

<?php 
include_once("configuracionbd.php");
 $consultaPrograma =mysqli_query($mysqli,"SELECT * FROM programa ORDER BY id_programa");
 $consultaAsignatura = mysqli_query($mysqli,"SELECT * FROM asignatura ORDER BY id_asignatura");
?>

And the code of how I'm loading the select I return and clarify, this way of loading them still does not provide me what I'm looking for

    <div class="form-group">
        <label for="selector1" class="col-sm-2 control-label">Programa: </label>
        <div class="col-sm-8"><select name="codPrograma" id="selector1" class="form-control1">
    <option value="000">Seleccione</option>
     <?php
       while ($row=mysqli_fetch_array($consultaPrograma)) {?>
<option value="<?php echo $row['id_programa']?>"><?php echo $row['nombre_programa']?></option>
<?php } ?>
?>
</select></div>
</div> 
    <div class="form-group">
        <label for="selector1" class="col-sm-2 control-label">Asignatura</label>
            <div class="col-sm-8"><select name="codAsignatura" id="" class="form-control1">
       <option value="000">Seleccione</option>
         <?php
           while ($row=mysqli_fetch_array($consultaAsignatura)) {?>
                                            <option value="<?php echo $row['id_asignatura']?>"><?php echo $row['nombre_asignatura']?></option>
<?php } ?>
?></select></div>
</div>

html php

asked by Carlos Alberto Guerrero Navarr 29.10.2018 в 15:46

source

1 answer

How to know if I click on a line in c # Why does not angular-tour work in sight?

score 2 · Accepted Answer

To achieve this you must use Ajax . Here is an example of how it is done.

Let's start by creating the two tables that we are going to use, in one I have some rock bands and in the other some discs that belong to the bands.

(MySQL)

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

CREATE TABLE IF NOT EXISTS 'bandas' (
  'id' int(11) NOT NULL,
  'nombre' varchar(50) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO 'bandas' ('id', 'nombre') VALUES
(1, 'Metallica'),
(2, 'Sabaton'),
(3, 'Freedom Call'),
(4, 'Stratovarius');

CREATE TABLE IF NOT EXISTS 'discos' (
  'id' int(11) NOT NULL,
  'banda_id' int(11) NOT NULL,
  'nombre' varchar(50) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

INSERT INTO 'discos' ('id', 'banda_id', 'nombre') VALUES
(1, 1, 'Kill''em All'),
(2, 1, 'Master Of Puppets'),
(3, 1, 'Metallica'),
(4, 2, 'The Last Stand'),
(5, 2, 'Heroes'),
(6, 2, 'Primo Victoria'),
(7, 3, 'Master Of Light'),
(8, 3, 'Beyond'),
(9, 3, 'Ages Of Light'),
(10, 4, 'Eternal'),
(11, 4, 'Polaris'),
(12, 4, 'Infinite');


ALTER TABLE 'bandas'
  ADD PRIMARY KEY ('id');

ALTER TABLE 'discos'
  ADD PRIMARY KEY ('id'), ADD KEY 'discos_r01_idx' ('banda_id');


ALTER TABLE 'bandas'
  MODIFY 'id' int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;
ALTER TABLE 'discos'
  MODIFY 'id' int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=13;

ALTER TABLE 'discos'
ADD CONSTRAINT 'discos_r01' FOREIGN KEY ('banda_id') REFERENCES 'bandas' ('id') ON DELETE NO ACTION ON UPDATE NO ACTION;

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

CREATE TABLE IF NOT EXISTS 'bandas' (
  'id' int(11) NOT NULL,
  'nombre' varchar(50) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

INSERT INTO 'bandas' ('id', 'nombre') VALUES
(1, 'Metallica'),
(2, 'Sabaton'),
(3, 'Freedom Call'),
(4, 'Stratovarius');

CREATE TABLE IF NOT EXISTS 'discos' (
  'id' int(11) NOT NULL,
  'banda_id' int(11) NOT NULL,
  'nombre' varchar(50) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

INSERT INTO 'discos' ('id', 'banda_id', 'nombre') VALUES
(1, 1, 'Kill''em All'),
(2, 1, 'Master Of Puppets'),
(3, 1, 'Metallica'),
(4, 2, 'The Last Stand'),
(5, 2, 'Heroes'),
(6, 2, 'Primo Victoria'),
(7, 3, 'Master Of Light'),
(8, 3, 'Beyond'),
(9, 3, 'Ages Of Light'),
(10, 4, 'Eternal'),
(11, 4, 'Polaris'),
(12, 4, 'Infinite');


ALTER TABLE 'bandas'
  ADD PRIMARY KEY ('id');

ALTER TABLE 'discos'
  ADD PRIMARY KEY ('id'), ADD KEY 'discos_r01_idx' ('banda_id');


ALTER TABLE 'bandas'
  MODIFY 'id' int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;
ALTER TABLE 'discos'
  MODIFY 'id' int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=13;

ALTER TABLE 'discos'
ADD CONSTRAINT 'discos_r01' FOREIGN KEY ('banda_id') REFERENCES 'bandas' ('id') ON

Now we are going to create 3 php files, one will be only for the connection with the database, an index.php and another get_discos.php where we will get the options of the second select.

con_db.php

<?php

function conDb(){
  $con = mysqli_connect('localhost', 'root', '', 'tutoriales-kiuvox');

  if(!$con){
    print_r(mysqli_connect_error());
    return false;
  }else{
    $con->set_charset("utf8");
    return $con;
  }
}


<?php

function conDb(){
  $con = mysqli_connect('localhost', 'root', '', 'tutoriales-kiuvox');

  if(!$con){
    print_r(mysqli_connect_error());
    return false;
  }else{
    $con->set_charset("utf8");
    return $con;
  }
}

Index.php

$con = conDb();
if(!$con){
  die("<br/>Sin conexi&oacute;n.");
}

/*obtenemos los datos del primer select*/
$sql = "select * from bandas";
$query = mysqli_query($con, $sql);
$filas = mysqli_fetch_all($query, MYSQLI_ASSOC); 
mysqli_close($con);
?>
<!DOCTYPE html>
<html>
  <head>
    <meta charset="UTF-8">
    <title>Select Ajax</title>
  </head>
  <body>
    <label>Bandas</label>
    <select id="bandas">
      <option value="">- Seleccione -</option>
      <?php foreach ($filas as $op): //llenar las opciones del primer select ?>
      <option value="<?= $op['id'] ?>"><?= $op['nombre'] ?></option>  
      <?php endforeach; ?>
    </select>

    <br/><br/>
    <label>Discos</label>
    <select id="discos" disabled="">
      <option value="">- Seleccione -</option>
    </select>

    <br/><br/>
    Opci&oacute;n seleccionada: <span style="font-weight: bold;" id="disco_sel"></span>

    <!-- Agregamos la libreria Jquery -->
    <script type="text/javascript" src="jquery-3.2.0.min.js"></script>

    <!-- Iniciamos el segmento de codigo javascript -->
    <script type="text/javascript">
      $(document).ready(function(){
        var discos = $('#discos');
        var disco_sel = $('#disco_sel');

        //Ejecutar accion al cambiar de opcion en el select de las bandas
        $('#bandas').change(function(){
          var banda_id = $(this).val(); //obtener el id seleccionado

          if(banda_id !== ''){ //verificar haber seleccionado una opcion valida

            /*Inicio de llamada ajax*/
            $.ajax({
              data: {banda_id:banda_id}, //variables o parametros a enviar, formato => nombre_de_variable:contenido
              dataType: 'html', //tipo de datos que esperamos de regreso
              type: 'POST', //mandar variables como post o get
              url: 'get_discos.php' //url que recibe las variables
            }).done(function(data){ //metodo que se ejecuta cuando ajax ha completado su ejecucion             

              discos.html(data); //establecemos el contenido html de discos con la informacion que regresa ajax             
              discos.prop('disabled', false); //habilitar el select
            });
            /*fin de llamada ajax*/

          }else{ //en caso de seleccionar una opcion no valida
            discos.val(''); //seleccionar la opcion "- Seleccione -", osea como reiniciar la opcion del select
            discos.prop('disabled', true); //deshabilitar el select
          }    
        });


        //mostrar una leyenda con el disco seleccionado
        $('#discos').change(function(){
          $('#disco_sel').html($(this).val() + ' - ' + $('#discos option:selected').text());
        });

      });
    </script>    
  </body>
</html>

get_discos.php

<?php
require_once './con_db.php'; //libreria de conexion a la base

$banda_id = filter_input(INPUT_POST, 'banda_id'); //obtenemos el parametro que viene de ajax

if($banda_id != ''){ //verificamos nuevamente que sea una opcion valida
  $con = conDb();
  if(!$con){
    die("<br/>Sin conexi&oacute;n.");
  }

  /*Obtenemos los discos de la banda seleccionada*/
  $sql = "select id, nombre from discos where banda_id = ".$banda_id;  
  $query = mysqli_query($con, $sql);
  $filas = mysqli_fetch_all($query, MYSQLI_ASSOC); 
  mysqli_close($con);
}

/**
 * Como notaras vamos a generar código 'html', esto es lo que sera retornado a 'ajax' para llenar 
 * el combo dependiente
 */
?>

<option value="">- Seleccione -</option>
<?php foreach($filas as $op): //creamos las opciones a partir de los datos obtenidos ?>
<option value="<?= $op['id'] ?>"><?= $op['nombre'] ?></option>
<?php endforeach; ?>

I hope it will help you understand the process and apply it to your project. Greetings!