Mysql

Tratto dal sito http://www.html.it - Scritto da: Giangiacomo Patteri

Premessa a Mysql

Mysql è un database relazionale open source.

Utilizzando la fantasia potremo paragonare i database ad una stanza piena di schedari. In altre parole un database ci aiuta a tenere ordinati i nostri dati e poterli raggiungere in ogni momento e da qualsiasi parte.

Mysql è un database molto veloce e professionale. Consente il salvataggio di grandi quantità di dati e l'accesso contemporaneo di molti utenti ( 101 ).

Prima di iniziare una affermazione è d'obbligo.
Molto spesso ( all'inizio ) si confonde SQL con Mysql.
SQL non è una tipologia di database ma il linguaggio utilizzato per connettersi ad essi. Anche Mysql utilizza SQL per dialogare con il resto del mondo. Utilizzando ancora la fantasia potremo dire che mentre Mysql rapresenta vari schedari in cui sono presenti i dati, SQL è il veicolo con il quali questi dati vengono messi nelle nostre mani.

Mysql: organizzazione interna

Se non avete mai usato o visto un database questa lezione cercherà di spiegarvi in parole semplici come è organizzato il database Mysql.

Un database è organizzato in tabelle che possono avere svariate colonne organizzate in righe.
I nostri dati vengono inseriti nei campi con una catalogazione ordinata per colonne.
In una ipotetica Mailing List costruiremo una tabella con una colonna chiamata e-mail ed all'interno dei campi di quella colonna potremo inserire tutti gli indirizzi dei nostri utenti.

Durante l'inserimento, se la colonna è presente Mysql creerà una nuova riga per ogni dato che noi inseriamo.
Naturalmente questo è un piccolo esempio scolastico. Un database articolato potrà avere più tabelle organizzate in più colonne collegate tra di loro.

Per accedere al database, inserire i dati, modificarli e qualsiasi altra azione utilizzeremo le funzioni che PHP ci mette a disposizione nel suo corredo di funzioni. Assieme a PHP utilizzeremo SQL che sarà il linguaggio che utilizzeremo per interfacciare PHP con Mysql.

Possiamo anticipare già da ora come deve essere organizzata una conessione al database.
Per poter prendere i dati dal nostro database occorre stabilire anzitutto una connessione con questo, pensando ancora una volta a Mysql come una stanza contenente tutti i nostri dati quando instauriamo una connessione non stiamo facendo altro che aprire la porta di questa ipotetica stanza.
Una volta connessi possiamo inserire,modificare,cancellare i dati, le colonne, le tabelle del database.
Quando finiamo di fare tutte le operazioni dobbiamo chiudere la connessione ed liberare le risorse del sistema per altri utenti.

Queste sono le operazioni che dobbiamo memorizzare. Da ora in poi vedremo come fare questo nella pratica.

Breve introduzione a SQL

Quasi tutti i database presenti sul mercato per comunicare con l'esterno utilizzano il linguaggio creato dalla IBM molti anni fa che va sotto il nome di SQL.

Anche PHP per comunicare e raccogliere i dati di un database Mysql utilizza SQL. Questa lezione non vuole addentrarsi nel mondo del SQL ma solo riassumere tutte le funzioni di questo linguaggio riguardo l'interrogazione dei database.
All'interno di html.it potete trovare una guida molto esaustiva di questo linguaggio molto semplice.

SQL raccoglie al suo interno delle istruzioni abbastanza intuitive rivolte al database che risponde alla richiesta inviando i dati:

Poniamo di avere una tabella chiamata "ordini" formata da 2 colonne denominate rispettivamente "ordini_da_eseguire" e "ordini_eseguiti":

con l'istruzione:

SELECT * FROM ordini_eseguiti

chiedo al database di inviarmi tutti i dati contenuti della tabella "ordini", il segno * sta per tutti.
Avrei che potuto richiedere i dati di una sola colonna specificando il nome della colonna:

SELECT ordini_eseguiti FROM ordini

in questo modo.

Come potete notare le istruzioni sono molto semplici e concise. Prima di proseguire in questo nostro viaggio consigliamo comunque una lettura veloce di tutti gli altri comandi SQL come:

CREATE
ALTER
INSERT
ORDER BY
JOIN
CROSS JOIN
RIGHT JOIN
INTO
WHERE

Prima Fase: connessione a Mysql

Nelle precedenti lezioni abbiamo visto come funziona un database Mysql ed abbiamo parlato delle tre fasi caratteristiche di una lettura delle tabelle: connessione, manipolazione dati e disconnessione.
Nell' ultima lezione abbiamo invece introdotto il linguaggio da utilizzare nella fase della manipolazione dei dati.

In questa lezione e nelle successive vedremo come unire tutti questi elementi con il nostro PHP.
Più volte vi ho detto che PHP contiene molte funzioni specifiche per Mysql, vedremo di esaminare le più importanti ed impareremo ad inserire modificare i nostri dati.

La prima cosa da fare è la connessione al Mysql. A questo riguardo PHP ci fornisce una funzione apposita: mysql_connect.

La sintassi della funzione di connessione è la seguente:

<?php

mysql_connect(nome_host,nome_utente,password);

?>

Il nome utente e la password sono indispensabili in quanto Mysql è un database molto sicuro e non si può accedere ad esso senza aver prima creato un account.
Se state provando Mysql in locale potete tranquillamente lasciare vuoti questi 2 campi. Se invece dovete provare i vostri script nel vostro sito dovete richiedere lo User e la Password ai vostri amministratori di sistema. Per quanto riguarda il nome dell'host esso è quasi sempre localhost, per maggiore sicurezza potete richiedere anche questo dato ai vostri amministratori di sistema.

Vediamo uno script completo per la connessione:

<?php

// script per la connessione a mysql

$host = 'localhost';
$user = 'vostro_user';
$password = 'vostra_password';

mysql_connect($host,$user,$password) or die ("Non riesco a connettermi");

print "Connessione eseguita";

// fine script di connessione

?>

La funzione "die" ci permette di stampare a video la scritta compresa tra virgolette nel caso la connessione non vada a buon fine. Nel caso PHP non riesca a connettersi a Mysql vedremo la scritta "Non riesco a connettermi al database", nel caso contrario vedremo la scritta "Connessione eseguita".

Abbiamo fatto per metà la prima fase, ora per manipolare i nostri dati dobbiamo selezionare il database da utilizzare.
Mysql permette, per ogni account, illimitati database.

Se non abbiamo ancora creato nessun database dobbiamo prima crearlo e poi selezionarlo, utilizzeremo quindi varie funzioni PHP nel prossimo script:

<?php

// script per la connessione, creazione e selezione di un database
// Mysql

// Mi connetto a Mysql

$host = 'localhost';
$user = 'vostro_user';
$password = 'vostra_password';

mysql_connect($host,$user,$password) or die ("Non riesco a connettermi");

print "Connessione eseguita";

// Creo il database "prova"

mysql_create_db("prova")or die ("Non riesco a creare il database");

// seleziono il database appena creato

mysql_select_db("prova") or die ("Non riesco a selezionare il database");

print "Connessione, creazione, selezione del database eseguita";

// Fine script

Con questo script abbiamo completato la fase di connessione ad un database Mysql.

Abbiamo visto due nuove funzioni PHP:

mysql_create_db("nome_database");

mysql_select_db("nome_database");

Notate che in ogni funzione abbiamo inserito l'istruzione die, questo ci aiuta in caso di problemi ad identificare subito il perchè degli errori e la loro risoluzione.
Nella prossima lezione analizzeremo la manipolazione dei dati.

Tabelle e dati in Mysql

Ora che ci siamo connessi al database da noi scelto possiamo eseguire qualsiasi azione al suo interno: creazione/cancellazione tabelle, creazione/modifica/cancellazione dati all'interno dei campi.

Dobbiamo prima creare una tabella nella quale inserire i dati. A questo scopo utilizzeremo SQL.

Una premessa è d'obbligo prima di iniziare a creare la nostra tabella: è buona abitudine studiare il database e l'organizzazione interna delle nostre tabelle a tavolino prima di cominciare a costruire lo script. Un buon database con una buona organizzazione interna è senz'altro più facile da manipolare e leggere ed inoltre questo migliora la velocità di connessione e manipolazione dei dati.

Ogni tabella dovrà essere identificata da un nome univoco in quanto non è concepita la presenza di due tabelle con lo stesso nome all'interno di un database.
Per quanto riguarda i campi, nella loro creazione bisogna specificare il tipo di dato che il campo dovrà contenere, Mysql supporta una moltitudine di tipologie dei campi a seconda dei dati che si dovranno inserire, vediamo quali sono i tipi più importanti.

Per quanto riguarda i numeri reali Mysql supporta svariati tipi di archiviazione, la differenza fra i tipi elencati sotto non è nel formato del numero che andrà inserito nel campo ma la grandezza del numero( più grande è il numero è più è la memoria utilizzata ):

TINYINT        1 byte
SMALLINT     2 byte
MEDIUMINT  3 byte
INT                  4 byte
BIGINT            8 byte

Per quanto riguarda i numeri in virgola mobile abbiamo invece le seguenti tipologie di archiviazione:

FLOAT 4 byte
DOUBLE 8 byte

Man mano che utilizzerete Mysql imparete a scegliere il tipo di dato più utilizzato per il campo della tabella, per i nostri esempi sceglieremo il tipo INT per l'archiviazione dei dati numerici.

Per quanto riguarda le stringhe sono due i tipi di campo più utilizzati:

CHAR(numero_caratteri)
VARCHAR

Come possiamo intuire mentre in un campo CHAR la lunghezza è fissa e viene stabilita da noi durante la creazione della tabella nel campo VARCHAR il numero di caratteri è variabile e non bisogna specificare preventivamente il numero di caratteri massimo che dovrà contenere il campo. Naturalmente i campi di tipo VARCHAR occuperanno più memoria e la lettura al loro interno impiegherà qualche decimo di secondo in più rispetto ai campi CHAR.

Esistono vari altri tipi di campo ma una loro trattazione esaustiva necessiterebbe di svariate pagine, tutta la documentazione sui tipi di dati supportati da Mysql potete leggerla in italiano nel sito www.mysql.com.

Un altro motivo dunque per cui dobbiamo organizzare anticipatamente il database è anche per la scelta del tipo di campo sapendo che questa influisce sulle prestazioni delle tabelle.

Per capirci: è uno spreco di spazio e di prestazioni dichiarare un tipo di campo VARCHAR se sapete già che quel campo non potrà contenere un numero determinato di caratteri.

Supponiamo di voler costruire una tabella che raccolga una serie di indirizzi e-mail per una eventuale mailing-list del nostro sito. Dovremo costruire una tabella con 3 colonne: una colonna raccoglierà un numero identificativo che ci aiuti ad indicizzare i dati, una colonna per il nome e cognome degli utenti ed infine la colonna che conterrà le e-mail: il codice sarà questo:

<?php

// script per la creazione di una tabella per la catalogazione delle
// e-mail per una mailing list

// per non appesantire il codice supponiamo di esserci già connessi
// al databese con le funzioni viste nelle lezioni precedenti

// scrivo l'istruzione SQL per la creazione della tabella

$sql= "CREATE TABLE mail (
id_utente INT(10) NOT NULL,
nome_cognome CHAR(100) NOT NULL,
mail CHAR(50) NOT NULL)";

