Tutorial MySQL per prin­ci­pian­ti

Alla base del nostro universo digitale si trova una mole di dati in costante aumento. Elementi centrali di Internet e del mondo in­ter­con­nes­so sono i Database Ma­na­ge­ment System come MySQL che con­sen­to­no di elaborare elet­tro­ni­ca­men­te grandi quantità di dati, di me­mo­riz­zar­li lo­gi­ca­men­te e di con­ser­var­li per­ma­nen­te­men­te. Così le banche dati complesse vengono suddivise in quantità più piccole e messe in relazione tra di loro, se ne­ces­sa­rio. Nel nostro tutorial MySQL per prin­ci­pian­ti vi forniamo le basi per la gestione dei database e vi in­di­chia­mo grazie a degli esempi come ot­ti­miz­za­re la gestione del vostro progetto web con MySQL.

MySQL fa parte dei sistemi di gestione di basi di dati re­la­zio­na­li più popolari al mondo, insieme a Oracle e Microsoft SQL Server (trovate una pa­no­ra­mi­ca recente sui DBMS su db-engines.com). Il software, svi­lup­pa­to nel 1994 dall’azienda svedese MySQL AB, è oggi sotto l’ala pro­tet­tri­ce della Oracle Cor­po­ra­tion e viene gestito sulla base di un sistema di licenza duale: oltre a una variante En­ter­pri­se pro­prie­ta­ria, Oracle ne mette a di­spo­si­zio­ne anche un’altra con licenza GPL e quindi open source. Questa doppia licenza dà alle aziende la pos­si­bi­li­tà di svi­lup­pa­re delle proprie ap­pli­ca­zio­ni uti­liz­zan­do MySQL senza che queste debbano sot­to­sta­re alla licenza open source. Nella community open source l’acquisto di MySQL da parte di Oracle ha sollevato però pre­va­len­te­men­te delle critiche. MySQL è scritto in C e C++ e dispone di un parser SQL basato su Yacc con un tokenizer au­to­svi­lup­pa­to (scanner lessicale). Il Database Ma­na­ge­ment System si con­trad­di­stin­gue inoltre per un ampio supporto del sistema operativo.

L’in­te­gra­zio­ne del progetto MySQL nel portfolio dei prodotti Oracle si è imbattuta so­prat­tut­to nei sospetti e nelle critiche degli svi­lup­pa­to­ri. Ciò dipende in­nan­zi­tut­to dal fatto che le dif­fe­ren­ze tra una versione MySQL con licenza GPL e il prodotto En­ter­pri­se a pagamento sono in costante aumento. Le nuove funzioni del Database Ma­na­ge­ment System sono sempre più spesso ri­la­scia­te solo per la versione pro­prie­ta­ria. I database degli errori non resi pubblici e i test necessari che, invece, vengono tra­scu­ra­ti, fanno pensare alla community che si dia poca rilevanza al progetto open source, passato sotto l’ala pro­tet­tri­ce del software Goliath della Oracle; ne consegue così un distacco della community.

Già dal 2009 il team prin­ci­pa­le degli svi­lup­pa­to­ri intorno all’ideatore di MySQL Michael “Monty“ Widenius ha voltato le spalle al popolare sistema di database e ha avviato con MariaDB un fork open source del software. Alla fine del 2012 sono state in­tro­dot­te con Fedora, OpenSUSE, Slackware e Arch Linux le prime di­stri­bu­zio­ni Linux che pre­ve­de­va­no il passaggio da MySQL a MariaDB nell’in­stal­la­zio­ne standard. In­nu­me­re­vo­li progetti open source e illustri aziende di software e piat­ta­for­me web hanno seguito il loro esempio, come Mozilla, Ubuntu, Google, Red Hat En­ter­pri­se Linux, Web of Trust, Team Speak, la Wikimedia Foun­da­tion e il software XAMPP, di cui si tratterà in seguito.

Già da ora emerge che MariaDB, a dif­fe­ren­za della versione MySQL open source, viene co­stan­te­men­te svi­lup­pa­ta. Tutti gli indizi lasciano perciò supporre che il fork supererà ben presto il progetto da cui ha preso le mosse.

Tolkien è elencato nella tabella autori con la chiave primaria id_autori. Per ricercare tutte le opere dell’autore, viene uti­liz­za­to nella tabella opere come chiave esterna. Vengono così ri­chia­ma­te tutte le rows, che sono collegate con id_autori 1.

Abi­tual­men­te le ope­ra­zio­ni del database MySQL vengono rea­liz­za­te ri­cor­ren­do ai comandi SQL standard come SELECT, INSERT, UPDATE e DELETE. Ap­pro­fon­di­re­mo questi comandi nei prossimi paragrafi del nostro tutorial MySQL.

Ov­via­men­te si sarebbero potuti salvare i dati sui due autori e le loro opere in un'unica tabella. Una gestione dei dati di questo tipo comporta che un database contenga una grande quantità di voci ri­don­dan­ti, perché ad esempio i dati delle colonne nome e cognome sarebbero dovuti essere pre­sen­ta­ti sin­go­lar­men­te per ogni opera. Una ri­don­dan­za simile non so­vrac­ca­ri­ca solo la memoria, ma porta anche al fatto che debbano essere ef­fet­tua­ti degli ag­gior­na­men­ti in più parti del database. Lavorando con i sistemi di database re­la­zio­na­li, ci si limita perciò a rap­pre­sen­ta­re un dato per tabella. In questo caso si parla di una nor­ma­liz­za­zio­ne dei dati.   

