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());
}
?>

Related Posts:

  • Login PHP con password_hash()Primero una aclaración…como no guardar contraseñas No guardar contraseñas en texto plano, esto debería ser obvio ya que si alguien tiene acceso a tu base de datos y a las contraseñas de tus usuarios, si un ataque externo…o in… Read More
  • ob_start PHPAl programar en PHP primero se realiza un procesamiento de la página que se muestra en el ordenador del usuario, luego se envía la petición al servidor y por último se envía al ordenador del usuario el resultado de procesar e… Read More
  • Session Fixation PHPSession fixation Session fixation (fijación de sesión) es un método de Session hijacking (robo de sesión) un poco especial, ya que, si normalmente en el robo de sesión se intenta conseguir el identificador de sesión de un us… Read More
  • Patrón Dependency InjectionRobert C. Martin afirma en el Principio de Inyección de Dependencias: A. Las clases de alto nivel no deberían depender de las clases de bajo nivel. Ambas deberían depender de las abstracciones. B. Las abstracciones no deber… Read More
  • Login PHP con crypt() y BlowfishEn esta entrada se utilizara la función crypt() y Blowfish, otra opción muy aceptable es utilizar password_hash(), dejo este link con una explicación del tema en Login PHP con password_hash(), pasando a la función crypt() co… Read More