// Invio l'istruzione SQL a mysql

mysql_query("$sql") or die ("Non riesco a creare la tabella");

print " La tabella mail è stata creata con successo!";

// fine dello script

?>

In questo modo abbiamo creato la nostra tabella con tre colonne. Vorrei soffermarmi un attimo sull'istruzione SQL che per comodità abbiamo inserito all'interno di una variabile e vorrei anche farvi notare forse la funzione per l'invio delle istruzioni SQL al database: mysql_query.

La sua sintassi è molto semplice:

mysql_query("istruzioni SQL");

Il risultato dipenderà da cio che si chiede di fare al Mysql.

Nelle prossime lezioni vedremo come manipolare i dati all'interno della tabella che abbiamo appena creato.

Manipolazione dati: lettura, inserimento

Ora che abbiamo creato la nostra tabella possiamo iniziare ad inserire i dati. Ripeto che stiamo cercando di costruire un database che memorizzi tutti gli indirizzi e-mail di colore che si iscrivono. Nelle precedenti lezioni abbiamo costruito una tabella formata da tre colonne: la prima che memorizza un numero identificativo per ogni dato che inseriremo, la seconda che memorizza il nome e il cognome dell'utente e la terza serve per l'indirizzo e-mail. Vediamo ora i comandi SQL e le funzioni PHP per inserire i dati.