Il campo di ap­pli­ca­zio­ne prin­ci­pa­le di MySQL è la me­mo­riz­za­zio­ne dei dati nell’ambito delle pagine web dinamiche. La com­bi­na­zio­ne di MySQL con il web server Apache e il lin­guag­gio di scripting PHP o Perl si è affermato come struttura software classica nello sviluppo web. Lo stack per il web si può rea­liz­za­re con tutti i sistemi operativi comuni per il server, prendendo quindi il nome di LAMP (Linux), MAMP (macOS) o WAMP (Windows).

Per i prin­ci­pian­ti di MySQL con­si­glia­mo di uti­liz­za­re l’ambiente di test locale XAMPP, così da ac­cu­mu­la­re delle prime espe­rien­ze con il sistema di gestione di database. Da notare però che la sua ultima versione si basa su MariaDB.

Per farvi com­pren­de­re meglio le basi MySQL, uti­liz­ze­re­mo degli esempi concreti. La nostra in­tro­du­zio­ne a MySQL si serve dell’ambiente di test XAMPP. Gli snippet del codice e gli screen­shot si con­cen­tra­no sulle ope­ra­zio­ni del database che sono state rea­liz­za­te tramite PHP grazie a un server Apache in locale su un computer Windows. Al posto del classico database MySQL viene uti­liz­za­to il fork MariaDB. Al momento i due sistemi di database di­mo­stra­no una totale com­pa­ti­bi­li­tà per quanto riguarda l'e­se­cu­zio­ne delle ope­ra­zio­ni. Per voi, in quanto utenti finali, nell’ambito di un tutorial per prin­ci­pian­ti non fa dif­fe­ren­za lavorare con un database MySQL o MariaDB. Come in­stal­la­re un simile ambiente di test locale sul vostro computer Windows è l’argomento prin­ci­pa­le del nostro tutorial su XAMPP. Se volete fare un po’ di gavetta per imparare a usare i database re­la­zio­na­li, vi con­si­glia­mo di optare di­ret­ta­men­te per MariaDB. Un ambiente di test al­ter­na­ti­vo basato su MySQL è di­spo­ni­bi­le gra­tui­ta­men­te con AMPPS. Inoltre c’è la pos­si­bi­li­tà di impostare uno stack per­so­na­liz­za­to. Se ne­ces­sa­rio, MySQL e MariaDB si possono combinare con diversi sistemi operativi, web server e linguaggi di scripting. I pacchetti di download gratuiti, con licenza GPL, sono di­spo­ni­bi­li su mysql.it e mariadb.com. Trovate una guida det­ta­glia­ta per l’in­stal­la­zio­ne sulle diverse piat­ta­for­me nella do­cu­men­ta­zio­ne in inglese di MySQL e MariaDB.

Nella gestione di MySQL ci im­bat­tia­mo nell’ap­pli­ca­zio­ne web gratuita ph­p­MyAd­min, già contenuta nel pacchetto di in­stal­la­zio­ne di XAMPP, ma che è anche di­spo­ni­bi­le se­pa­ra­ta­men­te sul sito del progetto ufficiale come pacchetto da scaricare. ph­p­MyAd­min si presenta come un software standard per l’am­mi­ni­stra­zio­ne di database MySQL nel World Wide Web. L’ap­pli­ca­zio­ne web scritta in PHP e Ja­va­Script consente di eseguire ope­ra­zio­ni di database tramite un’in­ter­fac­cia grafica. Così create e gestite le tabelle del vostro database re­la­zio­na­le fa­cil­men­te con un click sul browser. La co­no­scen­za dei comandi SQL non è ini­zial­men­te ne­ces­sa­ria.

Sull’angolo sinistro dell’in­ter­fac­cia utente trovate il pannello di na­vi­ga­zio­ne. Qui sono pre­sen­ta­te tutte le tabelle a cui potete accedere dal vostro database grazie a ph­p­MyAd­min. Sotto il logo del programma nell’angolo sinistro in alto, il software mostra i link alla pagina iniziale e mette a di­spo­si­zio­ne la do­cu­men­ta­zio­ne ufficiale. Inoltre avete la pos­si­bi­li­tà di con­fi­gu­ra­re il pannello di na­vi­ga­zio­ne e ag­gior­na­re la sua vi­sua­liz­za­zio­ne.

Iniziamo il nostro corso crash MySQL, creando il nostro primo database.

Con­fer­ma­te quanto inserito con un click su “Create“ (crea). Il database creato verrà vi­sua­liz­za­to nel pannello di na­vi­ga­zio­ne sulla pagina sinistra dello schermo. I nuovi database sono ini­zial­men­te vuoti. Per inserire dei dati, create nel prossimo passaggio una tabella.

Nella seguente tabella trovate una de­scri­zio­ne della struttura della tabella e delle possibili for­mat­ta­zio­ni.

