Accesso a database tramite pagine web.

Si costruisce dapprima il data base dei libri della biblioteca, poi si simula la consultazione a distanza dell'archivio e si effettueranno altre operazioni di accesso al data base.

Richiamare il server mysql tramite il menu di wamp, accessibile cliccando l'icona di wamp nell'area di notifica.

Creazione del data base:

mysql> create database Biblioteca;

Accesso al database:
mysql> use Biblioteca;

Creazione di tabelle nel database:
mysql> create table libri (

    -> IDlibro int not null auto_increment primary key,

    -> autore varchar(20),

    -> titolo varchar(20),

    -> note varchar(50)

    -> );

Il nome assegnato alla tabella è libri e, tra parentesi, viene specificato l'elenco dei campi: IDlibro, autore, titolo e note.

Dopo ciascun nome di campo viene specificato il tipo del campo, ad esempio il campo IDlibro è dichiarato INT cioè assume solo valori interi, NOT NULL perchè essendo un campo chiave primaria non può essere omesso, auto_increment perchè i valori possono essere assegnati automaticamente ogni volta che si inserisce un record.

Struttura di una tabella

Il comando per esaminare la struttura di una tabella è:

mysql> describe libri;

che mostra la tabella nella seguente forma:

+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| IDlibro | int(11)     |      | PRI | NULL    | auto_increment |
| autore  | varchar(20) | YES  |     | NULL    |                |
| titolo  | varchar(20) | YES  |     | NULL    |                |
| note    | varchar(50) | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)

Query di accodamento

Il comando per l'inserimento di record nella tabella:

mysql> insert into libri (autore, titolo, note)

    -> values ('Rossi', 'lezioni di Fisica', 'Ottica');

Query OK, 1 row affected (0.03 sec)

Query di selezione

Per consultare i record contenuti nella tabella si esegue una query di selezione:

mysql> select * from libri;
+---------+---------+--------------------+-------------------+
| IDlibro | autore  | titolo             | note              |
+---------+---------+--------------------+-------------------+
|       1 | Rossi   | lezioni di Fisica  | Ottica            |
|       2 | Bianchi | analisi matematica | integrazione      |
|       3 | verdi   | storia             | conflitti del 900 |
+---------+---------+--------------------+-------------------+
3 rows in set (0.02 sec)

Il risultato di una query è una tabella (temporanea).
Si noti che il campo IDlibro è stato generato automaticamente.

Accodamento tramite file di testo.

Un altro modo per inserire i record in una tabella consiste nel creare un file di testo.

Nella cartella mysql\bin creare un file di testo denominato (ad esempio) testi.txt, scrivendo un record per linea e separando i valori dei campi con una tabulazione.

In corrispondenza del campo chiave generato automaticamente scrivere \N (con N maiuscola. \N significa NULL).

Ad esempio, se il file contiene le seguenti righe:

\N	Eco	il nome della rosa	romanzo
\N	Dante	la Divina Commedia	commentata da Benigni

Per trasferire i record dal file di testo alla tabella libri usare il comando:

mysql> load data local infile 'testi.txt' into table libri

    -> lines terminated by '\r\n';

La specifica lines terminated by '\r\n'; indica che una linea del file generata con il blocco note è terminata con un 'vai a capo' codificato con la coppia di caratteri \r (linefeed: avanza di una riga) e \n (carriage return: vai a inizio riga).

Query di aggiornamento

Modifica di un campo di uno o più record.

mysql> update libri set autore = 'Bianchi' where IDlibro=2;

Il comando update specifica la tabella che contiene i record da modificare (libri) e, con set, indica il campo che deve essere aggiornato e il valore da assegnare al campo; nella sezione where si specifica la condizione che i record devono soddisfare affinchè vengano individuati tra quelli da modificare.

Per verifica subito dopo si può eseguire una query di selezione.

Query di selezione.

Selezione di Campi.

mysql> select titolo, note from libri;

la query precedente mostra solo due campi della tabella.

