PHP PDO y Procedimientos Almacenados MySQL


Fuente: http://pabletoreto.blogspot.ch/2015/04/php-y-procedimientos-almacenados.html

 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,
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<?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());
}
?>


Recuperación de varios conjuntos de resultados de un procedimiento almacenado en PHP (PDO)

Cuando una única llamada a un procedimiento almacenado devuelve más de un conjunto de resultados, puede utilizar el método PDOStatement::nextRow de la API de PDO para recuperar los conjuntos de resultados.

Antes de empezar

Debe tener un objeto PDOStatement que haya devuelto la llamada a un procedimiento almacenado con el método PDO::query o PDOStatement::execute.

Procedimiento

Para recuperar varios conjuntos de resultados:
  1. Capte filas del primer conjunto de resultados devuelto por el procedimiento mediante la llamada a uno de los métodos de captación de PDO siguientes. (El primer conjunto de resultados que se devuelve del procedimiento está asociado con el objeto PDOStatement que devuelve la sentencia CALL).
    • Para devolver una única fila de un conjunto de resultados como una matriz u objeto, llame al método PDOStatement::fetch.
    • Para devolver todas las filas del conjunto de resultados como una matriz de matrices u objetos, llame al método PDOStatement::fetchAll.
    Capte filas desde el objeto PDOStatement hasta que no haya disponibles más filas en el primer conjunto de resultados.
  2. Recupere los conjuntos de resultados siguientes llamando al método PDOStatement::nextRowset para devolver el conjunto de resultados siguiente. Puede captar filas desde el objeto PDOStatement hasta que no haya disponibles más filas en el conjunto de resultados.
    El método PDOStatement::nextRowset devuelve False cuando no hay más conjuntos de resultados disponibles o cuando el procedimiento no ha devuelto ningún conjunto de resultados.
    Para obtener más información sobre la API de PDO, consulte http://php.net/manual/en/book.pdo.php.

Ejemplo

Recuperar varios conjuntos de resultados de un procedimiento almacenado.
$sql = 'CALL multiple_results()';
$stmt = $conn->query($sql);
do {
   $rows = $stmt->fetchAll(PDO::FETCH_NUM);
   if ($rows) {
       print_r($rows);
   }
} while ($stmt->nextRowset());
Un ejemplo practico seria como el siguiente:



<?php $host="localhost"; $dbname="ejemplo"; $username="root"; $password="1234"; try { $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password); $sql = 'CALL select3()'; $stmt = $conn->query($sql); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); foreach($rows as $data){ echo $data[descripcion]."<br>"; echo $data[nombre]."<br>"; echo $data[precio]."<br>"; } $stmt->nextRowset(); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); foreach($rows as $data){ echo $data[nombre]."<br>"; } } catch (PDOException $pe) { die("Error occurred:" . $pe->getMessage()); } ?>
?

Comentarios

Publicar un comentario