Le opzioni pre­sen­ta­te qui com­pren­do­no le im­po­sta­zio­ni più im­por­tan­ti per la for­mat­ta­zio­ne delle colonne delle tabelle. Se con l’aiuto della barra di scor­ri­men­to scorrete ancora la pagina a destra, trovate altre im­po­sta­zio­ni a cui non abbiamo fatto ri­fe­ri­men­to in questo tutorial MySQL.

La tabella qui sotto presenta i diversi tipi di dati ela­bo­ra­bi­li con MySQL e MariaDB, così come i relativi domini di valori e lo spazio richiesto.

Per la tabella di esempio users abbiamo optato per le seguenti im­po­sta­zio­ni:

i possibili valori per la colonna id sono stati definiti come numeri interi (Integer, INT) e sono con­tras­se­gna­ti dall’attributo UNSIGNED. La colonna id può accettare solo valori numerici positivi. Alla voce “Index“ abbiamo scelto per id l’opzione PRIMARY. Il numero di iden­ti­fi­ca­zio­ne funge così da chiave primaria per la tabella users. La spunta su “A_I“ (Auto_Increment) segnala al Database Ma­na­ge­ment System che gli ID per ogni voce devono essere generati au­to­ma­ti­ca­men­te con numeri pro­gres­si­vi.

I valori per le colonne forename, surname, email e password sono stati definiti come tipo di dati VARCHAR. Si tratta così di sequenze di caratteri variabili, di cui abbiamo limitato la lunghezza (M) con l’opzione “Length/Value“ a 50 caratteri. Per la colonna email è stata anche attivata l’opzione Index UNIQUE. In questo modo ci as­si­cu­ria­mo che ogni indirizzo e-mail nella nostra tabella sia salvato solo una volta.

Per le colonne created_at e updated_at abbiamo scelto il tipo di dati TIMESTAMP. Il Database Ma­na­ge­ment System salva le in­di­ca­zio­ni temporali per la creazione e l’ag­gior­na­men­to dei record nel formato YYYY-MM-DD HH:MM:DD. Visto che il sistema per ogni nuova voce deve creare au­to­ma­ti­ca­men­te un timestamp, scegliamo per la colonna created_at il valore standard CURRENT_TIMESTAMP. La colonna updated_at diventa rilevante solo quando ag­gior­nia­mo una voce. Perciò con­sen­tia­mo i valori Null per questa colonna e im­po­stia­mo NULL come valore standard.

Una de­scri­zio­ne det­ta­glia­ta della sintassi SQL verrà fornita nel paragrafo dedicato alle in­ter­ro­ga­zio­ni del database.

Un click su “Save“ salva le im­po­sta­zio­ni prese. La tabella users viene mostrata nel pannello di na­vi­ga­zio­ne sotto il database test.

Per la gestione di tabelle di dati avete a di­spo­si­zio­ne nella barra del menu diverse tab. Se volete cambiare la struttura di una tabella di dati, se­le­zio­na­te la tab “Structure”. Ag­giun­ge­te nuovi record alla tabella dalla tab “Insert” (aggiungi). Inoltre ph­p­MyAd­min consente di ricercare dati nelle tabelle, di gestire i permessi e di esportare i record o di im­por­tar­li da altre tabelle.

Le modifiche alla struttura delle tabelle possono portare in alcune cir­co­stan­ze alla perdita di dati. Prima di elaborare o eliminare le colonne delle tabelle già esistenti, dovreste in ogni caso creare un backup del vostro database. Spo­sta­te­vi nella tab “Export” (Esporta), scegliete il formato di dati de­si­de­ra­to per il backup e con­fer­ma­te con “Go”. Si apre una finestra di dialogo in cui il vostro browser richiede la de­sti­na­zio­ne di ar­chi­via­zio­ne per il download. Rap­pre­sen­ta un’al­ter­na­ti­va al backup del database da ph­p­MyAd­min il programma di sicurezza gratuito My­SQL­Dum­per.

Dopo aver riempito la tabella di esempio users con i record, nei prossimi paragrafi vedremo come si possono in­ter­ro­ga­re i dati immessi tramite PHP at­tra­ver­so il web server Apache.

Per fare ciò, sta­bi­lia­mo nel primo passaggio una con­nes­sio­ne al database. Nel lin­guag­gio PHP sono a di­spo­si­zio­ne tre in­ter­fac­ce: MySQL Extension, MySQL Improved Extension (MySQLi) e PHP Data Objects (PDO).

• MySQL Extension: MySQL Extension è un’in­ter­fac­cia mi­glio­ra­ta di MySQL che prima era molto popolare, ma oggi risulta antiquata. Rispetto a MySQLi e PDO la vecchia MySQL Extension ha lo svan­tag­gio di non sup­por­ta­re né i prepared sta­te­men­ts, né i parametri indicati.

• MySQLi: MySQLi è una versione mi­glio­ra­ta della classica esten­sio­ne PHP per l’accesso ai database MySQL. L’in­ter­fac­cia lavora sia in maniera pro­ce­du­ra­le che orientata agli oggetti. Viene uti­liz­za­ta solo sui database MySQL e MariaDB.