Normalmente il risultato di una query è costituito da tutti i record che soddisfano la condizione di ricerca, quindi alcune righe potrebbero essere duplicate, ad esempio se, per contare quanti argomenti sono trattati nei libri in possesso, si esegue la query select note from libri; si potrebbero ottenere righe aventi le stesse note. Per non avere la ripetizione dei record aventi lo stesso valore nel campo note, eseguire la query:

mysql> select distinct note from libri;

Ordinamento dei record prodotti da una query.

mysql> SELECT titolo, note FROM libri ORDER BY note;

La clausola ORDER BY specifica il nome del campo da usare per ordinare i record, per default l'ordinamento è in senso crescente.

Se si desidera ottenere il risultato in ordine decrescente si deve aggiungere DESC (descending):

mysql> SELECT titolo, note FROM libri ORDER BY note DESC;

L'ordinamento può essere applicato a tutti i campi della query che si desidera e per ciascun campo si può scegliere la direzione dell'ordinamento. La seguente query produce una tabella di record ordinati per argomento in senso crescente e, in corrispondenza di note uguali, ordinati per titolo decrescente:

mysql> SELECT note, titolo FROM libri

    -> ORDER BY note, titolo DESC;

Notare che per il campo note non è stata specificata la direzione dell'ordinamento, quindi si assume quella di default: crescente, mentre DESC si applica solo al campo titolo.

Modifica della struttura di una tabella.

Aggiungere un campo alla tabella.

mysql> alter table libri add Prezzo int;

Sintassi:

(ALTER TABLE [Nome Tabella] ADD [Nome Colonna] [Proprietà Colonna];)

Tramite il comando update assegnare dei valori ai campi prezzo dei libri. Ad esempio:

mysql> update libri set prezzo=100 where IDlibro =2;

Modificare un campo

Per modificare il nome di un campo della tabella, ad esempio modificare il campo note in argomento:

mysql> alter table libri change note argomento varchar(50);

Sintassi:

(ALTER TABLE [Nome Tabella] CHANGE [Nome Campo] [Nuovo Nome] [Proprietà];)

Per modificare il tipo del campo si esegue lo stesso comando, ripetendo lo stesso nome per il campo e cambiando solo la parte che specifica il tipo del campo.

Prestito dei libri

Si costruisca una pagina di amministrazione del data base, nella quale è possibile scegliere di creare il data base, eliminarlo, aggiungere un nuovo libro, un nuovo utente, ecc.

Menu

Le operazioni che si prevede di compiere sul data base si possono dividere in un due categorie: quelle utili al progettista in fase di collaudo dell'applicazione e quelle utili in fase di gestione della biblioteca.

Quelle di amministrazione sono la creazione e l'eliminazione del data base, quest'ultima dovrebbe essere seguita da una richiesta di confermare l'intenzione di cancellare il data base o dovrebbe essere eseguita solo fornendo un codice in possesso del progettista. Poi ci sono le operazioni di creazione e di eliminazione delle tabelle.

L'utilizzo del data base, invece, richiede le operazioni di inserimento libri, inserimento utenti, consultazione del catalogo, prestito e restituzione dei libri.

Tutte queste operazioni vengono raccolte in una pagina che ha la funzione di menu.

Amministra.htm

<html>

<head>

<title>Biblioteca</title>

</head>

<body>

<h1>Biblioteca</h1>

<ul>

    <li><p><a href="creaDB.php">Crea</a> il data base.</p></li>

    <li><p><a href="eliminaDB.php">Cancella</a> il data base.</p></li>

    <li><p><a href="creaTabLibri.php">Crea</a> la tabella Libri.</p></li>

    <li><p><a href="cancTabLibri.php">Elimina</a> la tabella Libri.</p></li>

    <li><p><a href="creaTabUtenti.php">Crea</a> la tabella Utenti.</p></li>

    <li><p><a href="cancTabUtenti.php">Elimina</a> la tabella Utenti.</p></li>

    <li><p>Inserimento <a href="insLibro.htm">nuovo libro</a>.</p></li>

    <li><p>Consulta il <a href="catalogo.php">catalogo</a> della biblioteca</p></li>

    <li><p>aggiungi <a href="insutente.htm">utente</a></p></li>

    <li><p><a href="cerca.htm">Cerca</a> Libro per titolo</p></li>

    <li><p><a href="presta.htm">Prestito</a> Libri</p></li>

    <li><p><a href="prestati.php">Elenco utenti</a> con libri in prestito</p></li>

