Lezioni di PHP - Lezione 3.2 - Lavorare con il database MySQL. Inserimento dei dati con INSERT INTO. Selezione dei dati con SELECT.

Nella lezione precedente abbiamo creato una tabella per il nostro sito. In questa lezione miglioreremo la nostra tabella e inizieremo finalmente a lavorare con il database: inseriremo dati e li estrarremo dalla tabella. Non dovrebbe essere complicato, quindi iniziamo.

Per cominciare, propongo di migliorare la nostra tabella messages. Al momento ha campi per i dati, ma ci serve anche un campo per la numerazione dei record. Se apri il database di Drupal, noterai che nella tabella node c’è un campo nid, usato per numerare i nodi. Faremo la stessa cosa con la tabella messages.

Apriamo phpMyAdmin (http://localhost/tools/phpmyadmin per Denwer) e modifichiamo la nostra tabella. Aggiungiamo una nuova colonna all’inizio della tabella, come mostrato nell’immagine:

Chiamiamo la colonna mid (message id), di tipo INT (numero intero) con lunghezza 11 caratteri. Mettiamo la spunta su AUTO_INCREMENT, che significa che la numerazione verrà incrementata automaticamente per ogni nuovo record. Infine, impostiamo Index su PRIMARY, in modo da rendere questo campo la chiave primaria della tabella.

Se ricordi, avevamo creato un metodo separato nella classe simpleCMS per creare la tabella. Ora dobbiamo modificarlo in modo che aggiunga anche il campo mid. Vai nella tabella messages e clicca su Export:

Arriveremo alla pagina di esportazione della tabella. L’esportazione viene generata come query SQL per creare o modificare tabelle. Se modifichiamo una tabella in phpMyAdmin, possiamo poi copiare la query SQL per la sua creazione: è molto comodo.

Nella pagina di esportazione lascia tutto come predefinito e clicca su OK. PhpMyAdmin genererà la query SQL, che potrà essere mostrata nel browser o scaricata come file — entrambi i modi vanno bene. Copiamo questo codice:

Nota le virgolette oblique usate in phpMyAdmin; in PHP possiamo usare semplici virgolette singole. Copiamo il testo nel nostro metodo buildDB():

  public function buildDB(){
    $sql = "CREATE TABLE IF NOT EXISTS 'messages' (
			  'mid' int(11) NOT NULL AUTO_INCREMENT,
			  'title' varchar(150) DEFAULT NULL,
			  'bodytext' text,
			  'created' varchar(100) DEFAULT NULL,
			  PRIMARY KEY ('mid')
			) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1";
			
    return mysql_query($sql);    
  }

Ora verifichiamo se funziona. Cancelliamo la tabella messages.