<?php

// script per inserire i dati nella tabella mail
// per comodità supponiamo di esserci già connessi al database

// voglio inserire 1 nuovi indirizzi, scriveremo:

mysql_query("insert into mail (id_utente, nome_cognome,mail) values ('1','Mario Rossi','mario@suosito.com')");

// fine script

?>

Con questo piccolo script abbiamo inserito un nuovo indirizzo all'interno della tabella mail. Non bisogna specificare a Mysql di creare un nuovo campo in quanto questo avviene in automatico con l'uso dell'istruzione SQL "insert into".

Vi voglio riproporre la sintassi dell'istruzione INSERT INTO perchè è una delle istruzioni più utilizzate e la più complessa perchè molto articolata:

INSERT INTO nome_tabella (nome_campi) values ( dati_da_inserire_nei_campi);

Notate che l'elenco dei dati presente nella seconda coppia di parentesi tonde deve corrispondere all'ordine dei campi che abbiamo inserito nella coppia delle prime parentesi.
Ripetermo lo script tutte le volte che dobbiamo inserire un nuovo campo.

Ora che la nostra tabella contiene qualche dato possiamo leggere al suo interno per sapere quali dati ci sono al suo interno, per far questo utilizzeremo l'istruzione SQL "SELECT":

<?php

// script per leggere i dati contenuti in un campo della tabella
// mail
// per comodità supponiamo di esserci già connessi al database