</ul>

</body>

</html>

Creazione del data base.

CreaDB.php

La creazione del data base avviene con il comando Create database nome

Lo script php è ospitato in una pagina web, di cui la seguente è l'intestazione:

<html>

<head>

<title></title>

</head>

<body>

<html><head><title>Biblioteca</title></head>

<body>

Lo script php inizia con la richiesta di apertura connessione, questa crea un socket. La pagina web è il client e l'applicazione mysql è il server in ascolto delle richieste provenienti dallo script.

<?php

  $host = "localhost";

  $utente = "root";

  $pw = "";

  $con = mysql_connect($host, $utente, $pw) or die("impossibile connettersi a mysql");

La funzione mysql_connect restituisce un valore booleano per indicare l'esito del tentativo di apertura della connessione. Se l'esito è negativo, la funzione die() stampa un messaggio e termina l'esecuzione dello script.

Se l'apertura della connessione avviene correttamente si possono passare comandi al server mysql sulla connessione a cui si è assegnato l'identificatore $con.

  $nomeDB = "Biblio";

  $sql = "create database ".$nomeDB;

  if (mysql_query($sql, $con)) echo "Creato il database ".$nomeDB;

  else echo "il data base ".$nomeDB." esiste";

  mysql_close($con);

?>

<p>torna alla pagina di <a href="amministra.htm">amministrazione</a>.</p>

</body>

</html>

Al termine la connessione viene rilasciata con il comando mysql_close(identificatore della connessione)

EliminaDB.php

Lo script per l'eliminazione del data base differisce dal precedente per il solo comando di cancellazione: drop database nome

<html>

<head>

<title>Biblioteca</title>

</head>

<body>

<?php

  $host = "localhost";

  $utente = "root";

  $pw = "";

  $con = mysql_connect($host, $utente, $pw) or die("impossibile connettersi a mysql");

  $nomeDB = "Biblio";

  $sql = "drop database ".$nomeDB;

  if (mysql_query($sql, $con)) echo "Eliminato il database ".$nomeDB;

  else echo "il data base ".$nomeDB." non esiste";

  mysql_close($con);

?>

<p>torna alla pagina di <a href="amministra.htm">amministrazione</a>.</p>

</body>

</html>

Le altre pagine richiamate dalla pagina di amministrazione, e utilizzate in fase di progetto del data base sono:

Inserimento nuovo libro.

L'applicazione è formata da:

  1. una pagina che, sul client, acquisisce in un form i dati e li passa ad una pagina residente sul server

  2. La pagina richiamata sul server legge i campi trasmessi, si connette al data base ed effettua l'interrogazione,

  3. Il data base restituisce il risultato dell'interrogazione (una tabella temporanea)

  4. Lo script, infine, costruisce una pagina contenente i risultati dell'interrogazione e la invia al client.

  5. il client mostra la pagina

L'organizzazione descritta è tipica di ogni applicazione che accede a data base. In essa si individuano le tre componenti:

Pagina web con form
residente sul client		  →  pagina dinamica php
residente sul server		  →  data base
Pagina web con dati
ottenuti dal data base.
residente sul client		  ←  pagina dinamica php
acquisisce i dati dal data base
prepara la pagina da mostrare al client
residente sul server		  ← 

La pagina amministra.htm richiama insLibro.htm

insLibro.htm

Il client accede ad un form per l'inserimento dei dati:

<html>

<head>

<title>Biblioteca</title>

</head>

<body>

<h1>Nuovo Libro:</h1>

<form method="get" action="insLibro.php">

Autore:<input type="text" name="aut"><br />

Titolo:<input type="text" name= "tit"><br />

<input type="submit" />

</form>

</body>

</html>

Questa pagina propone un modulo per inserire i campi autore e titolo ed invia i dati alla pagina insLibro.php

