15 de abril de 2015

PHP PDO y Procedimientos Almacenados MySQL

Se trataran diferentes escenarios y en toda la entrada se trabajara la sentencia SELECT en un procedimiento almacenado en MySQL que variara según el numero y el tipo de parámetros que acepte o que entregara. La tabla a utilizarse tiene por nombre empleado_php la cual tiene solamente dos campos varchar: ID(PK 8) y Nombre(50).

  • Procedimiento Almacenado sin Parámetros
Este procedimiento solamente recupera con las sentencia select * from empleado_php todos los registros de la tabla, no aplica ningún filtro ni recibe o entrega ningún tipo de parámetros
DELIMITER $$
USE `ejemplo`$$
DROP PROCEDURE IF EXISTS `select_sp`$$

CREATE DEFINER=`root`@`localhost`
PROCEDURE `select_sp`()
BEGIN
    SELECT * FROM empleado_php;

END$$
DELIMITER ;
Ahora el codigo php que se utilizara para invocar el procedimiento almacenado y en este mismo se mostraran los datos que devuelve el procedimiento almacenado.
<?php
     
  $host="localhost";
  $dbname="ejemplo";
  $username="root";
  $password="";
     
  try {
   $conn = new PDO("mysql:host=$host;dbname=$dbname",
                            $username, $password);
   $sql = 'CALL select_sp()';
   $q = $conn->query($sql);
   $q->setFetchMode(PDO::FETCH_ASSOC); }
  catch (PDOException $pe) {
   die("Error occurred:" . $pe->getMessage());
        }
        
  while ($r = $q->fetch()):      
     echo "ID del empleado:  "  .$r['ID'];
     echo "Nombre:  ".  $r['Nombre']; 
     endwhile;
?>

  • Procedimiento Almacenado con Parámetro IN
Este procedimiento almacenado recupera el registro de un empleado de acuerdo a su ID que funcionara como criterio de busqueda, este ID es recibido en el procedimiento almacenado como parametro de entrada
DELIMITER $$
USE `ejemplo`$$
DROP PROCEDURE IF EXISTS `selectOne_sp`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `selectOne_sp`(
IN id_val VARCHAR(8)
)
BEGIN
    SELECT * FROM empleado_php WHERE ID=id_val;

END$$
DELIMITER ;
Ahora el código php que se utilizara para invocar el procedimiento almacenado, desde este código se envía el parámetro que servirá como criterio de búsqueda y en este mismo se mostraran los datos que devuelve el procedimiento almacenado.
<?php
     $host="localhost";
     $dbname="ejemplo";
     $username="root";
     $password="";
     $idEmpleado = "PHP001";
        
 try {
   $conn = new PDO("mysql:host=$host;dbname=$dbname",
                            $username, $password);

   $sql = 'CALL selectOne_sp(:id)';
   $stmt = $conn->prepare($sql);
   $stmt->bindParam(':id', $idEmpleado, PDO::PARAM_STR, 100);
   $stmt->execute();
   $stmt->setFetchMode(PDO::FETCH_ASSOC);
   $stmt->setFetchMode(PDO::FETCH_ASSOC);
   $num= $stmt->rowCount();
   //while ($r = $stmt->fetch(PDO::FETCH_ASSOC)): 
          
   if($num>0){
    while ($r = $stmt->fetch()):      
     echo "ID del empleado:  "  .$r['ID'];
     echo "Nombre:  ".  $r['Nombre']; 
     //echo '$' . number_format($r['creditlimit'],2) 
       endwhile; }
   else{
  echo "No se encontraron registros con el ID " .$idEmpleado;
       }
 }
 
catch (PDOException $pe) {
            die("Error occurred:" . $pe->getMessage());
        }
?>

  • Procedimiento Almacenado con Parámetro OUT
Este procedimiento almacenado no recibe ningún parámetro de entrada y controla un parámetro de salida que entrega el numero total de registros de la tabla empleado_php
DELIMITER $$
USE `ejemplo`$$
DROP PROCEDURE IF EXISTS `selectCount_sp`$$

CREATE DEFINER=`root`@`localhost`
PROCEDURE `selectCount_sp`(
OUT totalEmpleados INT
)
BEGIN  
      SELECT COUNT(Nombre) INTO totalEmpleados FROM empleado_php;