$dati = mysql_query("select * from mail");

$array = mysql_fetch_array($dati);

// fine script

?>

L'istruzione SELECT chiede i dati di una riga della tabella che abbiamo selezionato nell'istruzione FROM ( nel nostro caso la tabella si chiama mail ). Per poter utilizzare i dati che Mysql invia dobbiamo utilizzare la funzione mysql_fetch_array che crea un array associativo che ha come indice il nome delle colonne, continuando lo script avremo che per visualizzare i dati che abbiamo estrapolato dal database scriveremo:

<?php

// codice per leggere i dati contenuti in un campo

print "Contenuto della colonna id_utente: $array[id_utente] ";

print "Contenuto della colonna nome_cognome: $array[nome_cognome] ";

print "Contenuto della colonna mail: $array[mail] ";

?>

Naturalmente questo script legge una riga per volta e se nella istruzione SELECT non selezionate nulla Mysql restituirà i dati dell'ultima riga inserita, diversamente potete controllare il flusso dei dati Mysql con l'istruzione WHERE:

<?php

// Volendo visualizzare i dati dell'utente Mario Rossi avrei scritto
// in questo modo l'istruzione SQL

$dati = mysql_query("SELECT * FROM mail WHERE nome_cognome='Mario Rossi");

$array = mysql_fetch_array($dati);

// fine script

?>

Nella nostra lingua l'istruzione SQL risulterebbe così organizzata:
"Seleziona (SELECT) tutto (*) dalla tabella ( FROM ) mail dove ( WHERE ) il la colonna nome_cognome è uguale a Mario Rossi.

Questo script va bene nel caso dovessimo leggere solo una riga della tabella, nel caso in cui vogliamo invece leggere tutto il contenuto della tabella dovremo aggiungere un ciclo while così organizzato:

<?php

// script per leggere i dati contenutiin tutti i campi della tabella
// mail
// per comodità supponiamo di esserci già connessi al database

$dati = mysql_query("select * from mail");

while ( $array = mysql_fetch_array($dati)) {

print "Contenuto della colonna id_utente: $array[id_utente] ";

print "Contenuto della colonna nome_cognome: $array[nome_cognome] ";

print "Contenuto della colonna mail: $array[mail] ";

}

// fine script

?>

Con questo script finchè la tabella mail non sarà vuota PHP creerà l'array associativo contenente i dati letti dal database.

Manipolazione dati

Le ultime importanti nozioni sulla manipolazione dei dati inseriti nel database riguardano la modifica e la cancellazione dei dati. A questo scopo, rispetto agli script visti precedentemente riguardo l'inserimento cambiano solo le istruzioni SQL. Per la modifica utilizzeremo l'istruzione UPDATE mentre per la cancellazione l'istruzione DELETE.

Consiglio vivamente di utilizzare queste due istruzioni con cautela per non rischiare di cancellare o modificare tutti i dati che avete inserito all'interno della tabella.

La modifica:

<?php

// script per la modifica dei dati nella tabella mail

// supponiamo di essere già connessi al database

$dati = mysql_query ("UPDATE mail SET mail='mario@tiscalinet.it' WHERE nome_cognome='MARIO ROSSI'");

// fine script

?>

L'istruzione UPDATE è molto semplice, ricordate sempre di specificare il WHERE perchè altrimenti la modifica verrà eseguita in tutti campi della tabella mail ( per questo consiglio l'uso con cautela ).

Vediamo la sintassi dell'istruzione UPDATE:

UPDATE nome_tabella SET nome_colonna='nuovo_valore' WHERE nome_colonna='identificativo_colonna';