• PDO: nel caso di PHP Data Objects (PDO) si tratta di un’in­ter­fac­cia orientata agli oggetti che mette a di­spo­si­zio­ne un livello di astra­zio­ne per l’accesso ai dati. Così non solo si possono integrare tramite PDO i database MySQL, ma anche altri sistemi di database come Post­gre­SQL, Oracle, MSSQL o SQLite nel PHP.

Di seguito ci li­mi­te­re­mo alle con­nes­sio­ni database tramite PDO.

Per poter ef­fet­tua­re delle richieste al database nell’ambito di uno script PHP, bisogna prima di tutto au­ten­ti­fi­car­si. Una con­nes­sio­ne database tramite PDO viene in­stau­ra­ta ri­cor­ren­do alla seguente stringa di codice:

Si consiglia di inserirla all’inizio di ogni script che contiene ope­ra­zio­ni del database.

Uti­liz­zia­mo la parola chiave PHP new, per creare un’istanza della classe base PDO. I suoi co­strut­to­ri prevedono es­sen­zial­men­te tre parametri, cioè il Data Source Name (DSN), un nome utente e la password per il database, se presente. Nel nostro caso il DSN è composto dai seguenti parametri:

• Driver PDO del database: mysql
• Server database (host=): localhost
• Nome del database (dbname=): test
• Set di caratteri (charset=): utf8

Se non avete definito i dati di accesso per il vostro database, uti­liz­za­te il nome utente root e lasciate vuoto lo spazio riservato alla password:

La con­nes­sio­ne al database viene salvata in una variabile $pdo, così nel corso del codice del programma potete rimandare alla con­nes­sio­ne del database.

Se è stata stabilita una con­nes­sio­ne al database, potete inviare con il codice di script seguente un numero il­li­mi­ta­to di richieste al database. Una volta terminato lo script, si in­ter­rom­pe anche la con­nes­sio­ne al database.

Per ri­chia­ma­re i dati dal nostro database, ri­cor­ria­mo al lin­guag­gio di database SQL, che si basa se­man­ti­ca­men­te sull’inglese e viene mantenuto vo­lon­ta­ria­men­te semplice. La sintassi SQL è am­pia­men­te au­toe­spli­ca­ti­va.

Nel lin­guag­gio SQL si lavora con statement, al­tri­men­ti de­no­mi­na­ti come in­ter­ro­ga­zio­ni o richieste.

Una semplice query SELECT è composta, ad esempio, dai seguenti com­po­nen­ti:

Prima di tutto definite il comando SQL SELECT e le ri­spet­ti­ve colonne e tabelle alle quali deve fare ri­fe­ri­men­to il comando; un punto e virgola chiude lo statement.

Inoltre avete la pos­si­bi­li­tà di ampliare lo statement con una con­di­zio­ne opzionale e una funzione di clas­si­fi­ca­zio­ne o di rag­grup­pa­men­to:

In questo caso per con­ven­zio­ne i comandi SQL si scrivono in maiuscolo, mentre il nome del database, delle tabelle e degli altri campi sono minuscoli, per motivi di leg­gi­bi­li­tà. SQL è prin­ci­pal­men­te un lin­guag­gio non for­mat­ta­to e non fa perciò dif­fe­ren­za tra maiuscolo e minuscolo.

Se uti­liz­za­te i nomi delle tabelle e delle colonne che cor­ri­spon­do­no alle parole chiave SQL già definite (non con­si­glia­to), dovete inserire i backtick (``).

Vi chiariamo la sintassi di semplici statement SQL portando ad esempio i comandi SELECT, INSERT, UPDATE e DELETE.

Uti­liz­za­te il comando SELECT per ri­chia­ma­re le serie di dati (rows) se­le­zio­na­te da un numero in­de­fi­ni­to di tabelle. Se ad esempio volete che vengano vi­sua­liz­za­ti sul browser il nome, il cognome e gli indirizzi e-mail di tutti gli utenti presenti nella tabella di esempio, create nella cartella htdocs del vostro ambiente XAMPP un nuovo file PHP text.php e inserite il seguente script:

Il codice di esempio si legge così: prima iniziamo lo script con il tag di apertura PHP <?php. Nella riga 2 in­stau­ria­mo la con­nes­sio­ne al nostro database test su localhost e la salviamo nella variabile $pdo. Lo statement SQL con il comando SELECT si trova nella riga 3. Qui si rimanda al Database Ma­na­ge­ment System per ri­chia­ma­re le colonne forename, surname e email dalla tabella users. Salviamo questo statement nelle variabili $sql.

Le righe da 4 a 7 indicano un ciclo foreach, che ci offre la pos­si­bi­li­tà di iterare un array qualsiasi, cioè di at­tra­ver­sa­re per gradi una struttura di dati. Quale array vogliamo iterare e come i dati richiesti devono essere salvati, lo definiamo tra parentesi dopo il costrutto foreach:

La variabile $pdo prende in con­si­de­ra­zio­ne il database de­si­de­ra­to tramite la con­nes­sio­ne definita nella riga 2, che inviamo con la funzione query() salvata nello statement SQL della variabile $sql.

Il web server annuncia così le colonne forename, surname e email della tabella users dal database test e at­tra­ver­sa, nell’ambito di un ciclo foreach, ogni singola riga della tabella. La parola chiave PHP as definisce dove devono essere salvati i dati nella variabile array $row.

