L'estensione mysqli - II

28 November 2005

In questa seconda parte riprenderemo alcune tecniche già esposte per approfondire diversi aspetti, in particolare vedremo come inviare a MySQL alcune opzioni per la connessione, analizzeremo inoltre la differenza tra query buffered ed unbuffered e vedremo come inviare query multiple. Infine elencheremo altre funzioni di uso comune.

Con il primo articolo della serie dedicata all’estensione ext/mysqli abbiamo iniziato ad esplorare il nuovo set di funzioni ed oggetti messi a disposizione per l’interazione con MySQL 4.1 e versioni successive. Abbiamo visto come effettuare la connessione al database, come inviare una query e come estrarre eventuali risultati, usando sia il classico stile procedurale sia il nuovo approccio ad oggetti.

Gli esempi che proporremo questa volta utilizzeranno solo l’approccio orientato agli oggetti offerto da ext/mysqli, sia perché questo risulta particolarmente diverso dallo stile procedurale della vecchia estensione ext/mysql, sia perché permette agli sviluppatori più esperti di poter estendere le classi per ottenere strutture più complesse.

Inoltre continueremo a far riferimento alla tabella ed ai dati in essa inseriti nel precedente articolo.

Opzioni di connessione

A volte può essere necessario inviare al server MySQL alcune opzioni per la connessione. In tal caso non è possibile istanziare l’oggetto mysqli con l’operazione new mysqli(...) poiché questa operazione determina anche l’effettiva connessione al server. Per l’invio di opzioni che devono essere impostate prima che la connessione sia instaurata deve essere seguito un altro procedimento:

richiamare la funzione mysqli_init() che restituisce un oggetto mysqli non ancora connesso al server MySQL
usare il metodo options() dell’oggetto mysqli per impostare ciascuna opzione di connessione
richiamare il metodo real_connect() dell’oggetto mysqli, impostando eventualmente ulteriori opzioni, per effettuare la vera connessione a MySQL

Vediamo un esempio tratto dalla documentazione ufficiale:

<?php 
/* crea un oggetto connessione non ancora connesso */
$mysqli = mysqli_init();

/* imposto le opzioni di connessione */
$mysqli->options(MYSQLI_INIT_COMMAND, "SET AUTOCOMMIT=0");
$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5);

/* adesso effettuo la vera connessione al server */
$mysqli->real_connect('localhost', 'root', 'password_db', 'test');

/* controllo l'esito della connessione */
if (mysqli_connect_errno()) {
   printf("Connessione fallita: %s\n", mysqli_connect_error());
   exit();
}

printf ("Connessione: %s\n.", $mysqli->host_info);

$mysqli->close();
?>

Nella seguente tabella sono elencate le diverse opzioni che possono essere impostate attraverso il metodo options():

Name	Descrizione
MYSQLI_OPT_CONNECT_TIMEOUT	Imposta la scadenza della connessione espressa in secondi
MYSQLI_OPT_LOCAL_INFILE	Abilita e disabilita l’uso del comando LOAD LOCAL INFILE
MYSQLI_INIT_CMD	Specifica un comando da eseguire subito dopo la connessione al server MySQL
MYSQLI_READ_DEFAULT_FILE	Specifica il nome del file che contiene le impostazioni, da usare al posto di my.cnf
MYSQLI_READ_DEFAULT_GROUP	Legge le opzioni del gruppo specificato presente nel file my.cnf oppure nel file specificato con MYSQL_READ_DEFAULT_FILE.

Come già anticipato, anche il metodo real_connect() accetta diversi opzioni di connessione, da passare come ultimo parametro. Ecco i flag possibili:

Nome	Descrizione
MYSQLI_CLIENT_COMPRESS	Imposta l’utilizzo di un protocollo di compressione per la comunicazione
MYSQLI_CLIENT_FOUND_ROWS	Impone a MySQL di fornire il numero di righe che soddisfano le condizioni della clausola WHERE di una query e non il numero delle sole righe realmente interessate (modificate) dalla query. Questo numero viene restituito con il metodo affected_rows
MYSQLI_CLIENT_IGNORE_SPACE	Permette la presenza di spazi dopo il nome delle funzioni. Rende i nomi delle funzioni termini riservati.
MYSQLI_CLIENT_INTERACTIVE	Specifica i secondi di inattività secondo un interactive_timeout (al posto di un wait_timeout) prima che la connessione venga chiusa
MYSQLI_CLIENT_SSL	Abilita l’uso di una connessione criptata con il server via SSL

