Mostra la font per a Comunitats, províncies, municipis. AJAX i bases de dades

=Introducció=
Anem a practicar un exemple d'AJAX amb una base de dades dels municipis de l'estat. La base de dades comunitats-provincies-municipis té una estructura simple de relacions 1:M. Una comunitat té vàries províncies; i una província té molts municipis. A l'inrevés, un municipi només pertany a una província i a una comunitat.

En aquesta pràctica farem una llista desplegable amb totes les comunitats; i un cop seleccionada una comunitat, omplirem de forma dinàmica i amb una crida asíncrona la llista de províncies de la comunitat seleccionada. Després tu continuaràs completant l'exercici: quan seleccionis una província s'ompliran els municipis.

=Referències=
*www.ine.es (Institut Nacional d'Estadística per trobar una llista completa i actualitzada dels municipis)

D'aquest enllaç s'ha trobat la informació de les comunitats, províncies i municipis.

=Desenvolupament=
==Definició de la base de dades==
La base de dades de municipis la tens disponible en el '''docker'''. Pots connectar-te a la base de dades tant localment com al docker.

'''dades_municipis_mysql.sql''':
<pre>
#CREATE DATABASE municipis DEFAULT CHARACTER SET utf8;
#USE municipis;

#CREATE USER alumne IDENTIFIED BY 'keiL2lai';
#GRANT ALL ON municipis.* TO alumne@localhost identified by "keiL2lai";
#flush privileges;


drop table municipis;
drop table provincies;
drop table comunitats;

CREATE TABLE comunitats(
id_com smallint primary key,
comunitat varchar(80) not null,
abr_com varchar(4)
);

CREATE TABLE provincies(
id_prov smallint primary key,
provincia varchar(30) not null,
id_com  smallint references comunitat(id_com)
);

create table municipis(
id_prov smallint references provincies(id_prov),
id_mun smallint primary key,
municipi varchar(120),
superficie smallint,
habitants smallint
);

insert into comunitats values (1,'Catalunya','CAT');
insert into comunitats values (2,'Aragón','ARG');
insert into comunitats values (3,'Comunidad Valenciana','VAL');
insert into comunitats values (4,'Illes Balears','IB');
insert into comunitats values (5,'Navarra','NAV');
insert into comunitats values (6,'La Rioja','RIO');
insert into comunitats values (7,'Euskadi','EUZ');
insert into comunitats values (8,'Cantabria','CAN');
insert into comunitats values (9,'Asturias','AST');
insert into comunitats values (10,'Galicia','GAL');
insert into comunitats values (11,'Castilla León','CL');
insert into comunitats values (12,'Madrid','MAD');
insert into comunitats values (13,'Extremadura','EXT');
insert into comunitats values (14,'Castilla la Mancha','CM');
insert into comunitats values (15,'Murcia','MUR');
insert into comunitats values (16,'Andalucía','AND');
insert into comunitats values (17,'Islas Canarias','IC');
insert into comunitats values (18,'Ceuta','CEU');
insert into comunitats values (19,'Melilla','MEL');

insert into provincies values (01,'Álava',7);
insert into provincies values (02,'Albacete',14);
insert into provincies values (03,'Alacant',3);
insert into provincies values (04,'Almería',16);
insert into provincies values (05,'Ávila',11);
insert into provincies values (06,'Badajoz',13);
insert into provincies values (07,'Balears',4);
insert into provincies values (08,'Barcelona',1);
insert into provincies values (09,'Burgos',11);
insert into provincies values (10,'Cáceres',13);
insert into provincies values (11,'Cádiz',16);
insert into provincies values (12,'Castelló',3);
insert into provincies values (13,'Ciudad Real',14);
insert into provincies values (14,'Córdoba',16);
insert into provincies values (15,'A Coruña',10);
insert into provincies values (16,'Cuenca',14);
insert into provincies values (17,'Girona',1);
insert into provincies values (18,'Granada',16);
insert into provincies values (19,'Guadalajara',14);
insert into provincies values (20,'Gipuzkoa',7);
insert into provincies values (21,'Huelva',16);
insert into provincies values (22,'Huesca',2);
insert into provincies values (23,'Jaén',16);
insert into provincies values (24,'León',11);
insert into provincies values (25,'Lleida',1);
insert into provincies values (26,'La Rioja',6);
insert into provincies values (27,'Lugo',10);
insert into provincies values (28,'Madrid',12);
insert into provincies values (29,'Málaga',16);
insert into provincies values (30,'Murcia',15);
insert into provincies values (31,'Navarra',5);
insert into provincies values (32,'Ourense',10);
insert into provincies values (33,'Asturias',9);
insert into provincies values (34,'Palencia',11);
insert into provincies values (35,'Las Palmas',17);
insert into provincies values (36,'Pontevedra',10);
insert into provincies values (37,'Salamanca',11);
insert into provincies values (38,'Santa Cruz de Tenerife',17);
insert into provincies values (39,'Cantabria',8);
insert into provincies values (40,'Segovia',11);
insert into provincies values (41,'Sevilla',16);
insert into provincies values (42,'Soria',11);
insert into provincies values (43,'Tarragona',1);
insert into provincies values (44,'Teruel',2);
insert into provincies values (45,'Toledo',14);
insert into provincies values (46,'València',3);
insert into provincies values (47,'Valladolid',11);
insert into provincies values (48,'Bizkaia',7);
insert into provincies values (49,'Zamora',11);
insert into provincies values (50,'Zaragoza',2);
insert into provincies values (51,'Ceuta',18);
insert into provincies values (52,'Melilla',19);

insert into municipis values (1,1,'Alegría-Dulantzi',68,34663);
insert into municipis values (1,2,'Amurrio',23,37775);
insert into municipis values (1,3,'Añana',43,19466);
insert into municipis values (1,4,'Aramaio',53,5720);
insert into municipis values (1,5,'Armiñón',22,37639);
...
</pre>
Només se't dóna 5 insercions a municipis. El script complet amb els 8000 municipis te'l pots descarregar en format sql per a mysql:
*[[Fitxer:Dades municipis mysql.sql.zip]]
==scripts inicials (v1)==
Codi per a la versió 1:

'''fitxer index.php''':
<pre>
<?php include("open_db.php"); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="es" lang="es">
<head>
<title>Comunitats, províncies, municipis</title>
<meta name="keywords" content="" />
<meta name="description" content="" />
<meta http-equiv="content-type" content="text/html charset=utf-8" />
<script>
function canvi_comunitat(id_com)
{
	//alert(id_com);
	let txt;

	let xmlhttp=new XMLHttpRequest();

	xmlhttp.onreadystatechange=function()
	{
		if (xmlhttp.readyState==4 && xmlhttp.status==200)
		{
			txt=xmlhttp.responseText;
			document.getElementById('provincia').innerHTML=txt;
		} else {
			document.getElementById('provincia').innerHTML = "<img src=\"img/ajax_wait.gif\" />";
		}
	}
	xmlhttp.open("GET","cercar_provincies.php?id_com="+id_com,true);
	xmlhttp.send();

}
</script>
</head>
<body>
<h1>Navega per les províncies</h1>
<div id="comunitat">
	<select name="comunitat" onchange="canvi_comunitat(this.value)">
<?php
$sql = "SELECT * FROM comunitats order by id_com";
$result = mysqli_query($conn, $sql);

if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    die($message);
}

