Comunitats, províncies, municipis. AJAX i bases de dades

De Wikijoan
Revisió de 16:25, 6 març 2019; Joan (Discussió | contribucions)
(dif) ←Versió més antiga | Versió actual (dif) | Versió més nova→ (dif)
Dreceres ràpides: navegació, cerca

Contingut

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 simples 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.

Referències

A Internet podem trobar molts exemples d'aplicacions web que fan crides AJAX a bases de dades per tal de mostrar informació actualitzada al visitant. 1 exemple escollits entre mil:

Fixa't bé que la capa de disseny és molt important.

Desenvolupament

Definició de la base de dades

dades_municipis_mysql.sql:

#CREATE DATABASE municipis DEFAULT CHARACTER SET utf8;
#USE municipis;

#CREATE USER alumne IDENTIFIED BY 'keiL2lai';
#GRANT ALL ON provincies.* 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);
...

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:

scripts inicials (v1)

Fitxers de les versions v1 i v2: Fitxer:Municipis.zip

Codi per a la versió 1:

fitxer index.php:

<?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);
	var xmlhttp;
	var txt,x,xx,i;
	if (window.XMLHttpRequest)
	{// code for IE7+, Firefox, Chrome, Opera, Safari
	  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"); ?>

Fitxer cercar_provincies.php:

<?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");

?>

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:

És interessant poder descomposar el problema en parts independents.

fitxer open_db.php:

<?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,"provincies") or die('Could not select jbalmes database.');
mysqli_set_charset($conn, 'utf8');
?>

la línia

mysqli_set_charset($conn, 'utf8');

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:

<?php
mysqli_close($conn);
?>

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. Quedaria de la següent manera:

index.php:

<?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);
	var xmlhttp;
	var txt,x,xx,i;
	if (window.XMLHttpRequest)
	{// code for IE7+, Firefox, Chrome, Opera, Safari
	  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"); ?>

cercar_provincies.php:

<?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;
?>

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 en les properes pràctiques.

scripts inicials (v3)

scripts inicials (v4)

scripts inicials (v5)

(TBD, JSON)

Feina per l'alumne

  1. 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.
  2. 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.
  3. millor encara. Quan cliquis sobre una comunitat s'omple la llista de províncies; alhora, per la província seleccionada s'haurà d'omplir la llista de municipis.
  4. Fica una capa d'informació on pots mostrar la superfície i el número d'habitants del municipi (les dades són fictícies).
  5. 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).
  6. Recorda dissenyar aquesta mini-aplicació amb uns estàndards correctes i moderns, incorporant un fitxer CSS.
  7. 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

Aclariment: problemes amb els accents de les dades que vénen de la BD

Si no visualitzes bé els accents no t'has de quedar tan ample i has d'aconseguir que els accents en la pàgina web es vegin correctament. No ha de ser difícil, tot i que és una mica embolic perquè poden haver-hi vàries circumstàncies involucrades. A saber:

En aquest exercici farem servir UTF-8 com a codificació general.

Anem a pams. Creem la base de dades amb codificació UTF-8:

CREATE DATABASE provincies DEFAULT CHARACTER SET utf8;

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

Inserim les dades (amb accents, c trencada,...):

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);
insert into municipis values (1,6,'Maçanet de la Selva',22,37639);
insert into municipis values (1,7,'Argençola',22,37639);
insert into municipis values (1,8,'L''Arboç',22,37639);

En aquests moments podem fer una select de la base de dades i hem de veure les dades correctament. Des de la consola:

mysql> select * from municipis;
+---------+--------+----------------------+------------+-----------+
| id_prov | id_mun | municipi             | superficie | habitants |
+---------+--------+----------------------+------------+-----------+
|       1 |      1 | Alegría-Dulantzi     |         68 |     32767 |
|       1 |      2 | Amurrio              |         23 |     32767 |
|       1 |      3 | Añana                |         43 |     19466 |
|       1 |      4 | Aramaio              |         53 |      5720 |
|       1 |      5 | Armiñón              |         22 |     32767 |
|       1 |      6 | Maçanet de la Selva  |         22 |     32767 |
|       1 |      7 | Argençola            |         22 |     32767 |
|       1 |      8 | L'Arboç              |         22 |     32767 |

Anem a fer un script bàsic PHP per recuperar les dades de la base de dades. Primer de tot utilitzarem PHP en mode consola. És important utilitzar la funció mysql_set_charset per tal que les dades que vénen de la base de dades es visualitzin correctament en la consola:

mysql_set_charset — Sets the client character set

Només cal comentar aquesta línia per veure la importància.

llistar_municipis_v1.php:

<?php
$conn = mysqli_connect("localhost", "alumne", "keiL2lai");
if (!$conn) {
    $log->error('Could not connect: ' . mysqli_error());
    die('Could not connect: ' . mysqli_error());
}

mysqli_select_db($conn, "municipis") or die('Could not select municipis database.');
mysqli_set_charset($conn,'utf8');

	
$sql = "SELECT * FROM municipis order by id_mun";

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

if (!$result) {
    $message  = 'Invalid query: ' . mysqli_error() . "<br />";
    die($message);
}

while ($row = mysqli_fetch_assoc($result)) {
	echo $row['municipi']."<br />";
}

mysqli_close($conn);

?>
$ php llistar_municipis_v1.php
Alegría-Dulantzi
Amurrio
Añana
Aramaio
Armiñón
Maçanet de la Selva
Argençola
L'Arboç

Ara bé, quan aquesta informació la volem volcar sobre un navegador web (HTML), hem de fer que la codificació del client web també sigui HTML:

<head>
<meta charset="UTF-8"> 
</head>


llistar_municipis_v2.php:

<html>
<head>
<meta charset="UTF-8"> 
</head>
<body>
<?php
$conn = mysql_connect("localhost", "alumne", "keiL2lai");
if (!$conn) {
    $log->error('Could not connect: ' . mysql_error());
    die('Could not connect: ' . mysql_error());
}
mysql_select_db("provincies", $conn) or die('Could not select jbalmes database.');
mysql_set_charset('utf8',$conn);
	
$sql = "SELECT * FROM municipis order by id_mun";

$result = mysql_query($sql);

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

while ($row = mysql_fetch_assoc($result)) {
	echo $row['municipi']."<br />";
}

mysql_close($conn);

?>
</body>
</html>

Sortida pel navegador web:

 Alegría-Dulantzi
Amurrio
Añana
Aramaio
Armiñón
Maçanet de la Selva
Argençola
L'Arboç

Propagació de comunitats, províncies i municipis

Quan seleccionem una comunitat, apareixen les províncies. Per ex, Catalunya: Barcelona, Tarragona, Lleida i Girona.

Si volem que automàticament apareixin els municipis de la primera província, per exemple els municipis de Barcelona, hem de llençar la funció canvi_provincia(8), on el index és la província dels muncipis que volem mostrar.

Per tant, quan faci canvi_comunitat he d'enviar els option de totes les províncies, i també he d'enviar el id de la primera província.

cercar_provincies.php quedarà de la següent manera:

$txt = "<select name=\"provincia\" onchange=\"canvi_provincia(this.value)\">";
while ($row = mysql_fetch_assoc($result)) {
	if ($i == 0) echo $row['id_prov']."*";
	$txt .= "<option value=".$row['id_prov'].">".$row['provincia']."</option>";
	$i++;
}
$txt .= "</select>";
echo $txt;

Fixem-nos que faig servir el caràcter * per separar el id de la primera província, de la part del <select>.

En la part de Javascript he de fer un split:

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

Carreo la llista de províncies (res[0]), i llenço el canvi de província:

canvi_provincia(res[0]);

que em carregarà el llistat de municipis d'aquesta província.

Amb aquesta tècnica podem aconseguir que es propaguin els valors. Per exemple: seleccionem Extremadura; automàticament es carreguen les províncies de Cáceres i Badajoz; com que es mostra Cáceres, automàticament es llencem el canvi de província per tal de què es mostrin tots els municipis de Cáceres.

Entrega

Els alumnes entregaran al Schoology tots els fitxers generats (fitxers html). S'empaquetaran tots aquests fitxers i es pujaran al Schoology dins del termini d'entrega de la pràctica.

Recorda la normativa per entregar les pràctiques al Schoology: ASIX-M10-UF2#Normativa_d.27entrega_de_les_pr.C3.A0ctiques_al_Schoology


creat per Joan Quintana Compte, febrer 2019

Eines de l'usuari
Espais de noms
Variants
Accions
Navegació
Institut Jaume Balmes
Màquines recreatives
CNC
Informàtica musical
joanillo.org Planet
Eines