Fondamenti delle basi di dati
Corso MySQL a cura del prof. Pietro De Paolis
Esercizio svolto n. 2 (parte terza)
Nella precedente lezione abbiamo visto una richiesta al nostro data base:
$richiesta="SELECT * FROM studenti, corsirecupero, corsifrequentati WHERE studenti.cognome='$cognome' AND studenti.nome='$nome' AND corsifrequentati.codicestudente=studenti.codice AND corsifrequentati.codicecorso=corsirecupero.codicecorso";Se
che trasfromava in linguaggio MySQL la soluzione della domanda:
1 - Dato uno studente fornire un elenco dei corsi di recupero frequentati e il loro esito.
Ora vediamo meglio come formulare queste richieste alla base di dati, cioè vediamo il numero di condizioni AND da mettere, quali tabelle utilizzare.
Quali tabelle usare nella SELECT?
Le tabelle da usare nella istruzione FROM dipendono dalla domanda del problema; nel nostro caso la domanda diceva: Dato uno studente..., cioè conoscendo i dati delle studente, cioè nome o cognome o codice studente; è evidente che i dati dello studente erano stati memorizzati nella tabella studenti; quindi la tabella studenti è l'unica che contiene questi dati e va quindi utilizzata.
Inoltre la domanda diceva: ... fornire un elenco dei corsi di recupero frequentati e il loro esito; la tabella che contiene i corsi di recupero e il loro esito è la tabella corsifrequentati, essa quindi va utilizzata. Tale tabella, tuttavia, non contiene la descrizione dei corsi di recupero in quanto tale descrizione è stata messa nella tabella corsirecupero; quindi se vogliamo vedere in uscita non solo il codice del corso ma anche una descrizione del corso ci serve comunque anche la tabella corsirecupero.
Analizziamo ora la richiesta MySQL:
SELECT * FROM studenti, corsirecupero, corsifrequentati WHERE ...
essa ci dice di scegliere tutti i dati contenuti nelle tre tabelle: studenti, corsirecupero, corsifrequentati; se non esplicitiamo bene le condizioni, anzi se non mettessimo nessuna condizione in WHERE e quindi lo togliessimo proprio, essa rimarrebbe:
SELECT * FROM studenti, corsirecupero, corsifrequentati
il gestore del nostro database ci darebbe come risultato un insieme di righe costituito dalle combinazioni diverse a tre a tre di tutte le righe delle tre tabelle; in pratica supponiamo che:
tabella1= studenti abbia 2 righe;
tabella2= corsirecupero abbia 3 righe;
tabella3= corsifrequentati abbia 4 righe;
la tabella in uscita sarebbe una unione delle tre tabelle costituita in questo modo:
1 - riga 1 di tabella1 + riga 1 di tabella2 + riga 1 di tabella3
2 - riga 1 di tabella1 + riga 1 di tabella2 + riga 2 di tabella3
3 - riga 1 di tabella1 + riga 1 di tabella2 + riga 3 di tabella3
4 - riga 1 di tabella1 + riga 1 di tabella2 + riga 4 di tabella3
cioè lasciando costante la unione delle prime due righe delle prime due tabelle:
riga 1 di tabella1 + riga 1 di tabella2
la ha combinata con tutte le righe della terza tabella; non solo; ma avrebbe fatto la stessa cosa con le altre combinazioni diverse senza considerare il posto delle prime due tabelle e cioè avrebbe aggiunto:
5 - riga 1 di tabella1 + riga 2 di tabella2 + riga 1 di tabella3
6 - riga 1 di tabella1 + riga 2 di tabella2 + riga 2 di tabella3
7 - riga 1 di tabella1 + riga 2 di tabella2 + riga 3 di tabella3
8 - riga 1 di tabella1 + riga 2 di tabella2 + riga 4 di tabella3
e inoltre:
9 - riga 1 di tabella1 + riga 3 di tabella2 + riga 1 di tabella3
10 - riga 1 di tabella1 + riga 3 di tabella2 + riga 2 di tabella3
11 - riga 1 di tabella1 + riga 3 di tabella2 + riga 3 di tabella3
12 - riga 1 di tabella1 + riga 3 di tabella2 + riga 4 di tabella3
infine a queste 12 righe avrebbe aggiunto altre 12 righe e cioè:
13 - riga 2 di tabella1 + riga 1 di tabella2 + riga 1 di tabella3
14 - riga 2 di tabella1 + riga 1 di tabella2 + riga 2 di tabella3
15 - riga 2 di tabella1 + riga 1 di tabella2 + riga 3 di tabella3
16 - riga 2 di tabella1 + riga 1 di tabella2 + riga 4 di tabella3
17 - riga 2 di tabella1 + riga 2 di tabella2 + riga 1 di tabella3
18 - riga 2 di tabella1 + riga 2 di tabella2 + riga 2 di tabella3
19 - riga 2 di tabella1 + riga 2 di tabella2 + riga 3 di tabella3
20 - riga 2 di tabella1 + riga 2 di tabella2 + riga 4 di tabella3
21 - riga 2 di tabella1 + riga 3 di tabella2 + riga 1 di tabella3
22 - riga 2 di tabella1 + riga 3 di tabella2 + riga 2 di tabella3
23 - riga 2 di tabella1 + riga 3 di tabella2 + riga 3 di tabella3
24 - riga 2 di tabella1 + riga 3 di tabella2 + riga 4 di tabella3
Per un totale di 24 righe = 2 x 3 x 4
Ovviamente questo risultato non sarebbe coerente con il nostro problema; ci servono quindi delle condizioni AND da porre dopo WHERE; una regola che possiamo usare è questa:
1 - Il numero minimo di condizioni AND diverse deve essere uguale al numero delle tabelle messe dopo FROM;
2 - Ognuna delle condizioni AND precedenti deve riguardare una tabella diversa mettendo tutte le tabelle messe nella FROM, senza esclusione di nessuna.
3 - Possono essere richieste ulteriori AND aggiuntive oltre le minime di cui sopra in base alle richieste del problema da rioslvere.
Nel nostro caso essendo 3 le tabelle per la prima regola ci servono almeno tre AND e questo nella:
$richiesta="SELECT * FROM studenti, corsirecupero, corsifrequentati WHERE studenti.cognome='$cognome' AND studenti.nome='$nome' AND corsifrequentati.codicestudente=studenti.codice AND corsifrequentati.codicecorso=corsirecupero.codicecorso";
è soddisfatto, anzi ne abbiamo 4 AND; le prime due AND riguarda la prima tabella studenti:
WHERE studenti.cognome='$cognome' AND studenti.nome='$nome'
questo ci consente di escludere tutte le righe della prima tabella che non riguardano lo studente cercato; la terza AND:
AND corsifrequentati.codicestudente=studenti.codice
riguarda la tabella corsifrequentati e tende ad esludere dalle 24 righe tutte le righe diverse da quelle che riguardano lo studente interessato contenuto in studenti.codice
Infine l'ultima AND riguarda la tabella corsirecupero
corsifrequentati.codicecorso = corsirecupero.codicecorso";
e tende ad escludere tutte le righe della tabella corsirecupero che non riguardano lo studente interessato.
Le prime due regole sono soddisfatte e non occorrono AND aggiuntive per escludere righe non richieste.
Istruzione JOIN
La nostra SELECT precedente:
$richiesta="SELECT * FROM studenti, corsirecupero, corsifrequentati WHERE studenti.cognome='$cognome' AND studenti.nome='$nome' AND corsifrequentati.codicestudente=studenti.codice AND corsifrequentati.codicecorso=corsirecupero.codicecorso";
poteva anche essere scritta in questo altro modo:
$richiesta="SELECT * FROM studenti JOIN corsirecupero JOIN corsifrequentati WHERE studenti.cognome='$cognome' AND studenti.nome='$nome' AND corsifrequentati.codicestudente=studenti.codice AND corsifrequentati.codicecorso=corsirecupero.codicecorso";
cioè mettendo al posto della virgola una istruzione JOIN, che vuol dire giunzione, unione di tabelle.
La istruzione JOIN accetta le condizioni sia mediante WHERE e sia mediante ON;
cioè avremmo potuto mettere:
$richiesta="SELECT * FROM studenti JOIN corsirecupero JOIN corsifrequentati ON studenti.cognome='$cognome' AND studenti.nome='$nome' AND corsifrequentati.codicestudente=studenti.codice AND corsifrequentati.codicecorso= corsirecupero.codicecorso";
In realtà esiste una piccola differenza tra WHERE e ON; WHERE dovrebbe riguardare condizioni esterne alle tabelle messe nelle JOIN; mentre ON dovrebbe riguardare le condizioni riguardanti le colonne messe nelle tabelle delle JOIN, cioè ON dovrebbe essere interna alla JOIN e WHERE esterna alla JOIN.
Seconda ricerca sulla base di dati
In una lezione precedente abbiamo visto che il nostro esercizio prevedeva due richieste sul database, la seconda era:
2 - Dato un corso di recupero fornire un elenco di tutti gli studenti che lo hanno frequentato.
Prepariamo, quindi, una interfaccia per inserire il corso di recupero richiesto e poi un secondo programma in php che ci visualizzi i dati della ricerca.
L'interfaccia di ricerca può essere la seguente:
Modulo
Poichè i dati devono essere passati da un programma ad un altro dobbiamo per forza utilizzare un modulo che ci consenta di inviare i dati.
Il listato completo del programma che acquisisce il titolo del corso è il seguente:
<script
language="JavaScript">
<!-- // controllo che ci siano le due scelte function controllo1(){ // controllo che ci sia il cognome e il nome sceltatitolo=document.modulo1.titolo.value; if (sceltatitolo) { alert("Inserire il titolo del corso!"); return false; }; return true; } // --> </script> <form action="cercodaticorsi.php" name="modulo1" method="POST" onSubmit="return controllo1();">
<div align="left"> <table border="1" cellpadding="0" width="100%"> <tr> <td width="35%"><font size="4" face="Verdana" color="#FF0000">Titolo delcorso</font></td> <td width="65%" align="center"> <p align="center"> <font face="Verdana" size="4"> <font color="#0000FF"><span style="mso-bidi-font-size: 12.0pt"> <input type="text" name="titolo" size="22" style="color: #FF0000; font-family: Verdana; font-size: 14 pt; background-image: url('../../../immagini/sfondo3.gif')"> </span> </font> </font> </p> </td> </tr><tr> <td width="134%" colspan="2"> <p align="center"><input type="submit" value="CERCA STUDENTI" name="cerco" style="font-family: Verdana; font-size: 14 pt"></td> </tr> </table> </div> </form> |
listato di cercocorsi.php |
Funzione in javascript controllo1()
E' buona norma effettuare un primo controllo dei dati prima di effettuare la ricerca; per questo useremo una apposita funzione in javascript; i controlli potrebbero essere molteplici, noi ci limiteremo a controllare:
1 - Che l'utente abbia inserito il titolo del corso.
Quando l'utente preme il tasto di convalida:
tutti i dati contenuti nel modulo vengono passati al programma in php che serve ad effettuare la ricerca dei dati dello studente voluto e cioè cercodaticorsi.php.
I dati passati dal modulo verrano presi dal nostro programma in php in modo automatico e memorizzati nelle variabili php : $titolo.
A questo punto possiamo fare una richiesta di ricerca dei dati con una istruzione MySQL SELECT e precisamente:
$richiesta="SELECT corsirecupero.titolo, corsirecupero.descrizione, studenti.cognome, studenti.nome, corsifrequentati.esito FROM studenti, corsirecupero, corsifrequentati WHERE corsirecupero.titolo='$titolo' AND corsifrequentati.codicestudente=studenti.codice AND corsifrequentati.codicecorso=corsirecupero.codicecorso";$risultato =mysql_query($richiesta);
cioè cerchiamo il titolo, la descrizione e l'esito del corso e relativo nome e cognome dello studente; con FROM studenti, corsirecupero,corsifrequentati nelle tre tabelle studenti, corsirecupero, corsifrequentati; ma cercheremo solo quelli il cui titolo del corso corrisponde quello voluto.
Inoltre vi sono altre due condizioni da essere verificate e cioè:
AND corsifrequentati.codicestudente = studenti.codice
cioè dalla tabella corsifrequentati non devo prendere tutti i dati ma solo quelli frequentati dallo studente voluto, e questo lo faccio controllando che il codice dello studente inserito nella tabella studente (studenti.codice) sia uguale al codice dello studente inserito nella tabella corsifrequentati (corsifrequentati.codicestudente).
Non basta ma resta una ultima condizione e cioè:
AND corsifrequentati.codicecorso = corsirecupero.codicecorso
cioè dalla tabella corsifrequentati non devo prendere tutti i dati ma solo quelli frequentati dallo studente voluto, e questo lo faccio controllando che il codice del corso inserito nella tabella corsifrequentati (corsifrequentati.codicecorso) sia uguale al codice del corso di recupero inserito nella tabella corsirecupero (corsirecupero.codicecorso).
Il nostro programma completo per la ricerca dei corsi di recupero frequentati dallo studente voluto sarà:
<?php
if($titolo=="") return;
print ("Programma per la ricerca degli studenti che hanno frequentato un corso");
$nomehost = "miohost";
$utente = "mionome";
$parola = "miapasswors";
$nome = "nomedeldatabase";
$connessione=mysql_connect($nomehost,$utente,$parola);
$selezione = mysql_select_db($nome, $connessione);
$richiesta="SELECT corsirecupero.titolo, corsirecupero.descrizione, studenti.cognome, studenti.nome, corsifrequentati.esito FROM studenti, corsirecupero, corsifrequentati WHERE corsirecupero.titolo='$titolo' AND corsifrequentati.codicestudente=studenti.codice AND corsifrequentati.codicecorso=corsirecupero.codicecorso";$risultato =mysql_query($richiesta);
$risultato =mysql_query($richiesta);
$lunghezza = mysql_num_rows($risultato);
print("Le righe trovate sono: $lunghezza .<br>");
for ($t=0; $t<$lunghezza; $t++) {
$riga=mysql_fetch_array($risultato);
print("$riga[0] $riga[1] $riga[2] $riga[3] $riga[4] $riga[5] $riga[6] $riga[7] $riga[8] $riga[9]$riga[10]<br>");
};
mysql_close($connessione);
?>
Salviamo con il nome di cercodaticorsi.php ed eseguiamo cercocorsi.php che lo richiamerà al momento della ricerca dei dati sul nostro database.
A questo punto è bene riunire tutti i moduli del nostro programma in un'unica pagina, vedi:
Gestione dei corsi di recupero
Esercizi da svolgere
1 - Una biblioteca scolastica effettua il prestito di testi scolastici di cui si conosce: autore, titolo, casa editrice, prezzo, breve riassunto in formato testo agli alunni del proprio istituto, di cui si conosce: cognome, nome, data e comune di nascita, comune di residenza, indirizzo, numero di telefono, classe frequentata.
Creare un programma di gestione della base di dati che consenta:
a) - la ricerca per autore;
b) - la ricerca per titolo del libro;
c) - la ricerca per casa editrice;
d) - la ricerca di tutti i libri prestati e rientrati;
e) - la ricerca di tutti i libri prestati e non rientrati con i dati dei rispettivi alunni;
f) - la ricerca di tutti i libri prestati al singolo alunno.
2 - Un esercizio commerciale effettua degli acquisti e delle vendite con fornitori e clienti in possesso di partita IVA.
Di ogni fattura di acquisto si conosce: codice fornitore, codice articolo, descrizione articolo, quantità acquistata, importo parziale di acquisto.
Di ogni fattura di vendita si conosce: codice cliente, codice articolo, descrizione articolo, quantità venduta, importo parziale di vendita.
Creare un programma di gestione della base di dati del negozio che consenta:
a) - la ricerca di tutti gli articoli acquistati conoscendo il codice del fornitore;
b) - la ricerca di tutti gli articoli venduti ad un singolo cliente;
c) - la ricerca di tutti gli articoli acquistati e non venduti al termine di un anno fiscale (magazzino al 31 dicembre);
d) - le previsioni di acquisto, per singolo articolo, per il prossimo anno commerciale, ipotizzando una presunzione di circa l'80% delle vendite rispetto l'anno corrente.
giugno 2006
Corso di programmazione in MYSQL
Dizionario mysql e relativo php
Corso di programmazione in PHP
prof. Pietro De Paolis
Indice di tutte le pagine del sito