while($row = mysqli_fetch_assoc($result)) {
	echo "<option value=".$row['id_com'].">".$row['comunitat']."</option>";
}

?>
	</select>
</div>
<div id="provincia">
</div>
<div id="info">
</div>
</body>
</html>
<?php include("close_db.php"); ?>
</pre>

'''Fitxer cercar_provincies.php''':
<pre>
<?php
sleep(1);
include("open_db.php");
	
$id_com = $_GET["id_com"];
//echo $id_com;

$sql = "SELECT * FROM provincies where id_com=$id_com order by id_prov";

$result = mysqli_query($conn, $sql);

if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    die($message);
}
	
echo "<select name=\"provincia\" onchange=\"canvi_provincia(this.value)\">";
while ($row = mysqli_fetch_assoc($result)) {
	echo "<option value=".$row['id_prov'].">".$row['provincia']."</option>";
}
echo "</select>";
include("close_db.php");
?>

</pre>
Fixa't bé que un dels avantatges de passar les dades en format GET en comptes de POST és que pots treballar/depurar amb el script ''cercar_provincies.php'' sense haver de fer crides AJAX. Per exemple, prova:
*http://localhost/provincies_ajax/cercar_provincies.php?id_com=2
*http://localhost/provincies_ajax/cercar_provincies.php?id_com=5
*http://localhost/provincies_ajax/cercar_provincies.php?id_com=8
És interessant poder descomposar el problema en parts independents.