Nella creazione della tabella abbiamo all'inizio previsto una colonna che contiene l'dentificativo numerico del campo. Questo indice è importantissimo per le istruzioni di modifica e di cancellazione perchè in questo modo ogni riga ha un numero univoco e non si rischia di cancellare/modificare altre righe. Utilizzando l'identificativo avremo scritto:

Molto più semplice e senza rischio d'errore ( pensate se ci fossero stati due Mario Rossi all'interno della tabella mail).

L'istruzione DELETE permette di cancellare un intera riga dalla tabella. Utilizzate questa istruzione con molta cautela in quanto Mysql non chiede conferme, neanche per la cancellazione di grosse quantità di dati:

<?php

// script per la cancellazione di tutti i dati nella tabella mail

// supponiamo di essere già connessi al database

$dati = mysql_query ("DELETE FROM mail");

// fine script

?>

Con questo script cancelliamo tutte le righe presenti all'interno della tabella mail. Nel caso volessimo cancellare una determinata riga inseriremo nell'istruzione SQL l'istruzione WHERE, come segue:

<?php

// script per la cancellazione di una riga nella tabella mail

// supponiamo di essere già connessi al database

$dati = mysql_query ("DELETE FROM mail where id_utente='1'");

// fine script

?>

Questo script cancellerà la riga in cui l'id_utente è uguale a 1.

Altre funzioni PHP-Mysql

PHP contiene molte funzioni e, per non confondere troppo le idee, vi proponiamo un semplice elenco di quelle più importantanti impegnandoci nel breve futuro a proporvi altra guide pià approfondite solo sul Mysql:

mysql_num_rows()
Restituisce il numero di righe interessate dall'istruzione SQL:

<?php

// mysql_num_rows

$dati = mysql_query("SELECT * FROM mail");

$numero_righe = mysql_num_rows($dati);

?>

mysql_insert_id()
Restituisce l'ultimo id ( se presente ) della riga interessata dall' ultima operazione di INSERT:

<?php

// mysql_insert_id

$dati = mysql_query("INSERT INTO mail (id_utente, nome_cognome, mail values ('2', 'Mario Rossi', mario@tiscalinet.it') ");

$ultimo_id = mysql_insert_id();

?>

mysql_drop_db
Elimina un database Mysql:

<?php

// mysql_drop_db

mysql_drop_db("nome_database_da_eliminare");

?>

mysql_list_dbs
Restituisce la lista dei database presenti nel server Mysql:

<?php

// mysql_list_dbs

$connessione = mysql_connect($host, $user, $password);

mysql_list_dbs("$connessione");

?>

mysql_list_tables
Restituisce la lista delle tabelle presenti nel database selezionato:

<?php

// mysql_list_tables

mysql_list_tables("nome_database");

?>

Questo l'elenco delle funzioni più usate, potete trovare l'elenco completo delle funzioni sul sito del PHP www.php.net nella sezione funzioni per Mysql

Chiusura di una connessione Mysql

Una volta conclusa la fase di manipolazione dei dati è sempre opportuno chiudere tutte le connessioni al server Mysql e magari liberare la memoria occupata dai risultati della query SQL.

E' buona norma specificare sempre queste due funzioni per non caricare inutilmente il server che sta eseguendo i vostri script e dare la possibilità a tutti gli utenti di poter accedere al database.

Com ultimo esempio vi fornisco uno script completo di connessione, manipolazione e disconnessione ad un server Mysql.

<?php

// Mi connetto a Mysql

$host = 'localhost';
$user = 'vostro_user';
$password = 'vostra_password';

mysql_connect($host,$user,$password) or die ("Non riesco a connettermi");

print "Connessione eseguita";

// seleziono il database appena creato

mysql_select_db("mail") or die ("Non riesco a selezionare il database");

print "Il database è stato selezionato";

// visualizzo tutti i campi presenti nella tabella mail

$dati = mysql_query("select * from mail");

// inizio il ciclo while per la visualizzazione delle righe

while ($array = mysql_fetch_array($dati) {

print "Contenuto colonna id_utente: $array[id_utente] <br>";
print "Contenuto colonna nome_cognome: $array[nome_cognome] <br>";
print "Contenuto colonna mail: $array[mail]";

}

// libero la memoria occupata dall'istruzione SELECT

mysql_free_result($dati);

// chiudo la connessione al server Mysql

mysql_close();

// Fine script

Da notare che la funzione mysql_close può non contenere argomenti in quanto Mysql chiuderà automaticamente tutte le connessioni aperte.