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:
I believe that in Example 2 the changes are easier .