'''fitxer open_db.php''':
<pre>
<?php
$conn = mysqli_connect("localhost", "alumne", "keiL2lai");
if (!$conn) {
    $log->error('Could not connect: ' . mysql_error());
    die('Could not connect: ' . mysql_error());
}
mysqli_select_db($conn,"municipis") or die('Could not select municipis database.');
mysqli_set_charset($conn, 'utf8');
?>
</pre>
la línia
<pre>
mysqli_set_charset($conn, 'utf8');
</pre>
ha tingut importància per tal d'eliminar problemes amb els accents. És important també que la codificació de caràcters de la base de dades sigui utf-8, i que si incorporem les dades d'un fitxer SQL, aquest fitxer també estigui codificat en el mateix format utf-8. En definitiva, ens hem d'assegurar que les dades i els programes que utilitzen aquestes dades treballin amb la mateixa codificació.

'''fitxer close_db.php''':
<pre>
<?php
mysqli_close($conn);
?>
</pre>

==scripts inicials (v2)==
En la versió 1, en el fitxer cercar_provincies.php estàvem maquetant totes les opcions de la select box. Aquesta manera de fer no és la correcta, doncs estem delegant en el cantó del servidor (PHP) la maquetació de les opcions de la select. Aquesta feina s'ha de fer en el cantó del client.

Ens hem d'acostumar a pensar en el model MVC (model vista controlador). En el servidor resideixen les dades, i no podem delegar en el servidor res que tingui a veure amb el disseny o la maquetació. Això ho hem de fer així perquè amb les mateixes crides al servidor podem fer molts front-ends i interfaces d'usuari. Pensem com si fos una API.

Per tant, millorarem en aquesta v2 el codi fent que l'enviament de la informació sigui ''agnòstic''. Quedaria de la següent manera:

'''index.php''':
<pre>
<?php include("open_db.php"); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="es" lang="es">
<head>
<title>Comunitats, províncies, municipis</title>
<meta name="keywords" content="" />
<meta name="description" content="" />
<meta http-equiv="content-type" content="text/html charset=utf-8" />
<script>
function canvi_comunitat(id_com)
{
	//alert(id_com);
	let txt;
	let xmlhttp=new XMLHttpRequest();
	xmlhttp.onreadystatechange=function()
	{
		if (xmlhttp.readyState==4 && xmlhttp.status==200)
		{
			txt=xmlhttp.responseText;
			array_provincies = txt.split(";");
			selectboxcad = "<select name='provincia' onchange='canvi_provincia(this.value)' >";
			for (var i=0; i < array_provincies.length; i++) {
				var info_provincia = array_provincies[i].split('*');
				selectboxcad += "<option value=" +  info_provincia[0] + ">" + info_provincia[1] + "</option>";
			}
			selectboxcad += "</select>";


			document.getElementById('provincia').innerHTML=selectboxcad;
		} else {
			document.getElementById('provincia').innerHTML = "<img src=\"img/ajax_wait.gif\" />";
		}
	}
	xmlhttp.open("GET","cercar_provincies.php?id_com="+id_com,true);
	xmlhttp.send();

}
</script>
</head>
<body>
<h1>Navega per les províncies</h1>
<div id="comunitat">
	<select name="comunitat" onchange="canvi_comunitat(this.value)">
<?php
$sql = "SELECT * FROM comunitats order by id_com";
$result = mysqli_query($conn, $sql);

if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    die($message);
}

while($row = mysqli_fetch_assoc($result)) {
	echo "<option value=".$row['id_com'].">".$row['comunitat']."</option>";
}

?>
	</select>
</div>
<div id="provincia">
</div>
<div id="info">
</div>
</body>
</html>
<?php include("close_db.php"); ?>
</pre>

'''cercar_provincies.php''':
<pre>
<?php
sleep(1);
include("open_db.php");
	
$id_com = $_GET["id_com"];
//echo $id_com;

$sql = "SELECT * FROM provincies where id_com=$id_com order by id_prov";

$result = mysqli_query($conn, $sql);

if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    die($message);
}
	

$comptador = 0;
$cad = "";
while ($row = mysqli_fetch_assoc($result)) {
	//aquest és el format amb què enviem les dades al client (de moment no ho fem amb JSON)
	//províncies separades per ;. * entre el id i el nom de la província
	if ($comptador != 0) $cad .= ";";
	$cad .= $row['id_prov']."*".$row['provincia'];
	$comptador++;
}