END$$
DELIMITER ;
Ahora el código php que se utilizara para invocar el procedimiento almacenado, para llamadas a procedimientos almacenados que manejan parámetros de salida deben realizarse dos consultas con la conexión a la base de datos obtenida, en una consulta se hará el CALL al procedimiento almacenado y con la segunda consulta se logra recuperar los parámetros output del procedimiento.
<?php
  $host="localhost";
  $dbname="ejemplo";
  $username="root";
  $password="";
        $idEmpleado = "PHP001";
try {   
 $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
 $sql = 'CALL selectCount_sp(@total)';
    $stmt = $conn->prepare($sql);
    $stmt->execute();
 
    $stmt->closeCursor(); //permite limpiar y ejecutar la segunda query
    
 // este codigo es para recuperar un valor
    $r = $conn->query('select @total'); 
 $total = $r->fetchColumn();
 echo $total;
 
 // este codigo serviria para recuperar mas de un valor output desde un SP.
    //$r = $conn->query('select @total')->fetch();
 //echo $r['@total'];
 
 }
catch (PDOException $pe) {
    die("Error occurred:" . $pe->getMessage());
}
?>

  • Procedimiento Almacenado con Parámetros OUT
Este procedimiento entregara mas de un parametro de salida
DELIMITER $$
USE `ejemplo`$$
DROP PROCEDURE IF EXISTS `selectOuts_sp`$$

CREATE DEFINER=`root`@`localhost`
PROCEDURE `selectOuts_sp`(
OUT totalEmpleados INT,
OUT avgEmpleados VARCHAR(100))

BEGIN  
      SELECT COUNT(Nombre) INTO totalEmpleados FROM empleado_php;
      SELECT Nombre INTO avgEmpleados FROM empleado_php WHERE ID="PHP001";

END$$
DELIMITER ;
La llamada al procedimiento almacenado debe hacerse con dos query, deben realizarse dos consultas con la conexión a la base de datos obtenida, en una consulta se hará el CALL al procedimiento almacenado y con la segunda consulta se logra recuperar los parámetros output del procedimiento.

<?php
  $host="localhost";
  $dbname="ejemplo";
  $username="root";
  $password="";
        $idEmpleado = "PHP001";
try {   
  $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
  $sql = 'CALL selectOuts_sp(@total, @avg)';
  $stmt = $conn->prepare($sql);
  $stmt->execute();
 
  $stmt->closeCursor(); //permite limpiar y ejecutar la segunda query
    
  /* este codigo es para recuperar un valor
  $r = $conn->query('select @total, @avg'); 
  $total = $r->fetchColumn();
  echo $total;*/
 
  // este codigo serviria para recuperar mas de un valor output desde un SP.
  $r = $conn->query('select @total, @avg')->fetch();
  echo $r['@total'];
  echo $r['@avg'];
 
 }
catch (PDOException $pe) {
  die("Error occurred:" . $pe->getMessage());
}
?>

  • Procedimiento Almacenado con Parámetros IN-OUT
Procedimiento tratara los tipos de parámetro de entrada y salida,
DELIMITER $$
USE `ejemplo`$$
DROP PROCEDURE IF EXISTS `get_user`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_user`(
IN userId INT,
OUT firstName VARCHAR(100)
)
BEGIN
SELECT Nombre
INTO firstName
FROM empleado_php
WHERE ID = userId;

END$$
DELIMITER ;
Para utilizar este procedimiento almacenado se deben realizarse dos consultas con la conexión a la base de datos obtenida, en una consulta se hará el CALL al procedimiento almacenado y con la segunda consulta se logra recuperar los parámetros output del procedimiento.

<?php
 $host="localhost";
 $dbname="ejemplo";
 $username="root";
 $password="";
        $idEmpleado = "PHP001";
try {
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $sql = 'CALL get_user(:id,@lnombre)';
    $stmt = $conn->prepare($sql);
    $stmt->bindParam(':id', $idEmpleado, PDO::PARAM_STR, 100);
    $stmt->execute();
 
    $stmt->closeCursor(); //permite limpiar y ejecutar la segunda query
   
    $r = $conn->query("SELECT @lnombre AS nombre")->fetch(PDO::FETCH_ASSOC);
    if ($r['nombre']) {
        echo sprintf('Nombre del empleado %s es %s', $idEmpleado, $r['nombre']);
    }
 else
  echo sprintf('Nombre del empleado %s no esta especificado', $idEmpleado);
 }
catch (PDOException $pe) {
    die("Error occurred:" . $pe->getMessage());
}
?>