insLibro.php

Lo script acquisisce i parametri ricevuti con il metodo GET, apre la connessione al server mysql, seleziona il data base, compone la query di inserimento e la invia al server mysql sulla connessione $con.

<html>

<head>

<title>Biblioteca</title>

</head>

<body>

<?php

$aut = $_GET['aut'];

$tit = $_GET['tit'];

$host = "localhost";

$utente = "root";

$pw = "";

$con = mysql_connect($host, $utente, $pw) or die("impossibile connettersi a mysql");

$nomeDB = "Biblio";

mysql_select_db($nomeDB, $con);

$sql="INSERT INTO libri(autore, titolo) values ('$aut', '$tit')";

$esito = mysql_query($sql,$con);

if ($esito) echo "aggiunto libro ".$tit." alla tabella libri";

else echo "non sono riuscito ad aggiungere il libro";

?>

<p>torna alla pagina <a href='inslibro.htm'>inserimento</a> libro</p>

<p>torna alla pagina di <a href="amministra.htm">amministrazione</a>.</p>

</body>

</html>

Interrogazione del data base

La seguente pagina mostra il catalogo dei libri presenti nella biblioteca.

Questa pagina viene richiamata tramite il link nella pagina di amministrazione Stampa il <a href="catalogo.php">catalogo</a>

catalogo.php

Lo script, dopo l'apertura della connessione e la selezione del data base, compone l'interrogazione SELECT * FROM Libri.

Il risultato ottenuto dall'esecuzione della query

$recordSet = mysql_query("SELECT * FROM Libri");

è una tabella temporanea, detta RecordSet. Su ogni riga di questa tabella c'è un record.

Quando la tabella viene creata, possiede anche un puntatore al record corrente, inizializzato per puntare alla prima riga. L'accesso alla riga corrente della tabella avviene con mysql_fetch_array, che automaticamente posiziona il puntatore sul successivo record della tabella, per prepararsi alla successiva operazione di lettura. Il tentativo di leggere oltre la fine della tabella restituisce il valore falso.

<html>

<head>

<title>Biblioteca</title>

</head>

<body>

<h1>Catalogo:</h1>

<?php

$host = "localhost";

$utente = "root";

$pw = "";

$con = mysql_connect($host, $utente, $pw) or die("impossibile connettersi a mysql");

$nomeDB = "Biblio";

mysql_select_db($nomeDB, $con);

$query = "SELECT * FROM Libri";

$recordSet = mysql_query($query);

if (!$recordSet) {

  echo "Errore nell'interrogazione";

  exit();

}

?>

<table>

<tr><td>Nr.</td><td>Autore</td><td>Titolo</td></tr>

<?php

  while ($record = mysql_fetch_array($recordSet)) {

?>

<tr><td><?php echo $record["ID"]; ?></td>

<td><?php echo $record["Autore"]; ?></td>

<td><?php echo $record["Titolo"]; ?></td></tr>

<?php

}

mysql_close($con);

?>

</table>

<p>torna alla pagina di <a href="amministra.htm">amministrazione</a>.</p>

</body>

</html>

Aggiungere Utenti.

Per gestire il prestito dei libri è stata creata la tabella Utenti all'interno del database Biblioteca.

Anche in questo caso bisogna distinguere le componenti dell'applicazione: una pagina lato client che propone il form con i dati da utilizzare per accedere al data base, una pagina sul server che legge i dati e prepara l'interrogazione del data base poi, la stessa pagina, acquisisce i risultati dell'accesso al data base e prepara la pagina da inviare al client.

Dalla pagina di amministrazione viene richiamata la pagina con il form per l'acquisizione e la trasmissione dei dati:

Il file "insUtente.htm":

<html>

<head><title>Biblioteca</title>

</head>

<body>

<h1>Nuovo Utente</h1>

<form method="get" action="insUtenti.php">

Cognome:<input type="text" name="cog"><br>

classe:<input type="text" name= "cls"><br>

<input type="submit">

</form>

</body>

</html>

si presenta nel modo seguente:

Inserimento di un nuovo utente nell'archivio:

Cognome: 
Classe:
il file insutente.php

<html>

<head>

<title>Biblioteca</title>

</head>

<body>

<?php

$cog = $_GET['cog'];

$cls = $_GET['cls'];

$host = "localhost";

$utente = "root";

$pw = "";

$con = mysql_connect($host, $utente, $pw) or die("impossibile connettersi a mysql");

$nomeDB = "Biblio";

mysql_select_db($nomeDB, $con);

$sql="INSERT INTO utenti(Cognome, Classe) values ('$cog', '$cls')";

$esito = mysql_query($sql,$con);

if ($esito) echo "aggiunto utente ".$cog." alla tabella Utenti";

else echo "non sono riuscito ad aggiungere l'utente";

?>

<p>torna alla pagina <a href='insUtente.htm'>inserimento</a> utente"</p>

<p>torna alla pagina di <a href="amministra.htm">amministrazione</a>.</p>

</body>

</html>

Esercizi: scrivere le pagine web per cancellare e per modificare record. Si supponga che lo script riceva i parametri con il metodo GET.

Query di cancellazione record

$queryElimina = "DELETE FROM Utenti WHERE id='1'";

if (!mysql_query($queryElimina)) {

   echo ("eliminazione record non riuscita");

   exit();

}

Query di aggiornamento record.

$modifica = "UPDATE Utenti SET cognome = 'Rossi Ugo' WHERE id = '1'";

if (!mysql_query($modifica)) {

echo("errore nel tentativo di modifica record");

exit();

}

Ricerca

Il problema della ricerca di un libro, in base all'autore o in base al titolo, deve tener conto della possibilità che un autore possa essere scritto in forme diverse ("Rossi M. oppure M Rossi). Il problema si potrebbe risolvere, almeno in parte, separando, nella tabella, il nome e il cognome dell'autore. Anche le opzioni per cercare un libro in base al titolo devono tener conto della forma in cui questo viene registrato.

La ricerca di un record in una tabella avviene specificando la condizione che deve soddisfare un certo campo. Se il campo è una stringa di caratteri, si deve prevedere la possibilità che il testo cercato possa comparire all'inizio del campo o all'interno del campo.

Il modulo proposto all'utente per inserire i criteri di ricerca contiene una casella di testo in cui specificare il titolo del libro e una coppia di radio button per indicare se la stringa specificata corrisponde al titolo esatto del libro o è sufficiente che sia contenuta nel campo titolo.

Cerca.htm

<html>

<head>

<title>Biblioteca</title>

</head>

<body>

<h1>Ricerca per titolo</h1>

<form action="cerca.php" method="get">

<table>

<tr>

  <td>Titolo (o parte del titolo):</td><td><input type="text" name="tit" /></td></tr>

<tr>

  <td>cerca all'interno del titolo</td>

  <td><input type="radio" name="int" checked value="on" /></td></tr>

<tr>

  <td>titolo esatto:</td>

  <td><input type="radio" name="int" value="off" /></td></tr>

<tr><td></td>

<td><input type="submit" value="cerca"/></td></tr>

</table>

</form>

</body>

</html>

Cerca.php

<html>

<head>

<title>Biblioteca</title>

</head>

<body>

<h1>Cerca Libro per titolo</h1>

<?php

$tit = $_GET['tit'];

$int = $_GET['int'];

$host = "localhost";

$utente = "root";

$pw = "";

$con = mysql_connect($host, $utente, $pw) or die("impossibile connettersi a mysql");

$nomeDB = "Biblio";

mysql_select_db($nomeDB, $con);

$query = "select * from Libri where Titolo like ";

if ($int=="on") $query .= "'%$tit%'";

else $query .= "'$tit'";

echo "<br>".$query."<br>";

$rs = mysql_query($query);

if (!$rs) {

echo "errore nell'interrogazione";

exit();

}

?>

<table border="1">

<tr><td>id</td><td>Autore</td><td>Titolo</td></tr>

<?php while ($record = mysql_fetch_array($rs)) { ?>

<tr><td><?php echo $record["ID"]; ?></td>

<td><?php echo $record["Autore"]; ?></td>

<td><?php echo $record["Titolo"]; ?></td></tr>

<?php }