echo $cad;
?>
</pre>
Ara ja està millor. De totes maneres la separació de la informació de les províncies utiltizant ; i * és una mica forçada. I és que la format correcte per traspassar informació del servidor al client és per exemple utilitzar JSON, que és com ho farem a continuació.
==scripts inicials (v3)==
PHP i Javascript. Intercanvi d'informació amb JSON:
*[[Formats_d%27intercanvi_d%27informació._JSON]]
script '''index.php''':
<pre>
<?php include("open_db.php"); ?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="es" lang="es">
<head>
<title>Comunitats, províncies, municipis</title>
<meta name="keywords" content="" />
<meta name="description" content="" />
<meta http-equiv="content-type" content="text/html charset=utf-8" />
<script>
function canvi_comunitat(id_com)
{
	//alert(id_com);
	let txt;

	let xmlhttp=new XMLHttpRequest();
	
	xmlhttp.onreadystatechange=function()
	{
		if (xmlhttp.readyState==4 && xmlhttp.status==200)
		{
			txt=xmlhttp.responseText;
			//alert(txt);
		    var array_provincies = JSON.parse(txt);
			selectboxcad = "<select name='provincia' onchange='canvi_provincia(this.value)' >";
			array_provincies.forEach(function (valor) {

			selectboxcad += "<option value=" +  valor.id_prov + ">" + valor.provincia + "</option>";
			})
			selectboxcad += "</select>";

		    document.getElementById('provincia').innerHTML = selectboxcad;
		} else {
			document.getElementById('provincia').innerHTML = "<img src=\"img/ajax_wait.gif\" />";
		}
	}
	xmlhttp.open("GET","cercar_provincies.php?id_com="+id_com,true);
	xmlhttp.send();

}
</script>
</head>
<body>
<h1>Navega per les províncies</h1>
<div id="comunitat">
	<select name="comunitat" onchange="canvi_comunitat(this.value)">
<?php
$sql = "SELECT * FROM comunitats order by id_com";
$result = mysqli_query($conn, $sql);

if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    die($message);
}

while($row = mysqli_fetch_assoc($result)) {
	echo "<option value=".$row['id_com'].">".$row['comunitat']."</option>";
}

?>
	</select>
</div>
<div id="provincia">
</div>
<div id="info">
</div>
</body>
</html>
<?php include("close_db.php"); ?>
</pre>
script '''cercar_provincies.php''':
<pre>
<?php
sleep(1);
include("open_db.php");
	
$id_com = $_GET["id_com"];
//echo $id_com;

$sql = "SELECT * FROM provincies where id_com=$id_com order by id_prov";

$result = mysqli_query($conn, $sql);

if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    die($message);
}
	

// Fetch all
$allrows = mysqli_fetch_all($result, MYSQLI_ASSOC);

$result->close();

$myJSON = json_encode($allrows);
echo $myJSON;

?>
</pre>
==scripts inicials (v4)==
Ja estem en la fase final, encara queden coses importants:
*Netejar el codi, separar codi html, js i php
*es creen les select i les opcions de forma dinàmica.
*S'omple automàticament les províncies de la primera comunitat.
script ''index.html'':
<pre>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="es" lang="es">
<head>
<title>Comunitats, províncies, municipis</title>
<meta name="keywords" content="" />
<meta name="description" content="" />
<meta http-equiv="content-type" content="text/html charset=utf-8" />
<script src="./js/script.js"></script>
</head>
<body">
<h1>Navega per les províncies</h1>
<div id="comunitat"></div>
<div id="provincia">
</div>
<div id="info">
</div>
</body>
</html>
</pre>
script ''script.js'':
<pre>
window.addEventListener("load", function(){
	cercar_comunitats();
});

function cercar_comunitats()
{
	let txt;

	let xmlhttp=new XMLHttpRequest();
	
	xmlhttp.onreadystatechange=function()
	{
		if (xmlhttp.readyState==4 && xmlhttp.status==200)
		{
			txt=xmlhttp.responseText;
			//alert(txt);
		    var array_comunitats = JSON.parse(txt);
			let sel = document.createElement("select");
			array_comunitats.forEach(function (valor) {
				let option = document.createElement("option")
				//option.value = valor.id_com;
				//option.text = valor.comunitat;
				// o bé:
				option.setAttribute("value", valor.id_com);
				option.appendChild( document.createTextNode( valor.comunitat ) );
				sel.appendChild(option);
			});
			//sel.addEventListener("change", function() {canvi_comunitat(sel.value)});
			//amb funció fletxa:
			sel.addEventListener("change", () => {canvi_comunitat(sel.value)});
			document.getElementById('comunitat').innerHTML = "";
			document.getElementById('comunitat').appendChild(sel);
			//propaguem a la primera comunitat:
			canvi_comunitat(1);
		} else {
			document.getElementById('comunitat').innerHTML = "<img src=\"img/ajax_wait.gif\" />";
		}
	}
	xmlhttp.open("GET","./php/cercar_comunitats.php",true);
	xmlhttp.send();

}