Query buffered ed unbuffered

Quando effettuiamo una query che prevede la restituzione di un risultato, ad esempio quando lanciamo una SELECT, dobbiamo tener presente che tale operazione coinvolge più attori: il client, ovvero il motore PHP che effettua la richiesta, il server, ovvero MySQL che riceve la richiesta ed i dati che viaggiano dal server al client.

Il passaggio dei dati tra MySQL e PHP può avvenire in 2 modi distinti: buffered ed unbuffered

Una query buffered prevede le seguenti operazioni:

PHP attraverso l’estensione ext/mysqli invia la query al server MySQL
MySQL esegue la query ed invia il set completo dei risultati a PHP
PHP conserva i risultati in memoria e li rende accessibili allo script attraverso le operazioni di fetch

Una query unbuffered invece funziona nel seguente modo:

PHP attraverso l’estensione ext/mysqli invia la query al server MySQL
MySQL esegue la query e conserva il set dei risultati
PHP accede ai risultati conservati sul server MySQL con le istruzioni di fetch

Risulta evidente quindi che nel caso delle query buffered i risultati risiedono nella memoria di PHP mentre nel caso delle query unbuffered i dati rimangono sul server. A questo punto verrebbe da chiedersi come mai PHP implementa entrambe le tecniche. La risposta risiede nel fatto che nessuna delle due è a priori migliore dell’altra e che anzi ciascuna ha pregi e difetti e pertanto il loro impiego è indicato in precise situazioni.

Con le query buffered i dati vengono conservati da PHP che ha quindi la possibilità di poterli manipolare più agevolmente. E' infatti possibile utilizzare il metodo data_seek() della classe mysqli_stmt per spostarsi liberamente all’interno dei risultati. Inoltre è possibile accedere alla varabile num_rows della classe mysqli per sapere subito quante righe sono state restituite dall’ultima query. Liberando immediatamente MySQL, le query buffered permettono al server di sbloccare subito i dati coinvolti dalla query in modo che altri processi possano accedervi.

L’inconveniente di questo tipo di approccio è l’occupazione di memoria, a volte considerevole, necessaria alla conservazione dei risultati da parte di PHP. Inoltre lo script non può procedere nella sua esecuzione fino a quando tutti i dati non hanno raggiunto il motore PHP, quindi il primo risultato è disponibile solo quando tutti i risultati non sono stati memorizzati dal PHP.

Per contro le query unbuffered prevedono la conservazione dei dati sul server MySQL. Questi sono accessibili al PHP esclusivamente in maniera sequenziale ma sono disponibili non appena MySQL inizia a restituirli. Il beneficio di non occupare la memoria di PHP deve essere pagato però con un altro inconveniente: poiché l’interazione tra PHP e MySQL non si conclude con l’invio della query, prima che ne venga eseguita una nuova è necessario prelevare tutti i risultati (fetch) della precedente oppure chiudere il set di risultati (free_result).

Chiariti i pro ed i contro di ciascun approccio sarà semplice decidere di volta in volta quale tecnica utilizzare. Riassumendo, le query buffered consentono una maggiore elasticità nell’accesso ai risultati e permettono di effettuare nuove query anche se si sta ancora lavorando su risultati precedenti. Al contrario le query unbuffered garantiscono un accesso più immediato al set di risultati e permettono un risparmio in termini di allocazione di memoria da parte di PHP proporzionale alla dimensione dei risultati.

Per effettuare query buffered ed unbuffered ci sono due alternative: utilizzare il metodo real_query() della classe mysqli insieme ai metodi store_result() e use_result() sempre della classe mysqli, oppure usare il metodo query() della classe mysqli, già incontrato nel precedente articolo, a cui passare un ulteriore parametro (MYSQLI_USE_RESULT o MYSQLI_STORE_RESULT) che specifica il tipo di query.