mysql_close($con);

mysql_free_result($rs);

?>

</table>

<p>Torna alla <a href="cerca.htm">ricerca</a></p>

<p>Torna alla pagina di <a href="amministra.htm">amministrazione</a></p>

</body>

</html>

Prestito

Il prestito del libro avviene specificando, in un form, il nome dell'utente che prende il libro e il codice del libro preso in prestito.

presta.htm

<html>

<head>

<title>Biblioteca</title>

</head>

<body>

<form method="get" action="presta.php">

Utente:<input type="text" name="ute"><br>

Nr inventario del Libro:<input type="text" name= "inv"><br>

<input type="submit">

</form>

</body>

</html>

Lo script acquisisce i parametri e aggiorna il record del libro inserendo il codice dell'utente nel campo IDutente. Si tratta di una relazione Uno a Molti: un utente può prelevare molti libri.

presta.php

<?php

$ute = $_GET['ute'];

$inv = $_GET['inv'];

$host = "localhost";

$utente = "root";

$pw = "";

$con = mysql_connect($host, $utente, $pw) or die("impossibile connettersi a mysql");

$nomeDB = "Biblio";

mysql_select_db($nomeDB, $con);

$sql="select * from utenti where cognome='".$ute."'";

$esito = mysql_query($sql,$con);

$rset = mysql_fetch_array($esito);

$id=$rset['IDutente'];

$sql = "update libri set IDutente=".$id." where ID=".$inv;

$esito = mysql_query($sql,$con);

?>

<p>torna alla pagina <a href='presta.htm'>prestito</a> libro</p>

<p>Torna alla pagina di <a href="amministra.htm">amministrazione</a></p>

Elenco dei libri in prestito

Prestati.php

<html>

<head>

<title>Biblioteca</title>

</head>

<body>

<?php

$host = "localhost";

$utente = "root";

$pw = "";

$con = mysql_connect($host, $utente, $pw) or die("impossibile connettersi a mysql");

$nomeDB = "Biblio";

mysql_select_db($nomeDB, $con);

$query = "select * from Libri where IDutente IS NOT NULL";

$rs = mysql_query($query);

if (!$rs) {

  echo "errore nell'interrogazione";

  exit();

}

?>

<table border="1">

<tr><td>id</td><td>Autore</td><td>Titolo</td><td>Utente</td><td>Classe</td><td>Restituzione</td></tr>

<?php while ($Libro = mysql_fetch_array($rs)) {

echo "<td>".$Libro["ID"]; ?></td>

<td><?php echo $Libro["Autore"]; ?></td>

<td><?php echo $Libro["Titolo"]; ?></td>

<?php

  $query = "select * from Utenti where IDutente like '".$Libro["IDutente"]."'";

  $rsUt = mysql_query($query);

  $Utente = mysql_fetch_array($rsUt);

  echo "<td>".$Utente["Cognome"]."</td>";

  echo "<td>".$Utente["classe"]."</td>";

  echo "<td><a href=rest.php?ID=".$Libro["ID"].">Reso</a></td></tr>";

}

mysql_close($con);

mysql_free_result($rs);

?>

<p>Torna alla pagina di <a href="amministra.htm">amministrazione</a></p>

</body>

</html>

Restituzione di un libro

rest.php

Accanto a ciascun utente che possiede un libro in prestito compare il link per registrare la restituzione del libro. La pagina richiamata da tale link è la seguente:

<html>

<head>

<title></title>

</head>

<body>

<?php

$ID = $_GET['ID'];

$host = "localhost";

$utente = "root";

$pw = "";

$con = mysql_connect($host, $utente, $pw) or die("impossibile connettersi a mysql");

$nomeDB = "Biblio";

mysql_select_db($nomeDB, $con);

$sql="update Libri set IDutente=NULL where ID=".$ID;

$esito = mysql_query($sql, $con);

mysql_close($con);

?>

<p>torna alla pagina di <a href='amministra.htm'>amministrazione</a></p>

</body>

</html>