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 filled create in PHP a connection to your database Mysql, with PDO thus:
<?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 = "connection ip of your database or localhost if database is local (127.0.0.1)"
where is 'root' is the user of the bank and 'password' is the password of this user to connect to your bank.
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: php combos.
<?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:
The UF loading already happens by default with the loading of this page, by clicking on the UF and choosing some in the list the same triggers an event in select (onchange) and will send Ajax request information to the server. All this using the jQuery with $.post.
The PHP file responsible for receiving such a request has this layout:
Name: call_cities.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 its processing, it returns a JSON in that 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"}]
That one JSON will be worked on $.each of jQuery loading select Cmbcidade, and any choice will be loaded the list referring to that UF.
Example 2:
Using the plugin Jquery Cascadeselect we may have the same effect as Example 1, making minimal changes to Combo.php
and Call_Cidades.php
. This plugin uses HTTP Method GET
and has a peculiarity in the return of information (uses label
and value
for the completion of select
format-dependent JSON)
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_cities2.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 Call_Cidades.php
has several details of change:
I believe in the Example 2 the changes are calmer.
It’s PHP and Mysql????
– user6026