How to select an option in one select and load related data in another?

15

I would like to select an option of a <select> and fill the other <select> with related content.

I will only be able to do this with the information stored in database database or how to do with array? How can I do this?

HTML

<select id='cursos'>
    <option>curso 1</option>
    <option>curso 2</option>
</select>

<select id='atividades'>
    <!-- carregar options ao selecionar uma opção no select anterior -->
</select>
    
asked by anonymous 03.05.2014 / 02:21

2 answers

13

Example 1:

Related tables (ufs and cities)

//ufs
CREATE TABLE 'ufs' (
  'ufid' int(11) NOT NULL AUTO_INCREMENT,
  'uf' varchar(2) NOT NULL,
  PRIMARY KEY ('ufid')
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
//cidades
CREATE TABLE 'cidades' (
  'cidadeid' int(11) NOT NULL AUTO_INCREMENT,
  'ufid' int(11) NOT NULL,
  'cidade' varchar(45) NOT NULL,
  PRIMARY KEY ('cidadeid'),
  KEY 'ufpkufid_idx' ('ufid'),
  CONSTRAINT 'ufpkufid' FOREIGN KEY ('ufid') REFERENCES 'ufs' ('ufid') 
  ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8

With these tables created and populated create in PHP a connection to your database MySQL , with PDO like this:

<?php
    $pdo = new PDO('mysql:dbname=generics;host=localhost', 'root', 'senha', 
                    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"));
    dbname ="name of your database"

  • host ="ip of your database connection or localhost if database is local (127.0.0.1)"

  • 'root' is the user of the database and 'password' is the user's password to log in to your database.

    / li>

This connection will be used in every example, so create a separate file with the name of conn.php (the name may be of your preference), and put the code in it.

PHP

Name: combos.php

<?php include 'conn.php'; ?>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>Combos Dependentes</title>
<script src="jquery-1.11.0.min.js" type="text/javascript"></script>
</head>
<body>
<select id="CmbUF"> 
    <option value="">Selecione a UF</option>
    <?php
        foreach($pdo->query('SELECT ufid, uf FROM ufs order by uf') as $row){
            echo '<option value="'.$row['ufid'].'">'.$row['uf'].'</option>';
        }       
    ?>
</select>
<select id="CmbCidade"> 
</select>
<script type="text/javascript">
    $(document).ready(function() {
        $('#CmbUF').change(function(e) {
            $('#CmbCidade').empty();
            var id = $(this).val();
            $.post('call_cidades.php', {ufid:id}, function(data){
                var cmb = '<option value="">Selecione a Cidade</option>';
                $.each(data, function (index, value){
                    cmb = cmb + '<option value="' + value.cidadeid + '">' + value.cidade + '</option>';;
                });
                $('#CmbCidade').html(cmb);
            }, 'json');
        });
    });
</script>
</body>
</html>

The same rendered by the browser will look like this:

UFloadingalreadyhappensbydefaultwithloadingthispage,clickingontheUFandchoosingsomeinthelistwilltriggeraneventintheselect(onchange ) and will send Ajax request information to the server. All of this using jQuery with $ .post .

The PHP file responsible for receiving such a request has this layout:

Name: call_cidades.php

<?php       
    if (isset($_SERVER["HTTP_X_REQUESTED_WITH"]) && $_SERVER["HTTP_X_REQUESTED_WITH"] === "XMLHttpRequest"){
        include 'conn.php';
        $ufid = filter_input(INPUT_POST, 'ufid', FILTER_SANITIZE_NUMBER_INT);
        if ($ufid){
            $query = $pdo->prepare('SELECT cidadeid, cidade FROM cidades where ufid=? ORDER BY cidade');
            $query->bindParam(1, $ufid, PDO::PARAM_INT);
            $query->execute();          
            echo json_encode($query->fetchAll());
            return;
        }       
    }
    echo NULL;

After processing, it returns a JSON in this format:

[{"cidadeid":"5","0":"5","cidade":"BOM JARDIM","1":"BOM JARDIM"},{"cidadeid":"4","0":"4","cidade":"CAMBUCI","1":"CAMBUCI"},{"cidadeid":"3","0":"3","cidade":"RIO DE JANEIRO","1":"RIO DE JANEIRO"}]

This JSON will be worked on $.each of jQuery by loading the select CmbCity, and any choice will be loaded into the list for that UF.

Example 2:

Using the Jquery CascadeSelect plugin can have the same effect as Example 1 , making minor changes to Combo.php and Call_Cidades.php . This plugin uses HTTP Method GET and has a peculiarity in the information return (uses label and value to fill% dependent% in JSON format)

PHP

Name: combo2.php

<?php include 'conn.php'; ?>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>Combos Dependentes</title>
<script src="jquery-1.11.0.min.js" type="text/javascript"></script>
<!--https://code.google.com/p/jquery-cascade/-->
<script src="jquery.cascade-select.js" type="text/javascript"></script>
</head>
<body>
<select id="CmbUF"> 
    <option value="">Selecione a UF</option>
    <?php
        foreach($pdo->query('SELECT ufid, uf FROM ufs order by uf') as $row){
            echo '<option value="'.$row['ufid'].'">'.$row['uf'].'</option>';
        }       
    ?>
</select>
<select id="CmbCidade"> 
</select>
<script type="text/javascript">
    $(document).ready(function() {
        $('#CmbUF').cascade({
                source: "call_cidades2.php",
                cascaded: "CmbCidade",
                extraParams: { ufid: function(){ return $('#CmbUF').val();  } },
                dependentLoadingLabel: "Carregando Cidades ...",
                dependentNothingFoundLabel: "Não existe cidades",
                dependentStartingLabel: "Selecione a UF",
        });     
    });
</script>
</body>
</html>

Name: call_cidades2.php

<?php           
    if (isset($_SERVER["HTTP_X_REQUESTED_WITH"]) && $_SERVER["HTTP_X_REQUESTED_WITH"] === "XMLHttpRequest"){        
        include 'conn.php';
        $ufid = filter_input(INPUT_GET, 'ufid', FILTER_SANITIZE_NUMBER_INT);
        if ($ufid){
            $query = $pdo->prepare('SELECT cidadeid as value, cidade as label FROM cidades where ufid=? ORDER BY cidade');
            $query->bindParam(1, $ufid, PDO::PARAM_INT);
            $query->execute();          
            echo json_encode($query->fetchAll());
            return;
        }       
    }
    echo NULL;

Note: No select has several change details:

  • Call_Cidades.php now as a way to retrieve information in INPUT_GET .

  • SQL is now renamed to filter_input and cidadeid as value to standardize return according to the plugin

I believe that in Example 2 the changes are easier .

    
03.05.2014 / 16:37
0
$dados = $conex->query('SELECT nome, email FROM cadastros');
while ($linha = $dados->fetch(PDO::FETCH_OBJ)) {
  echo $linha->nome . ' - ' . $linha->email;
  echo '<br>';
}
    
13.11.2017 / 14:11