Nel primo passaggio del ciclo foreach questo array si presenta così.

Se tutte le colonne devono essere lette da una tabella di dati, uti­liz­za­te nello statement SQL l’asterisco (*) come se­gna­po­sto.

In entrambi gli esempi pre­sen­ta­ti pre­ce­den­te­men­te il web server re­sti­tui­sce i dati utente nell’ordine in cui sono stati inseriti nella tabella users (in base all’ID). Se volete vi­sua­liz­za­re i dati secondo un certo ordine, definite la sequenza con la parola chiave SQL ORDER BY. Nel seguente esempio i dati vengono vi­sua­liz­za­ti in ordine al­fa­be­ti­co per nome:

La creazione di record nel database raramente avviene ma­nual­men­te tramite ph­p­MyAd­min. Di solito i dati vengono scritti nel database nell’ambito dell’ese­cu­zio­ne degli script da parte del web server, ad esempio nel momento in cui un utente compila un modulo online su un sito o un cliente lascia un commento su un negozio online. In entrambi i casi viene uti­liz­za­to in back­ground il comando SQL INSERT. Uno statement SQL con il comando INSERT viene creato secondo lo schema seguente:

E si legge così: apri la tabella citata e aggiungi i valori 1, 2 e 3 nelle colonne 1, 2 e 3.

Un semplice script PHP per ag­giun­ge­re alla nostra tabella di esempio users un altro record, potrebbe essere così:

Se volete ag­gior­na­re un record già esistente, uti­liz­za­te il comando SQL UPDATE secondo lo schema seguente:

Espresso a parole questo statement SQL significa: seleziona la tabella indicata e so­sti­tui­sci il valore nella colonna1 con il valore1 e il valore nella colonna2 con il valore2, a patto che la colonna3 contenga il valore3. At­ten­zio­ne: se di­men­ti­ca­te di inserire la con­di­zio­ne, MySQL so­vra­scri­ve i campi coinvolti in tutti i record.

Abbiamo così a che fare con uno statement SQL che collega un’ope­ra­zio­ne database a una con­di­zio­ne. Trasposto alla nostra tabella di esempio, l’indirizzo e-mail dell’utente John Doe si può ag­gior­na­re tramite lo script PHP seguente:

Per tra­sfe­ri­re i valori, uti­liz­zia­mo il seguente statement UPDATE:

Visto che vogliamo ag­gior­na­re tutti i record, non for­mu­lia­mo alcuna con­di­zio­ne per l’update

Se nel vostro database lavorate con ID, grazie a questi è possibile iden­ti­fi­ca­re i record da can­cel­la­re. Nel caso in cui vogliate ad esempio eliminare il record 5 dalla nostra tabella di esempio, procedete come segue:

Il comando SQL DELETE elimina sempre un’intera riga di database. Se volete eliminare solo i valori in colonne spe­ci­fi­che di un record, rea­liz­za­te questo intento con uno statement UPDATE. Con UPDATE tabella SET colonna = NULL WHERE …potete assegnare a una colonna il valore NULL, premesso ov­via­men­te che per la colonna coinvolta abbiate con­sen­ti­to l’uso del valore NULL.

Con PDO si possono impostare le ope­ra­zio­ni del database come prepared statement. Queste “richieste già pronte” sono oggi normale routine nello sviluppo web e vengono sup­por­ta­te da tutti i moderni Database Ma­na­ge­ment System.

Negli esempi fatti fino a qui abbiamo pre­sen­ta­to i valori dei parametri di­ret­ta­men­te nello statement SQL. Invece, i prepared statement lavorano con i se­gna­po­sto, che vengono so­sti­tui­ti solo suc­ces­si­va­men­te con dei valori. Ciò consente al Database Ma­na­ge­ment System di ve­ri­fi­ca­re la validità dei parametri, prima di ela­bo­rar­li. I prepared statement offrono una pro­te­zio­ne efficace contro la SQL injection, a patto che vengano inseriti in maniera regolare nel codice sorgente. In questo schema di attacco gli hacker creano o cambiano i comandi SQL per arrivare ai dati sensibili, per so­vra­scri­ver­li o inserire abu­si­va­men­te i propri comandi in un sistema.

La SQL injection si basa su una vul­ne­ra­bi­li­tà co­no­sciu­ta quando si lavora con i database SQL: se gli input utente vengono tra­sfe­ri­ti ad esempio con parametri statici tramite $_GET, ciò offre la pos­si­bi­li­tà agli hacker di ar­ric­chi­re l’input con i me­ta­ca­rat­te­ri, com­por­tan­do così degli effetti in­de­si­de­ra­ti, a patto che giungano all’in­ter­pre­te SQL senza ma­sche­ra­tu­ra. Tramite richieste pa­ra­me­triz­za­te si può evitare ef­fi­ca­ce­men­te tutto questo. I prepared statement fungono così da template per i comandi SQL che possono essere co­mu­ni­ca­ti se­pa­ra­ta­men­te dai parametri effettivi al database. Questi con­va­li­da­no i dati trasmessi, ma­sche­ra­no au­to­ma­ti­ca­men­te i me­ta­ca­rat­te­ri e ag­giun­go­no i parametri al posto del se­gna­po­sto nello statement SQL.