Di seguito riportiamo due esempi, ciascuno esegue la stessa query sia in modalità buffered che unbuffered. Il primo usa i metodi store_result() e use_result() mentre il secondo impiega il metodo query() insieme ad i parametri MYSQLI_USE_RESULT o MYSQLI_STORE_RESULT

<?php 
// provo a connettermi
$mysqli = new mysqli('localhost', 'root', 'password_db', 'test');

echo "- Eseguo una query unbuffered -<br /><br />\n";

// lancio la query
$mysqli->real_query('SELECT user_id, nome, cognome FROM mia_tabella');

// dichiaro di voler utilizzare i risultati in modo unbuffered
$result = $mysqli->use_result();

// provo ad leggere il numero di righe restituite
// otterrò un risultato errato
echo 'Risultati ottenuti: ' , $result->num_rows , " (errato!)<br />\n";

// eseguo la fetch dei risultati
while ($row = $result->fetch_row())
{
   printf ("ID utente:%d, nome:%s, cognome:%s<br />\n",
   $row[0], $row[1],$row[2]);
}
echo 'Risultati ottenuti: ' , $result->num_rows , " (corretto)<br /><br />\n";

// --------------------------------------------------------

echo "- Eseguo una query buffered -<br /><br />\n";

// lancio la query
$mysqli->real_query('SELECT user_id, nome, cognome FROM mia_tabella');

// dichiaro di voler utilizzare i risultati in modo buffered
$result = $mysqli->store_result();

// provo ad leggere il numero di righe restituite
// otterrò un risultato errato
echo 'Risultati ottenuti: ' , $result->num_rows , " (corretto)<br />\n";

// eseguo la fetch dei risultati
while ($row = $result->fetch_row())
{
   printf ("ID utente:%d, nome:%s, cognome:%s<br />\n",
   $row[0], $row[1],$row[2]);
}
?>

Adesso vediamo l’uso dei paramentri MYSQLI_USE_RESULT e MYSQLI_STORE_RESULT

<?php 
// provo a connettermi
$mysqli = new mysqli('localhost', 'root', 'password_db', 'test');

echo "- Eseguo una query unbuffered -<br /><br />\n";

// lancio la query unbuffered
$result = $mysqli->query('SELECT user_id, nome, cognome FROM mia_tabella',MYSQLI_USE_RESULT);

// provo ad leggere il numero di righe restituite
// otterrò un risultato errato
echo 'Risultati ottenuti: ' , $result->num_rows , " (errato!)<br />\n";

// eseguo la fetch dei risultati
while ($row = $result->fetch_row())
{
   printf ("ID utente:%d, nome:%s, cognome:%s<br />\n",
   $row[0], $row[1],$row[2]);
}
echo 'Risultati ottenuti: ' , $result->num_rows , " (corretto)<br /><br />\n";

// --------------------------------------------------------

echo "- Eseguo una query buffered -<br /><br />\n";

// lancio la query unbuffered
$result = $mysqli->query('SELECT user_id, nome, cognome FROM mia_tabella',MYSQLI_STORE_RESULT);

// provo ad leggere il numero di righe restituite
// otterrò un risultato errato
echo 'Risultati ottenuti: ' , $result->num_rows , " (corretto)<br />\n";

// eseguo la fetch dei risultati
while ($row = $result->fetch_row())
{
   printf ("ID utente:%d, nome:%s, cognome:%s<br />\n",
   $row[0], $row[1],$row[2]);
}
?>

Nel caso al metodo query() non venga passato alcun parametro, le impostazioni di default prevedono che la query sia buffered. Si noti infine come nel caso di query unbuffered la variabile num_rows restituisce valori errati se chiamato prima della conclusione della fase di fetch.

Query multiple

Una tra le più interessanti novità introdotte con l’estensione ext/mysqli è sicuramente la possibilità di inviare al server MySQL più query con un’unica chiamata di funzione. Basterà infatti invocare il metodo multi_query() della classe mysqli e passargli una stringa contenente le diverse query separate da un punto e virgola.