Conferma la cancellazione. Ora, quando eseguiremo index.php sul nostro sito, verrà eseguito il metodo buildDB() e la tabella sarà ricreata.

  public function buildDB(){

$sql = "CREATE TABLE Messages
(
mid int NOT NULL AUTO_INCREMENT,
PRIMARY KEY(mid),
title varchar(15),
bodytext text,
created  int(11)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1";

    $result = mysql_query($sql);   
    print_r($result);	
  }

Eseguendo index.php, la tabella Messages verrà creata nuovamente.

Query di inserimento – INSERT INTO

Il database è pronto, possiamo quindi scrivere e leggere messaggi utilizzando il database. Iniziamo con l’inserimento. Apri il metodo write() e modifichiamolo.

L’inserimento di record in una tabella avviene con l’operatore INSERT INTO, nel seguente modo:

  public function write($p) { // metodo per scrivere un messaggio
    $sql = 'INSERT INTO Messages (title, bodytext, created) VALUES ("'. $p["title"] . '", "' . $p["bodytext"] . '", ' . time() . ')';
    return mysql_query($sql);
  } 

Vediamo ora come vengono inseriti i valori. Come già detto, la query SQL inizia con INSERT INTO, seguito dal nome della tabella Messages. Poi indichiamo le colonne in cui vogliamo inserire i valori. Nota che non includiamo mid, perché il suo valore verrà assegnato automaticamente da MySQL, dato che abbiamo impostato l’auto-incremento. Dopo scriviamo VALUES e indichiamo i valori per ciascuna colonna, rispettando l’ordine e la quantità dei campi.

Se dobbiamo inserire stringhe di testo, le racchiudiamo tra virgolette. Bisogna però fare attenzione a non confondere le virgolette del testo SQL con quelle usate per concatenare le variabili PHP. Ad esempio, qui:

"'. $p["title"] . '"

L’ordine corretto delle virgolette è: doppia, singola, punto, variabile, punto, singola, doppia.

Per ora questo esempio basta. Creiamo ora un paio di record nella tabella sostituendo il codice del metodo write() con quello sopra.

Query di selezione – SELECT

Dopo aver inserito alcune righe, possiamo verificarle in phpMyAdmin:

Ora possiamo mostrare questi record tramite il metodo display_public(), che modificheremo. Per selezionare i dati da una tabella useremo l’operatore SELECT. Ecco un semplice esempio:

  public function display_public() { // metodo per visualizzare i messaggi
    $content = '';

	$sql = 'SELECT * FROM Messages'; // query di selezione
	$result = mysql_query($sql);  // salviamo il risultato della query
	
	while($row = mysql_fetch_array($result)){ // elaboriamo il risultato con mysql_fetch_array()
	  print '<div class="post">'; // div contenitore del messaggio
	  print '<span class="time">#' . $row['mid'] . ' del ' . date('d-m-Y', $row['created']) . '</span><h2>' . $row['title'] . '</h2>'; 	// visualizziamo data e titolo
	  print '<p>' . $row['bodytext'] . '</p>'; // testo del messaggio
	  print '</div>'; // fine del div
	}

	$content .= '<p><a href="/index.php?admin=1">Aggiungi messaggio</a></p>';
    return $content;
  }

Ho già sostituito completamente il metodo display_public(): ora i messaggi vengono visualizzati dal database. Analizziamolo. La query di selezione inizia con SELECT, seguito dal simbolo * (asterisco), che indica “tutti i campi”. Se avessimo scritto:

$sql = 'SELECT mid,title FROM Messages'; // query di selezione
$result = mysql_query($sql);

avremmo selezionato solo mid e title, ignorando bodytext e created.

Dopo * scriviamo FROM, che specifica la tabella da cui vogliamo prelevare i dati.

Il risultato della funzione mysql_query() per la selezione deve essere salvato in una variabile (qui $result), perché sarà poi elaborato dalla funzione mysql_fetch_array().

Immagina di aver ottenuto 3 record: la funzione mysql_fetch_array() restituisce un array per ogni riga, una alla volta. Al primo ciclo otteniamo la prima riga, al secondo la seconda e così via. Quando non ci sono più righe, la funzione restituisce false. Per questo usiamo il ciclo while, che continua a eseguire le stesse operazioni finché la condizione è vera. In questo modo elaboriamo ogni riga della tabella.

Potrebbe sembrare complicato la prima volta, ma con la pratica scrivere query SQL diventa naturale. Gli operatori SQL non sono molti, quindi si imparano in fretta.

Ordinamento dei risultati – ORDER BY

Possiamo anche specificare l’ordine dei risultati con ORDER BY. Per impostazione predefinita, l’ordinamento è crescente. Ad esempio:

$sql = 'SELECT * FROM Messages ORDER BY mid';

Così ordineremo i risultati in base a mid in ordine crescente. Per ottenere l’ordine decrescente, aggiungiamo l’operatore DESC:

$sql = 'SELECT * FROM Messages ORDER BY mid DESC';

Come vedi, è semplice. Per l’ordinamento crescente possiamo usare ASC (anche se è il valore predefinito):

$sql = 'SELECT * FROM Messages ORDER BY mid ASC';

Filtrare i risultati – WHERE

Oltre all’ordinamento, possiamo filtrare i risultati con WHERE:

$sql = 'SELECT * FROM Messages WHERE mid<3 ORDER BY mid DESC';

Così mostreremo solo i primi due record (con mid=1 e mid=2), se esistono. Possiamo anche usare gli operatori AND (e) e OR (o) per combinare più condizioni. Con AND entrambe devono essere vere, con OR basta che lo sia una sola.

$limit_time = time() - 3600; // un’ora fa
$sql = 'SELECT * FROM Messages WHERE mid<3 AND created>' . $limit_time . ' ORDER BY mid DESC'; // query di selezione

In questo modo aggiungiamo anche la condizione che il record sia stato creato non più di un’ora fa. Penso che per ora basti con le query SELECT, quindi passiamo alla prossima lezione.