Utiliser les procédures stockées MySQL et étendre MySQLi dans PHP

MySQLi vous permet de créer un objet qui représente votre connexion à la base de données MySQL en opposition à la création d'une référence comme dans la vieille fonction procédurale. En créant votre propre classe vous pouvez étendre MySQLi et ajouter des fonctionnalités personnalisées qui correspondent mieux à votre projet.

Le code ci-dessous représente une extension de la classe MySQLi sans ajouter aucune fonction ou variable :

class MySQLDB extends mysqli{ 

} 

$db_connection = new MySQLDB('hostname', 'username', 'password', 'db_name');

Les procédures stockées ont été introduites dans MySQL dès la version 5, mais ont été très peu adoptées par les développeurs LAMP réguliers, car d'une certaine manière elles n'ont toujours pas fait leurs preuves. Utiliser les procédures stockées n'améliore pas seulement la sécurité, mais réduit aussi le trafic entre votre application web et le serveur de base de données. Pour créer une procédure stockée dans MySQL vous devrez exécuter une commande CREATE PROCEDURE comme celle ci-dessous :

CREATE PROCEDURE `stpInsertUser`( 
   first_name VARCHAR(50), 
   last_name VARCHAR(50) 
) 
BEGIN    
   INSERT INTO `tblUsers` 
   (`strFirstName`, `strLastName`) 
   VALUES 
   (first_name, last_name); 
END$$

La commande précédente fait basiquement état qu'il devrait y avoir une procédure nommée stpInsertUser de créée. Cette procédure doit prendre 2 VarChar de taille 50 caractères en tant que paramètres, l'un appelé first_name et l'autre last_name. La requête à exécuter se trouve entre les commandes BEGIN et END. Notez bien les 2 signes dollars ($) après la commande END, c'est un délimiteur personnalisé. Les délimiteurs personnalisés sont utilisés car le délimiteur standard, le point virgule (;) est inclus dans la requête pour être exécuté et si le serveur de base de données exécute jusque la alors il n'exécutera pas la commande CREATE PROCEDURE jusqu'au bout. C'est pourquoi vous devez régler le délimiteur sur les 2 signes dollars avant que la commande CREATE PROCEDURE soit exécutée. Quand vous utilisez PHPMyAdmin vous pouvez le définir quand vous exécutez une requête (vous pouvez le trouver tout en bas de la fenêtre des requêtes juste en dessous du bouton "exécuter ") mais quand vous vous connectez via un autre client MySQL vous devez utiliser la commande suivante :

DELIMITER $$

Rappelez-vous juste en utilisant cette commande de le remettre ensuite.

DELIMITER |

Maintenant que nous avons créé notre procédure stockée nous pouvons l'exécuter. Pour ce faire, nous utilisons la commande CALL en lui passant le nom de la procédure et tous les paramètres nécessaires à celle-ci. Pour appeler la procédure décrite avant nous devons utiliser la commande qui suit :

CALL sdpInsertUser('foo', 'bar');

Si cette requête devait renvoyer des données elle les renverrait de la même manière qu'une requête standard.

Avec une procédure stockée en place, nous allons maintenant étendre MySQLi et lui ajouter une méthode publique nommée executeStoredProcedure. Exécuter une procédure stockée depuis MySQLi n'est autre que simplement appeler la méthode CALL définie précédemment, mais nous allons créer une méthode qui va nous permettre de la personnaliser, l'exécuter et de vérifier les données retournées. Le code ci-dessous est une extension de la classe MySQLi avec une nouvelle méthode publique pour exécuter les procédures stockées. La méthode prend le nom de la procédure en tant que chaîne de caractères et un tableau des paramètres requis pour l'exécution. Elle regroupe les paramètres en une chaîne de caractères pour former la commande CALL et retourner n'importe quelle donnée en tant que tableau d'une ligne.

class MySQLDB extends mysqli { 

   public function storedProcedure($proc_name, $params){ 
      $ds = array(); 
      if ($result = $this->query("CALL $proc_name('" . implode("', '", $params) . "');")) { 
         if ($result->num_rows > 0){ 
            while ($row = $result->fetch_array(MYSQLI_ASSOC)){ 
               $ds[] = $row; 
            } 
            $result->close(); 
         } 
         $this->next_result(); 
      } 
      $this->commit(); 
      return $ds; 
   } 
}

Le code ci dessus peut évidemment être exécuté sans étendre la classe MySQLi, mais il y'a quelques avantages à procéder de cette manière. Pour commencer vous vous assurez que tous les appels que vous faites sont exécutés de la même manière. Notez que les commandes $result->close() et $this->next_result() peuvent souvent être oubliés, cela libère la connexion pour les prochaines requêtes ce qui m'a souvent donné pas mal de peine. Vous pouvez aussi garder les données quand vous les lisez depuis l'objet renvoyé par MySQL dans un format beaucoup plus approprié à vos besoins. Dans le passé j'ai créé un objet DataSet pour diverses raisons, faire ceci me permet de retourner les données d'une requête dans un objet DataSet en opposition à un simple tableau associatif.

Il y'a définitivement quelques avantages à travailler avec les procédures stockées et MySQLi. Ce que j'ai montré ici est juste le début de beaucoup de choses différentes que vous pouvez faire pour améliorer et personnaliser vos applications web LAMP. Les procédures stockées et MySQLi ne se sont pas encore faits une place dans le paysage LAMP mais ils sont des améliorations valables. La prochaine fois que vous développerez une application LAMP, prenez le temps de penser à ce que tout cela peut vous apporter.

Vous pouvez aussi aller voir mes autres traductions.