Risulta però più complessa l’estrazione dei risultati provenienti dalle singole query inviate. Nell’esempio che segue vedremo come risolvere il problema. Verranno eseguite due query e lanciate con un’unica chiamata al metodo multi_query(). Per recuperare il primo set di risultati useremo il metodo store_result() (query buffered) seguito da una normale fetch. Dopo aver chiuso il set di risultati corrente (close()) si passerà a quello successivo attraverso il metodo next_result() e l’intera procedura verrà ripetuta.

<?php 
// provo a connettermi al server MySQL
$mysqli = new mysqli('localhost', 'root', 'password_db', 'test');

// preparo le query da inviare al server MySQL
$query = "SELECT COUNT(*) FROM mia_tabella;";
$query .= "SELECT * FROM mia_tabella";

// eseguo le query
$mysqli->multi_query($query);

// recupero il primo set di risultati (metodo buffered)
$result = $mysqli->store_result();

// estraggo i risultati
$row = $result->fetch_row();
printf("Numero utenti: %s<br /><br />\n", $row[0]);


// chiudo il primo set di risultati
$result->close();

// passo al secondo set di risultati
$mysqli->next_result();

// recupero il secondo set di risultati (metodo buffered)
$result = $mysqli->store_result();

// estraggo i risultati
echo "Elenco utenti:<br />\n";
while ($row = $result->fetch_assoc())
{
   printf ("ID utente:%d, nome:%s, cognome:%s\n<br />",
   $row['user_id'], $row['nome'],$row['cognome']);
}

// chiudo il secondo set di risultati
$result->close();

// chiudo la connessione al server MySQL
$mysqli->close();
?>

E' importante osservare che l’invio di query multiple al server MySQL espone lo script a potenziali rischi di sicurezza. Infatti se una query contiene dati inviati dall’utente, questa potrebbe venir abilmente manipolata. Si pensi ad esempio alle query:

SELECT user_id FROM utenti WHERE nome_utente = ‘$_POST[nome_utente]'; SELECT COUNT(*) FROM UTENTI

se $_POST[nome_utente] vale

test’; DELETE * FROM utenti; SELECT '

la query diventa:

SELECT user_id FROM utenti WHERE nome_utente = ‘test’; DELETE * FROM utenti; SELECT ' ‘; SELECT COUNT(*) FROM UTENTI

Siamo riusciti a cancellare tutti i dati dalla tabella utenti!

Altre funzioni di ext/mysqli

Prima di concludere questo articolo elenchiamo alcune funzioni messe a disposizione dall’estensione ext/mysqli che risultano estremamente utili e di uso comune. La maggior parte di queste comunque era già disponibile con la vecchia estensione ext/mysql ma risulta comunque interessante analizzare il loro utilizzo in un approccio orientato agli oggetti.

mysqli::affected_rows (mysqli_affected_rows)

Restituisce il numero di righe interessate dall’ultima query. affected_rows va usato solo quando la query modifica il contenuto del database, come nel caso di INSERT, UPDATE e DELETE e non deve essere impiegato con le SELECT. Nel caso di una query di tipo UPDATE affected_rows restituisce il numero di righe realmente modificate e non il numero di righe che rispondono alla clausola WHERE della query stessa. Questo comportamento può essere modificato agendo sui parametri di connessione come mostrato all’inizio di questo articolo.

<?php 
// provo a connettermi al server MySQL
$mysqli = new mysqli('localhost', 'root', 'password_db', 'test');

// preparo le query di inserimento
$query = "INSERT INTO mia_tabella SET nome = 'Gianni', cognome = 'Rossi'";

// eseguo le query
$mysqli->multi_query($query);

// mostro il numero delle righe interessate dall'ultima query
echo 'Numero di righe inserite: ', $mysqli->affected_rows , '<br />';

// preparo le query di cancellazione
$query = "DELETE FROM mia_tabella WHERE nome = 'Gianni'";

// eseguo le query
$mysqli->multi_query($query);

// mostro il numero delle righe interessate dall'ultima query
echo 'Numero di righe cancellate: ', $mysqli->affected_rows;

// chiudo la connessione al server MySQL
$mysqli->close();
?>

