Mysqli: uso delle stored procedure

A partire dalla versione 5 MySQL permette l'uso delle stored procedure. Per lavorare con queste tramite mysqli dobbiamo creare innanzitutto una stored procedure da utilizzare, definiremo quindi una semplice procedura che conta il numero dei record presenti nella tabella users, questo ci permetterà di gestire l'output della procedura stessa.

Da phpMyAdmin, una volta connessi al database, creeremo la procedura tramite il pannello "sql":

CREATE PROCEDURE contaUsers (OUT param INT) SELECT COUNT(id) FROM users

Per testarne il corretto funzionamento useremo la seguente istruzione:

CALL contaUsers (@a)

A questo punto possiamo procedere iniziando dalla solita connessione al database:

$mysqli = new mysqli('localhost', 'root', '', 'corso_2016');
if ($mysqli->connect_error) {
    die('Errore di connessione (' . $mysqli->connect_errno . ') '. $mysqli->connect_error);
}

Prepariamo la query con il metodo visto nelle lezioni precedenti:

$query="CALL contaUsers(@a) ";
$stmt = $mysqli->prepare($query);

In questo caso al posto delle solite istruzioni SQL abbiamo la chiamata alla procedura CALL, il nome della procedura da chiamare, contaUsers, e gli eventuali parametri da passare, nel nostro caso solamente quello di output.

A questo punto eseguiamo la query

$stmt->execute();

e poi procediamo con il recupero del parametro di output; useremo quindi il metodo bind_result in modo da avere la variabile da usare.

$stmt->bind_result($output_data);

Attenzione, si tratta di un bind, quindi la variabile output_data non è ancora valorizzata e utilizzabile, lo sarà solo dopo il fetch:

$stmt->fetch();

A questo punto possiamo chiudere la connessione e quindi stampare la variabile nella nostra pagina.

$stmt->close();
echo $output_data;

Procedura con input:

Creiamo una seconda procedura per vedere come mandare parametri in input. La procedura si occuperà di fare l'inserimento dello username nella tabella degli users.

CREATE PROCEDURE 'miaInsert'(IN 'username' VARCHAR(50) CHARSET utf8) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER insert into users(username) values(username)

La differenza fra questa procedura e la precedente sta nel fatto che mentre la prima prevedeva un parametro di output questa prevede un parametro di input. Come visto in precedenza prepariamo la variabile contenente la query e lo username da inserire:

$username='Pippo';
$query="CALL miaInsert(?) ";
$stmt = $mysqli->prepare($query);

Come sappiamo il punti interrogativo sta per il parametro che andremo ad associare prima dell'esecuzione:

$stmt->bind_param("s", $username);
$stmt->execute();

In questo modo abbiamo utilizzato una stored procedure per l'inserimento dei dati nel nostro database.