function canvi_comunitat(id_com)
{
	//alert(id_com);
	let txt;

	let xmlhttp=new XMLHttpRequest();
	
	xmlhttp.onreadystatechange=function()
	{
		if (xmlhttp.readyState==4 && xmlhttp.status==200)
		{
			txt=xmlhttp.responseText;
			//alert(txt);
		    var array_provincies = JSON.parse(txt);
			let sel = document.createElement("select");
			array_provincies.forEach(function (valor) {
				let option = document.createElement("option")
				option.setAttribute("value", valor.id_prov);
				option.appendChild( document.createTextNode( valor.provincia ) );
				sel.appendChild(option);
			});
			sel.addEventListener("change", () => {canvi_provincia(sel.value)});
			document.getElementById('provincia').innerHTML = "";
			document.getElementById('provincia').appendChild(sel);
		} else {
			document.getElementById('provincia').innerHTML = "<img src=\"img/ajax_wait.gif\" />";
		}
	}
	xmlhttp.open("GET","./php/cercar_provincies.php?id_com="+id_com,true);
	xmlhttp.send();
}

function canvi_provincia(id_prov) {
	console.log(id_prov);
}
</pre>
script ''cercar_comunitats.php'':
<pre>
<?php
include("open_db.php");

sleep(1);
include("open_db.php");

$sql = "SELECT * FROM comunitats order by id_com";

$result = mysqli_query($conn, $sql);

if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    die($message);
}

// Fetch all
$allrows = mysqli_fetch_all($result, MYSQLI_ASSOC);

$result->close();

$myJSON = json_encode($allrows);
echo $myJSON;

include("close_db.php");
?>
</pre>
script ''cercar_provincies.php'':
<pre>
<?php
include("open_db.php");

sleep(1);
include("open_db.php");
	
$id_com = $_GET["id_com"];
//echo $id_com;

$sql = "SELECT * FROM provincies where id_com=$id_com order by id_prov";

$result = mysqli_query($conn, $sql);

if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    die($message);
}
	

// Fetch all
$allrows = mysqli_fetch_all($result, MYSQLI_ASSOC);

$result->close();

$myJSON = json_encode($allrows);
echo $myJSON;

include("close_db.php");
?>
</pre>

==Descàrrega del codi==
*[[Fitxer:Municipis ajax 4 versions.zip]]

=Feina per l'alumne=
#Hauràs d'incorporar els municipis. Quan cliquis sobre una província, has d'omplir la llista desplegable de tots els municipis d'aquella província.
#Recorda que quan cliquis sobre una província hauràs de netejar la llista de municipis; quan cliquis sobre una comunitat hauràs de netejar la llista de províncies i municipis. La informació que apareix per pantalla sempre ha de ser coherent.
#S'ha d'implementar la propagació. Quan cliquis sobre una comunitat s'omple la llista de províncies (això ja ho fa la v4); alhora, per la província seleccionada s'haurà d'omplir immediatament la llista de municipis.
#wikipedia. Fés un enllaç al link de la wikipedia per als 8000 municipis. Evidentment no ho has de fer manualment, sinó automàticament a partir del nom dels municipis. Considera les diferents causístiques que hi ha (municipis amb apòstrofs, accents,...). No cal que sigui perfecte per als 8000 municipis, però sí per a la majoria. Pots utilitzar un ''iframe'' o similar per incrustar la informació de la wikipedia a la teva pàgina. Per facilitar la feina a la wikipèdia pots substituir els espais en blanc del municpi per underscore (''http://ca.wikipedia.org/wiki/Arenys_de_Munt'').
#Recorda dissenyar aquesta mini-aplicació amb uns estàndards correctes i moderns, incorporant un fitxer CSS.
#opcional. Una altra possibilitat és afegir un ''iframe'' d'un mapa del municipi seleccionat. En aquest cas, la url que has de carregar en el iframe és per exemple: ''https://www.google.es/maps/place/17244+Cassà+de+la+Selva'' (pots substituir els espais en blanc del municipi pel signe +).

'''NOTA'''. En el cas de què vulguis fer un request del nom d'un municipi amb el mètode GET, recorda que no funcionen els espais en blanc, i que has de substituir els espais en blanc per %20. Per ex, ''http://localhost/prova.php?municipi=Arenys de Munt'' no funciona, hauria de ser ''http://localhost/prova.php?municipi=Arenys%20de%20Munt''

=Entrega=
Els alumnes entregaran al Classroom, dins del termini, tots els fitxers generats (carpeta comprimida), i un pdf amb les captures de pantalla principals. Tanmateix, és obligatori ensenyar al professor, a classe, el funcionament correcte de l'exercici.

{{Autor}}, febrer 2022
[[Categoria: Institut Jaume Balmes]]
[[Categoria: Assignatura DAW-M06-WEC]]