mysqli_connect_error e mysqli_connect_errno

Forniscono rispettivamente un messaggio di errore ed un relativo codice numerico associato nel caso in cui la connessione al server MySQL non abbia avuto esito positivo. In particolare mysqli_connect_errno() può essere impiegato per verificare l’esito della connessione semplicemente con un IF.

<?php 
// provo a connettermi al server MySQL
$mysqli = new mysqli('localhost', 'root', 'password_db', 'test');

// Verifico l'esito della connessione
if (mysqli_connect_errno())
{
   printf("Connessione fallita: %s\n", mysqli_connect_error());
   exit();
}
?>

mysqli_result::data_seek (mysqli_data_seek)

Nel caso di query buffered permette di posizionarsi su una qualsiasi delle righe restituite dalla query

<?php 
// provo a connettermi al server MySQL
$mysqli = new mysqli('localhost', 'root', 'password_db', 'test');

// preparo le query da inviare al server MySQL
$query = "SELECT * FROM mia_tabella";

// eseguo le query
$result = $mysqli->query($query);

// sposto il puntatore dei risultati alla terza riga
$result->data_seek(2);

// estraggo la riga
$row = $result->fetch_row();

// mostro i risultati
printf ("ID utente:%d, nome:%s, cognome:%s<br />\n",$row[0],$row[1],$row[2]);

// chiudo la connessione al server MySQL
$mysqli->close();
?>

mysqli::error e mysqli::errno (mysqli_errno e mysqli_error)

Forniscono rispettivamente un messaggio di errore ed un relativo codice numerico associati all’ultimo errore verificatosi nell’interazione con il database MySQL. Utili per effettuare il debug delle query

<?php 
// provo a connettermi al server MySQL
$mysqli = new mysqli('localhost', 'root', 'password_db', 'test');

// eseguo le query
if(!$mysqli->query("SELECT * FROM tabella_inesistente"))
{
   printf('Errore nella query (codice %d): %s', $mysqli->errno,$mysqli->error);
}
?>

mysqli_result::field_count ( mysqli_field_count)

Permette di sapere quanti campi (colonne) contiene un set di risultati. Particolarmente utile quando viene eseguito il metodo store_result() e non è noto a priori se la query restituirà dei risultati (es. una SELECT) o meno (INSER, UPDATE, …)

<form id="form1" name="form1" method="post" action=""> 
<label>Inserisci una query: 
<input name="query" type="text" size="50" /> 
</label> 
<input type="submit" name="Submit" value="&lt;- Esegui" /> 
</form> 
<?php 
if($_POST)
{
   // provo a connettermi al server MySQL
   $mysqli = new mysqli('localhost', 'root', 'password_db', 'test');

   // eseguo le query
   $result = $mysqli->real_query($_POST['query']);

   // se la query ha prodotto un risultato
   if($mysqli->field_count)
   {
       $result = $mysqli->store_result();

       while ($row = $result->fetch_assoc())
       {
           foreach ($row as $key => $value)
           {
               echo "$key=>$value ";
           }
           echo '<br />';
       }
   }
}
?>

mysqli::insert_id (mysqli_insert_id)

Fornisce l’ultimo ID autogenerato da MySQL per un campo auto_increment.

<?php 
// provo a connettermi al server MySQL
$mysqli = new mysqli('localhost', 'root', 'password_db', 'test');

// eseguo le query
$result = $mysqli->query("INSERT INTO mia_tabella SET nome='Gigi', cognome='Cremeria'");

echo "L'ID del nuovo utente inserito è ", $mysqli->insert_id;
?>

mysqli::more_results ( mysqli_more_results)

Nel caso di query multiple restituisce true se sono presenti ulteriori set di risultati provenienti da una precedente chiamata alla funzione multi_query(). Utile quando non si conosce a priori il numero delle query eseguite con una multi_query() e pertanto non è noto il numero di set di risultati da elaborare.

Nel prossimo articolo, che concluderà la serie dedicata a ext/mysqli, vedremo come sfruttare i prepared statements per ottenere il binding dei parametri e dei risultati, come effettuare le transazioni e come gestire i BLOB.