I prepared statement offrono, oltre all’aspetto sicurezza, anche un mi­glio­ra­men­to della per­for­man­ce. Ciò è di­mo­stra­to quando lo stesso comando SQL deve venire eseguito in un ciclo con parametri diversi. Dopo che un prepared statement è stato ana­liz­za­to una volta, sarà presente nel sistema di database e deve essere sem­pli­ce­men­te eseguito con nuovi parametri. Così facendo, si possono no­te­vol­men­te ac­ce­le­ra­re le richieste che risultano più com­pli­ca­te.

Nel PDO i prepared statement vengono messi in atto grazie alla funzione prepare(), che prepara uno statement per l’ese­cu­zio­ne e re­sti­tui­sce un oggetto di statement. Come se­gna­po­sto per i ri­spet­ti­vi valori vengono uti­liz­za­ti un punto in­ter­ro­ga­ti­vo (?) o un parametro no­mi­na­ti­vo (Named Parameter).

L’esempio di codice seguente indica l’ope­ra­zio­ne del database INSERT come prepared statement con parametri senza nome:

Per prima cosa generiamo grazie alla funzione prepare() un oggetto di statement della richiesta de­si­de­ra­ta e lo salviamo nell’array $statement. Al posto di valori di parametri concreti viene usato il punto in­ter­ro­ga­ti­vo come se­gna­po­sto.

Se uno statement SQL comprende solo il se­gna­po­sto, i valori trasmessi se­pa­ra­ta­men­te devono essere collegati a questo nel codice seguente. Nel PHP si utilizza anche la funzione bindParam(). Uti­liz­zia­mo l’operatore freccia (->) per ricorrere al metodo dell’oggetto $statement e as­se­gnar­lo a questa variabile (1 cor­ri­spon­de al primo punto in­ter­ro­ga­ti­vo, 2 al secondo, ecc.).

Il template SQL così creato può ora essere eseguito con i parametri de­si­de­ra­ti tutte le volte che si vuole. Nell’esempio corrente definiamo i valori delle variabili per due record. L’ese­cu­zio­ne dello statement SQL già pronto avviene per ogni record tramite execute().

Più chiaro di un se­gna­po­sto con il punto in­ter­ro­ga­ti­vo sono i parametri no­mi­na­ti­vi. Si tratta così di parametri per­so­na­liz­za­ti a cui si può dare un nome, se ne­ces­sa­rio, secondo il seguente schema:

I parametri no­mi­na­ti­vi non devono però contenere spazi vuoti o trattini (-). Uti­liz­za­te al loro posto un un­der­sco­re (_).

Il seguente esempio indica l’ope­ra­zio­ne di database INSERT come prepared statement con parametri no­mi­na­ti­vi:

Nel prepared statement si trovano i parametri no­mi­na­ti­vi :forename, :surname, :email e :password, che uniamo tramite bindParam() alle variabili $forename, $surname, $email e $password. Nell’esempio appena riportato abbiamo scelto di far coin­ci­de­re il nome dei parametri e delle variabili con quello uti­liz­za­to per le varie colonne nella nostra tabella di esempio. Questo però non avviene in au­to­ma­ti­co. Una de­no­mi­na­zio­ne unitaria è tuttavia con­si­glia­ta, così da avere un codice sorgente di facile lettura. L’as­se­gna­zio­ne dei valori delle variabili e l’ese­cu­zio­ne degli statement SQL avviene ana­lo­ga­men­te all’esempio proposto pre­ce­den­te­men­te.

MySQL e MariaDB sup­por­ta­no diverse funzioni per lavorare con le in­di­ca­zio­ni temporali e la data. Trovate una lista completa a questo indirizzo. Nel nostro tutorial MySQL per prin­ci­pian­ti ci limitiamo a esporre solo alcuni di questi.

Un possibile scenario di ap­pli­ca­zio­ne per le funzioni per l’ora e la data su MySQL è dato ad esempio dalle in­ter­ro­ga­zio­ni del database, in cui devono essere ana­liz­za­ti tutti i record che sono stati creati in un giorno preciso.

Il seguente script fornisce tutti i record della nostra tabella di esempio users, che è stata creata oggi:

Per vi­sua­liz­za­re solo le voci di oggi, uti­liz­zia­mo la seguente con­di­zio­ne nello statement SQL:

Per prima cosa estraiamo la data con l’aiuto della funzione DATE() dal timestamp salvato nella colonna created_at e lo sin­cro­niz­zia­mo con la data attuale nel prossimo passaggio. Il comando SELECT seleziona così solo le voci in cui il timestamp coincide con la data corrente.

In al­ter­na­ti­va possiamo scegliere la voce che abbiamo ag­gior­na­to al 16.12.2016. Così dobbiamo solo adattare la con­di­zio­ne del nostro statement SQL:

In questo caso l’in­di­ca­zio­ne della data estratta dal timestamp viene ag­gior­na­ta con una data concreta. Inoltre le richieste vengono de­li­mi­ta­te al­l'in­ter­no di un  anno, di un mese o di un giorno in par­ti­co­la­re.

Il seguente statement fa ri­fe­ri­men­to a tutte le voci nella tabella users, che sono state create a dicembre:

Oltre al segno dell’uguale, SQL supporta anche nelle con­di­zio­ni i seguenti operatori di confronto:

Inoltre si possono collegare più con­di­zio­ni con gli operatori logici:

Il seguente statement seleziona ad esempio tutte le voci che sono state create dopo febbraio e prima di aprile:

Finora abbiamo salvato nel nostro database le in­di­ca­zio­ni della data e quelle temporali nel formato indicato. Con MySQL e MariaDB non siete però obbligati ad uti­liz­za­re proprio quello. La funzione DATE_FORMAT() vi dà la pos­si­bi­li­tà di for­mat­ta­re i dati e l’ora a vostro pia­ci­men­to con i parametri opzionali.

La tabella seguente indica i possibili parametri per la funzione DATE_FORMAT() in base alla do­cu­men­ta­zio­ne MySQL.

Se non si può eseguire uno script come si vorrebbe, ciò dipende so­li­ta­men­te dagli errori sin­tat­ti­ci nel codice sorgente o dalle tabelle, colonne e variabili che pre­sen­ta­no dei nomi errati. Il server database non mostra però per forza un messaggio di errore. Spesso non si ottiene il risultato sperato e non seguono neanche in­di­ca­zio­ni di un’ope­ra­zio­ne errata.

Con errorInfo() PDO mette perciò a di­spo­si­zio­ne una funzione con cui si può ri­chia­ma­re in modo mirato in­for­ma­zio­ni avanzate sugli errori ve­ri­fi­ca­ti­si nell’ultima ope­ra­zio­ne di database, ad esempio per vi­sua­liz­zar­le sul browser.

Nel seguente script per l’ag­gior­na­men­to dell’indirizzo e-mail viene uti­liz­za­ta la funzione errorInfo() combinata a un ciclo if, la cui con­di­zio­ne è la corretta ese­cu­zio­ne dello statement SQL. Se questo viene eseguito senza errori, il web server re­sti­tui­sce la stringa Update suc­ces­sful. Al­tri­men­ti viene eseguito il codice pre­sen­ta­to sotto else.

Nell’esempio attuale in­for­mia­mo l’utente che si è ve­ri­fi­ca­to un errore SQL e forniamo, tramite errorInfo(), lo statement SQL e le in­for­ma­zio­ni avanzate sugli errori:

Se eseguiamo lo script dal web server, riceviamo le seguenti in­for­ma­zio­ni:

Nella prassi le in­for­ma­zio­ni det­ta­glia­te sugli errori vengono però vi­sua­liz­za­te raramente sul browser. Gli utenti hanno poche pos­si­bi­li­tà di riuscire a risolvere il problema con queste in­for­ma­zio­ni. Invece, i po­ten­zia­li hacker sfruttano questi messaggi di errore per in­ter­cet­ta­re le richieste SQL e rilevare così le vul­ne­ra­bi­li­tà di un’ap­pli­ca­zio­ne. Perciò si consiglia di informare gli utenti in maniera generica in caso di errori e di salvare in­ter­na­men­te le in­for­ma­zio­ni relative a degli errori concreti. Si può ad esempio procedere nel modo seguente:

La tabella indica una selezione delle nostre canzoni preferite degli anni 60 e dovrebbe servire a rap­pre­sen­ta­zio­ne di un esempio negativo di una struttura del database sbagliata.

Di primo acchito si nota che le tabelle pre­sen­ta­no in­nu­me­re­vo­li campi di dati ri­don­dan­ti. Ri­sol­via­mo la si­tua­zio­ne, sud­di­vi­den­do i dati nell’ambito della nor­ma­liz­za­zio­ne in tabelle separate e col­le­gan­do­li con chiavi esterne.

Una buona struttura del database si con­trad­di­stin­gue per via della poca ri­don­dan­za. Si possono evitare le voci doppie tramite la nor­ma­liz­za­zio­ne delle tabelle. All’interno del modello di database re­la­zio­na­le si sono affermate tre forme normali che si basano le une sulle altre e che pre­sta­bi­li­sco­no regole fisse per la strut­tu­ra­zio­ne ottimale dei dati.

Una tabella cor­ri­spon­de alla prima forma normale, quando tutti i valori degli attributi sono atomici. I valori di un attributo si con­si­de­ra­no atomici, quando con­ten­go­no solo un’in­for­ma­zio­ne. Questo concetto si può chiarire grazie al nostro esempio negativo.

Con­si­de­ra­te, ad esempio, le colonne album_title e interpret nella tabella album. Al posto di eseguire le in­for­ma­zio­ni contenute nella tabella in una colonna separata, le abbiamo rese vo­lu­ta­men­te facili e abbiamo annotato le in­for­ma­zio­ni sull’anno di pub­bli­ca­zio­ne di un album e la presenza del disco sem­pli­ce­men­te tra parentesi tonde, dietro il titolo dell’album e le in­di­ca­zio­ni sull’in­ter­pre­te. Ma pagheremo le con­se­guen­ze della nostra scelta in un secondo momento, quando ad esempio vogliamo ri­chie­de­re tutti i titoli che sono stati pub­bli­ca­ti in un anno in par­ti­co­la­re.

Vi con­si­glia­mo perciò di creare le tabelle secondo le regole della prima forma normale. Per la nostra tabella di esempio sarebbe così:

Se ora volessimo ri­pro­dur­re un titolo preciso nel nostro database com­pren­si­vo delle in­for­ma­zio­ni ri­guar­dan­ti album e inteprete, dovremmo collegare le tre tabelle separate con il comando SQL JOIN e la ri­spet­ti­va chiave esterna.

Le relazioni della chiave esterna vi con­sen­to­no di aprire con­tem­po­ra­nea­men­te dati da diverse tabelle con un unico statement SQL. Perciò su MySQL e MariaDB avete a di­spo­si­zio­ne quattro tipi di JOIN:

• INNER JOIN: nel caso di un INNER JOIN il Database Ma­na­ge­ment System ricerca delle voci comuni in entrambe le tabelle collegate tramite JOIN. Vengono letti solo i record in cui ci sono delle cor­ri­spon­den­ze, ovvero laddove i valori nelle colonne collegate (chiave primaria e chiave esterna) coin­ci­do­no in entrambe le tabelle.

• OUTER JOIN: in un OUTER JOIN si distingue tra tabella di sinistra e di destra. A dif­fe­ren­za di un INNER JOIN non vengono solo letti i record in cui si trovano delle cor­ri­spon­den­ze in entrambe le tabelle, ma anche tutti i record rimanenti della tabella di destra e/o di sinistra.

• LEFT JOIN: vengono letti tutti i record della tabella sinistra e quelli della tabella destra dove vi sono delle cor­ri­spon­den­ze.

• RIGHT JOIN: vengono letti tutti i record della tabella destra e quelli della tabella sinistra in cui si trovano delle cor­ri­spon­den­ze.

Nel nostro tutorial MySQL ci limitiamo all’INNER JOIN.

La sintassi di un INNER JOIN cor­ri­spon­de al seguente schema di base:

Il comando SQL SELECT combinato con il se­gna­po­sto * dà l’istru­zio­ne al Database Ma­na­ge­ment System di ana­liz­za­re i valori di tutte le colonne per far valere le con­di­zio­ni delle clausole ON e WHERE.

Visto che si tratta di un INNER JOIN, vengono raccolti solo i record in cui si trova una cor­ri­spon­den­za tra la chiave esterna della tabella1 e la chiave primaria della tabella2, prese dal database. Inoltre grazie alla clausola WHERE si può definire un filtro opzionale.

Ve lo spie­ghia­mo con un esempio che fa ri­fe­ri­men­to alle nostre tabelle nor­ma­liz­za­te album, interpret e title:

Lo script di esempio vi indica un INNER JOIN, dove la tabella album viene collegata con quella interpret. Ana­liz­zia­mo solo i record in cui sussiste una cor­ri­spon­den­za tra chiave primaria ed esterna.

In uno statement SQL abbiamo così ana­liz­za­to le in­di­ca­zio­ni sull’in­ter­pre­te dalla tabella interpret e le in­for­ma­zio­ni sul titolo dell’album e l’anno di pub­bli­ca­zio­ne del disco contenute nella tabella album.

Con una con­di­zio­ne nella clausola WHERE si può de­li­mi­ta­re quali record del join vengano vi­sua­liz­za­ti. Se ad esempio vogliamo solo vi­sua­liz­za­re gli album che sono stati pub­bli­ca­ti nel 1968, possiamo procedere nella seguente maniera:

Grazie al comando JOIN si possono unire molte tabelle a una serie di dati. Nell’esempio seguente col­le­ghia­mo la tabella album in un INNER JOIN con le tabelle interpret e title, per poter vi­sua­liz­za­re tutte le in­for­ma­zio­ni sui titoli delle canzoni salvate nel database.

Il nostro tutorial MySQL per prin­ci­pian­ti è solo un assaggio, che vi permette di av­vi­ci­nar­vi alle basi dei sistemi di database basati sul lin­guag­gio SQL e che getta luce sulle semplici ope­ra­zio­ni di database, grazie all’uso di esempi concreti. Se il vostro interesse tra­va­li­cas­se le pos­si­bi­li­tà di ap­pli­ca­zio­ne rap­pre­sen­ta­te, vi con­si­glia­mo di leggere la do­cu­men­ta­zio­ne dei DBMS MySQL e MariaDB, che potete trovare nel paragrafo in­tro­dut­ti­vo. Inoltre su Internet trovate in­nu­me­re­vo­li siti, che pro­pon­go­no tutorial ed esempi di ap­pli­ca­zio­ne per questo famoso Database Ma­na­ge­ment System. In più si consiglia la piat­ta­for­ma Internet Stack Overflow, dove un’attiva community di utenti di più 6,5 milioni di svi­lup­pa­to­ri ri­spon­do­no alle domande poste e si scambiano soluzioni ai problemi insorti durante lo sviluppo del software. Ov­via­men­te anche qui su IONOS Digital Guide avete a di­spo­si­zio­ne molti articoli di ap­pro­fon­di­men­to sui database, che trovate con­trol­lan­do tra i tag presenti.