PHP Operacións BD Mysql

De MediaWiki
Saltar a: navegación, buscar

Contenido

Introdución

  • Como comentamos no punto anterior, para conectarnos a un xestor de base de datos específico imos ter que ter instalados os drivers correspondentes.
Un driver é o software que nos permite comunicarnos cun xestor de base de datos, pero normalmente non imos facer dita comunicación utilizando o driver directamente se non que o usaremos a través dunha API, que son un conxunto de clases, funcións,... que nos permiten facer uso do driver e conectar a un xestor de base de datos.
  • Existen dous tipos de API´s:
  • Procedimentais: Facemos uso de funcións para realizar as operacións contra a base de datos.
  • Orientadas a obxectos: Instanciamos clases e chamamos a métodos dos obxectos creados.

Normalmente faremos uso desta segunda opción.


  • En PHP, ditas API´s se 'activan' facendo uso de Extensións.
Indicar que unha API non é o mesmo que unha extensión en PHP, xa que algunhas extensións non proporcionan ningunha API ao usuario (por exemplo a extensión do driver PDO MySQL, non proporciona ningunha API ó programador PHP).


  • Existen tres APIs principais para conectar a un servidor de bases de datos MySQL:
  • Extensión MySQL de PHP
  • Extensión mysqli de PHP
  • Obxectos de Datos de PHP (PDO)


  • Para conectarnos imos utilizar as funcións mysqli (mysql improved) que foron introducidas a partires de Mysql 4.1
A extensión mysqli ven incluída nas versiones PHP 5 e posteriores.
Algunha das melloras de dita extensión con respecto á extensión mysql:


Dita extensión tamén ten unha versión procedural, de tal forma que imos poder chamar ás funcións de dúas maneiras diferentes:

Exemplo de función cun estilo procedimental que pecha unha conexión a unha base de datos ($link é un identificador cando se fai a conexión contra a base de datos):

  1. mysqli_close($link);

Exemplo de función cun estilo obxectual que pecha unha conexión a unha base de datos:

  1. mysqli::close();


  • Aquí tedes un cadro comparativo obtido de php.net onde se ve o comentado anteriormente:
Php BD 2.jpg


Nota: Máis información neste enlace.

Conexión

  • Partimos da base de que xa temos instalado:
  • O xestor de bases de datos Mysql
  • Unha base de datos creada. Neste manual vai ter de nome PHP. Facer que o 'default collation' sexa utf8_spanish2_ci.
  • Un usuario / password que teña todos os permisos sobre a base de datos creada. Neste manual o usuario/password creado son user_php/user_php.


Todo o anterior xa está indicado no punto anterior da wiki.


  • Sintaxe (se indica a sintaxe do construtor e polo tanto será equivalente a facer un 'new' da clase mysqli):
mysqli::__construct ([ string $host = ini_get("mysqli.default_host") [, string $username = ini_get("mysqli.default_user") [, string $passwd = ini_get("mysqli.default_pw") [, string $dbname = "" [, int $port = ini_get("mysqli.default_port") [, string $socket = ini_get("mysqli.default_socket") ]]]]]] )
Todos eles son optativos, pero normalmente indicaremos o nome de host, login, password e base de datos.
Os parámetros son:
  • Host a conectar (cadea): Pode ser o nome ou a dirección IP. Normalmente imos poñer 'localhost' (se o xestor de bases de datos está instalado no mesmo equipo que o servidor web).
  • Nome do usuario con acceso á base de datos (cadea): Nome do usuario que ten acceso á base de datos.
  • Password (cadea): Password do usuario.
  • Dbname (cadea): Nome da base de datos sobre a que imos traballar.
  • Porto (numérico): Porto no que está escoitando o xestor de base de datos. No caso de mysql ó o 3306.
  • Socket (cadea): Socket ou tubería para conectar có xestor de bases de datos. En Linux é un tipo especial de arquivo que serve para 'transmitir' información, neste caso, cara o xestor de bases de datos.


  • Aplicado ao noso caso, teremos a seguinte orde:
  1. $conex = new mysqli('localhost','user_php','user_php','PHP');


  • En caso de erro, $conex é un obxecto que represente a conexión á base de datos.
  • En caso de ter unha versión PHP anterior á PHP 5.2.9 e 5.3.0 teremos que chamar á función mysqli_connect_error() desta forma:
  1. $conex = new mysqli('localhost','user_php','user_php','PHP');
  2. if (mysqli_connect_error()){
  3.    die('Erro de Conexión (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
  4. }
  • En caso de ter unha versión PHP posterior á PHP 5.2.9 e 5.3.0:
  1. $conex = new mysqli('localhost','user_php','user_php','PHP');
  2. if ($conex->connect_error) {
  3.    die('Erro de Conexión (' . $conex->connect_errno . ') ' . $conex->connect_error);
  4. }
Como vemos amosamos por un lado a mensaxe de erro que devolve o xestor de bases de datos e o número de erro asociado a esa mensaxe.
Podemos consultar os número de erro e mensaxes asociadas neste enlace.


  • Por exemplo, imos modificar o password do usuario por un que non exista e veremos o resultado:
  1. $conex = new mysqli('localhost','user_php','password_non_existe','PHP');
  2. if (mysqli_connect_error()){
  3.    die('Erro de Conexión (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
  4. }
Dará como resultado:
Php BD 3.jpg

Gardando os datos de conexión nun lugar seguro

  • Como é obvio os datos de base de datos, usuario e password son moi importantes e non debemos permitir que alguén poida descubrilos.
  • O que faremos será o seguinte:
  • Definiremos en forma de constantes ditos datos nun arquivo de include.
  • Gardaremos dito arquivo fora do cartafol de acceso do noso sitio web.
  • O primeiro punto, definir en forma de constante e nun arquivo de include os datos da conexión:

Arquivo: bd.inc.php:

  1. <?php
  2.  
  3. define ('SERVIDOR','localhost');
  4. define ('USUARIO','user_php');
  5. define ('PASSWORD','user_php');
  6. define ('BD','PHP');
  • Faremos un require deste arquivo en todas as páxinas que necesiten unha conexión á base de datos. Desta forma, se necesitamos cambiar algo soamente o cambiaríamos nun arquivo.


  • O segundo punto consiste en descubrir cal é a ruta física á que apunta o noso servidor web e colocar o arquivo de include 'fora' desa ruta.
  • Temos neste punto da Wiki información sobre como funciona Apache e como saber a onde apunta o servidor web no seu arquivo de configuración.
  • Temos varias opcións:
Se facemos un echo destas variables dentro dunha páxina do noso sitio web, obteremos unha ruta, coma por exemplo:
  1. /var/www/html/
Nota: Se o facemos coa constante __DIR__ e a páxina que está a facer o echo se atopa dentro dun cartafol, tamén aparecerá o cartafol (por exemplo /var/www/html/cartafol/).


  • Isto quere dicir que o servidor web (e polo tanto calquera usuario de Internet) vai poder acceder as páxinas que se atopan no cartafol /var/www/html e calquera dentro del.
Unha medida de seguridade é colocar o arquivo de include 'fora' desta ruta. Por exemplo, podemos crear un arquivo no cartafol /var/www/confBD
A dito cartafol ten que poder acceder o usuario que utiliza o Apache para acceder aos cartafoles a nivel do S.O. (no caso de Apache soe ser www-data, podedes miralo no punto da wiki indicado anteriormente).


  • Agora, calquera páxina que faga uso destes datos terá que facer o seguinte require:
  1. <?php
  2.  
  3. //error_reporting(0);  // Inabilita os erros de Apache se temos activada a directiva display_erros no php.ini
  4.  
  5.  
  6. require ('/var/www/confBD/bd.inc.php');
  7. $conex = new mysqli(SERVIDOR,USUARIO,PASSWORD,BD);

O xogo de caracteres

  • Debemos ter en conta que unha páxina web está 'aberta' a todo o mundo que se conecte a ela.
Cada persoa en diferentes países terá diferente xogos de caracteres no que cada carácter está relacionado cun código numérico.
  • Se gardamos como dato dentro da base de datos unha cadea cun 'ñ' entre os seus datos, dito carácter estará asociado a un código nun xogo de caracteres. Se o usuario que vai cargar a páxina o carga cun xogo de caracteres diferente non vai poder visualizalo.


  • Cando creamos aplicacións en PHP con acceso a base de datos teremos que ter en conta os seguintes xogos de caracteres:
  • Xogo de caracteres da base de datos (no noso caso Mysql): É o que se coñece como Charset/Collation.
Php BD 7.jpg
Se vos fixades cando creamos a base de datos en Mysql, escollemos o charset (primeira columna) e o collation (segunda columna).
Como indicamos antes, cando creamos a base de datos o charset/collation indicado foi utf8/utf8_spanish2_ci.
O charset é como mysql garda internamente os caracteres e os relaciona cun código numérico e o collation indica como comparar e ordenar o texto. Podedes consultar esta blog para obter máis información.
  • Xogo de caracteres da páxina HTML.
Na páxina web indicamos coa etiqueta <meta> o xogo de caracteres para que o navegador web poida visualizalos correctamente.
No caso que nos ocupa teremos que poñer: <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
Máis información en w3schools.
  • No código PHP ao conectar coa base de datos.
Cando conectemos coa base de datos teremos que indicarlle que imos utilizar o xogo de caracteres utf8 con esta liña:
  1. $conex = new mysqli(SERVIDOR,USUARIO,PASSWORD,BD);
  2. $conex->set_charset('utf8');


  • E FUNDAMENTAL QUE NOS TRES SITIOS ESTEA POSTO O MESMO XOGO DE CARACTERES.

Creando as táboas en Mysql


  • Imos implementar unha base de datos para xestionar a venda de libros de segunda mano.
O modelo EE/R é o seguinte:
Php BD 6.jpg

Nota:

  • A táboa ESTADOS podería eliminarse e crear un campo estado de tipo enum en Mysql se ditos 'estados' van ser sempre os mesmos e non van modificarse...
  • O campo 'imaxe' da táboa EXEMPLARES está nesta táboa e non na táboa LIBROS, xa que a idea é amosar ao comprador unha imaxe do exemplar para amosar o seu estado.


  • O modelo relacional implementado nunha base de datos Mysql é o seguinte:
CREATE TABLE `PHP`.`LIBROS` (
  `id_libro` INT NOT NULL AUTO_INCREMENT,
  `titulo` VARCHAR(300) NOT NULL,
  PRIMARY KEY (`id_libro`));

CREATE TABLE `PHP`.`ESTADOS` (
  `id_estado` INT NOT NULL AUTO_INCREMENT,
  `descripcion` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id_estado`));

 CREATE TABLE `PHP`.`EXEMPLARES` (
  `libro_id` INT NOT NULL,
  `id_exemplar` INT NOT NULL,
  `imaxe` VARCHAR(100) NULL,
  `prezo` DECIMAL(6,2) NOT NULL,
  `estado_id` INT NULL,
  INDEX `fk_EXEMPLARES_LIBROS_idx` (`libro_id` ASC),
  INDEX `fk_EXEMPLARES_ESTADOS_idx` (`estado_id` ASC),
  PRIMARY KEY ( `libro_id`,`id_exemplar`),
  CONSTRAINT `fk_EXEMPLARES_LIBROS`
    FOREIGN KEY (`libro_id`)
    REFERENCES `PHP`.`LIBROS` (`id_libro`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `fk_EXEMPLARES_ESTADOS`
    FOREIGN KEY (`estado_id`)
    REFERENCES `PHP`.`ESTADOS` (`id_estado`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE);

CREATE TABLE `PHP`.`AUTORES` (
  `id_autor` INT NOT NULL AUTO_INCREMENT,
  `apelido` VARCHAR(200) NOT NULL,
  `nome` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id_autor`));


CREATE TABLE `PHP`.`LIBROS_AUTORES` (
  `libro_id` INT NOT NULL,
  `autor_id` INT NOT NULL,
  PRIMARY KEY (`libro_id`, `autor_id`),
  INDEX `fk_LIBROS_AUTORES_AUTORES_idx` (`autor_id` ASC),
  CONSTRAINT `fk_LIBROS_AUTORES_LIBROS`
    FOREIGN KEY (`libro_id`)
    REFERENCES `PHP`.`LIBROS` (`id_libro`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
  CONSTRAINT `fk_LIBROS_AUTORES_AUTORES`
    FOREIGN KEY (`autor_id`)
    REFERENCES `PHP`.`AUTORES` (`id_autor`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE);
  • Para ter algún dato inicial imos a executar estas ordes:
INSERT INTO `ESTADOS` VALUES (1,'NOVO'),(2,'COMO NOVO'),(3,'DETERIODADO'),(4,'CON ANOTACIÓNS');


INSERT INTO `PHP`.`AUTORES` (`apelido`, `nome`) VALUES ('Drumont', 'Eduardo');

INSERT INTO `PHP`.`LIBROS` (`titulo`) VALUES ('El caso Dreyfus, expresión del antisemitismo, temas, Gallica, BNF');
INSERT INTO `PHP`.`LIBROS` (`titulo`) VALUES ('Les amis');

INSERT INTO `PHP`.`EXEMPLARES` (`libro_id`, `id_exemplar`, `imaxe`, `prezo`, `estado_id`) VALUES ('1', '1', 'dreyfus.jpg', '20,52', '2');
INSERT INTO `PHP`.`EXEMPLARES` (`libro_id`, `id_exemplar`, `imaxe`, `prezo`, `estado_id`) VALUES ('1', '2', 'dreyfus.jpg', '15,34', '3');
INSERT INTO `PHP`.`EXEMPLARES` (`libro_id`, `id_exemplar`, `imaxe`, `prezo`, `estado_id`) VALUES ('2', '1', 'amis.jpg', '35,35', '1');
INSERT INTO `PHP`.`EXEMPLARES` (`libro_id`, `id_exemplar`, `imaxe`, `prezo`, `estado_id`) VALUES ('2', '2', 'amis.jpg', '20,25', '4');
INSERT INTO `PHP`.`EXEMPLARES` (`libro_id`, `id_exemplar`, `imaxe`, `prezo`, `estado_id`) VALUES ('2', '3', 'amis.jpg', '25,34', '3');

INSERT INTO `LIBROS_AUTORES` VALUES (1,1),(2,1);


  • Agora faremos o seguinte:
  • Crearemos dentro do noso sitio web (normalmente /var/www/html/) un cartafol de nome IMAXES
  • Copiamos as imaxes que veñen a continuación a dito cartafol.
  • Cambiaremos o propietario coa orde chown -R www-data:www-data IMAXES/

Amis.jpg Nome: amis.jpg Dreyfus.jpg Nome: dreyfus.jpg


Regras de nomeado de táboas e columnas

  • Aínda que non existen 'regras' únicas sobre como nomear as táboas e campos nos imos seguir as seguintes:
  • Nomes de táboas en PLURAL e con MAIÚSCULAS. Desta forma distinguiremos facilmente o nome da táboa e os campos nunha consulta SQL.
  • Nomes de campos en minúscula e singular.
  • Se o campo é a clave primaria terá de nome: id_nometáboa_en_singular
  • Se o campo é unha clave foránea terá de nome: nometáboa_en_singular_id
  • Se o nome de campo está formado por varias palabras, estarán separadas por un guión baixo ( _ )
  • Non usar til nin caracteres 'raros' como espazos en branco, interrogacións,...
  • As claves foráneas irán ao final da lista de columnas da táboa.

Cambiando de base de datos activa

  • Cando creamos a cadea de conexión, un dos parámetros que enviamos foi a base de datos na que iamos traballar.
Podemos non enviar ese parámetro ou querer cambiar de base de datos dentro dunha páxina php.
Se queremos escoller outra diferente teremos que facer uso da función mysqli::select_db.
Por exemplo:
  1. $conex = new mysqli('localhost','user_php','user_php','PHP');
  2. $conex->set_charset('utf8');
  3.  
  4. if (!$conex->select_db('OutraBD')){
  5.      die('Erro ao cambiar de base de datos');
  6. }


Consulta

  • Para conectarnos primeiro teremos que ter a consulta SQL.
  • Indicar que o SQL (Structure Query Language) é a linguaxe utilizada para obter datos dunha base de datos relacional.
Consulta SQL: SELECT col1, col2 FROM TABOA_1


  • Funcións para realizar consultas a base de datos e funcións relacionadas:
  • mysqli::query: Realiza a consulta
  • mysqli_result: En caso de realizar unha consulta de tipo 'select' este obxecto representa o conxunto de resultados obtidos.


  • O proceso para realizar unha consulta é o seguinte:
  • Abrimos a conexión á base de datos.
  • A idea é chamar ao procedemento query enviando como parámetro a consulta.
  1. $result=$conex->query('SELECT col1, col2 FROM TABOA_1');
$result vai ter o conxunto de filas que devolva o resultado. Podemos velo como unha táboa formada polas filas do resultado e polas columnas indicadas no select.
Se a consulta está mal feita (por exemplo, seleccionamos unha táboa que non existe) devolverá false.
Polo tanto, o seguinte será comprobar se temos datos
  1. if ($result && ($result->num_rows>0)){
  2.   // Veñen datos
  3.  
  4. }
  5. else {
  6.   echo 'Non hai datos ou a consulta está mal feita';
  7. }
  • Agora necesitaremos percorrer dende o primeiro rexistro ata o último amosando o resultado.
Isto o faremos cun bucle, chamando á función mysqli_result::fetch_assoc(void).
Debemos entender que, inicialmente, despois de facer a consulta, temos un punteiro que está 'apuntando' xusto antes do primeiro rexistro de resultados. O que fai a función anterior é mover ese punteiro ao seguinte rexistro (a primeira vez apuntará ao primeiro do conxunto de resultados) e devolve nun array a fila á que está 'apuntando'.
Se volvemos a chamar, volve a moverse ao seguinte rexistro e así ata ao final no que devolve false.
  1. if ($result && ($result->num_rows>0)){
  2.   // Veñen datos
  3.    while($row = $result->fetch_assoc()) {
  4.     printf("%s-%s",$row["col1"],$row["col2"]);
  5.    }
  6. }
  7. else {
  8.   echo 'Non hai datos ou a consulta está mal feita';
  9. }


  • Ao rematar de percorrer o resultado, o liberaremos de memoria.
  1. if ($result && ($result->num_rows>0)){
  2.   // Veñen datos
  3.    while($row = $result->fetch_assoc()) {
  4.     printf("%s-%s",$row["col1"],$row["col2"]);
  5.    }
  6.    $result->free();  // Liberamos da memoria os recursos
  7. }
  8. else {
  9.   echo 'Non hai datos ou a consulta está mal feita';
  10. }
  • Pechamos a conexión.
  1. $conex->close();  // Pechamos a conexión



Caso Práctico

Imos crear unha consulta que nos devolva todos os libros da táboa LIBROS:
Para non ter as consultas no medio do código PHP das diferentes páxinas, imos definila no arquivo bd.inc.php creado no punto anterior.
define ('CONSULTAR_LIBROS','select id_libro,titulo from LIBROS order by titulo');


  • Normas nos nomes das constantes:
  • Comenzarán cun verbo que vai poder ser: CONSULTAR, INSERIR, MODIFICAR e ELIMINAR
  • Seguirá cun guión baixo.
  • En caso de consultar os datos dunha única táboa se indicará o nome da táboa. En caso de consultar datos de varias táboas se indicará con varias palabras separadas con guión baixo que indiquen o obxectivo da consulta.



  • Aplicado ao exemplo que estamos a seguir...

Arquivo: Consultar_Libros.php

  1. <?php
  2.  
  3. require ('/var/www/confBD/bd.inc.php');
  4. //error_reporting(0);
  5.  
  6. $conex = new mysqli(SERVIDOR,USUARIO,PASSWORD,BD);
  7. $conex->set_charset('utf8');
  8.  
  9. if ($conex->connect_error){
  10.      die('Erro de Conexi&oacute;n (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
  11. }
  12. ?>
  13.  
  14.  
  15. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  16.     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  17. <html xmlns="http://www.w3.org/1999/xhtml" lang="es" xml:lang="es">
  18.     <head>
  19.         <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  20.         <title>Operacións BD</title>
  21.     </head>
  22.  
  23.     <body>
  24. <?php        
  25.        
  26.         $result=$conex->query(CONSULTAR_LIBROS);
  27.         if ($result->num_rows>0){
  28.             echo "<table border='1'>";
  29.             while($row = $result->fetch_assoc()) {
  30.                 echo '<tr>';
  31.                 printf("<td>%s</td>",$row["titulo"]);
  32.                 echo '</tr>';
  33.             }
  34.             echo '</table>';
  35.             $result->free();  // Liberamos da memoria os recursos
  36.         }
  37.         else {
  38.             echo "Non hai datos que amosar!!!!";
  39.         }
  40.         $conex->close();  // Pechamos a conexión
  41.        
  42. ?>        
  43.     </body>    
  44. </html>


  • Resultado:
Php BD 8.jpg


  • Fixarse como neste exemplo, aínda que a consulta devolve o id_libro, como non o necesitamos non o amosamos ao usuario, xa que dito dato é un código interno que utilizamos como clave primaria.


  • O método fetch_assoc() move un punteiro interno á seguinte fila de resultados.
Se queremos modificar dito punteiro e situarnos en calquera fila do resultado, debemos facer uso da función mysqli_data_seek·


Consulta enviando parámetros de tipo numérico

Nota: Lembra que no paso de datos temos que ter en conta os posibles 'ataques' que poidan intentar facerse. Repasar o dado na wiki anteriormente.


  • Cando facemos unha consulta ou operación contra base de datos (alta, baixa e modificación) imos ter que 'enviar' datos.
A forma de facelo é utilizando a consulta SQL cun parámetro e substituíndo dito parámetro polo valor que queiramos enviar.
  • Así: SELECT col1, col2 FROM TABOA_1 WHERE col1=%d
No exemplo, col1 é de tipo numérico.
  1. define ('CONSULTA_CON_PARAM_NUMERICO','SELECT col1, col2 FROM TABOA_1 WHERE col1=%d');
  2.  
  3. $query = sprintf(CONSULTA_CON_PARAM_NUMERICO,valor_numérico_a_enviar);
Agora facemos o mesmo código que no exemplo anterior.
  1.  ......
  2.  $result=$conex->query(CONSULTA_CON_PARAM_NUMERICO);
  3.  ......


Caso práctico: Enviando parámetros de tipo numérico

  • Seguindo con exemplo, imos facer que cando o usuario seleccione un libro dentro dunha combobox, aparezan os exemplares do mesmo.
Para iso teremos que:
  • Crear a sentencia SQL correspondente enviando un parámetro que vai ser o código do libro seleccionado.
  • Obter dito dato. Isto o faremos utilizando un formulario cun método POST.
  • Modificar o código da páxina anterior (Consultar_Libros.php) e facer que os libros aparezan nunha combobox (dropdownlist ou <select> en html).
  • Facer que cando o usuario escolla un libro 'active' o formulario e polo tanto provoque o 'submit' do mesmo amosando os exemplares do libro seleccionado.
  • Manter seleccionado o libro na combo.


Nota: Ao principio ides ter problema de tipo 'non amosa ningún dato'. O primeiro que tedes que descartar é que a consulta SQL estea ben e devolva datos, polo que vos aconsello que fagades un 'echo' de dita consulta e a executedes no MysqlWorkBench para ver que realmente devolve algún resultado.


  • Crearemos unha nova páxina de nome Consultar_Exemplares_Por_Libro.php.
Modificamos o código para que aparezan os libros dentro da etiqueta <select> de html:
Neste caso, na combo imos gardar o código do libro xa que cando escollamos un, necesitamos obter dito código para facer outra consulta buscando os exemplares dese libro...
  1.     <body>
  2. <?php        
  3.        
  4.         $result=$conex->query(CONSULTAR_LIBROS);
  5.         if ($result && $result->num_rows>0){
  6.             echo "<select name='lstLibros'>";
  7.             echo "<option value='-1'></option>";
  8.             while($row = $result->fetch_assoc()) {
  9.                 printf("<option value='%d'>%s</option>",$row["id_libro"],$row["titulo"]);
  10.             }
  11.             echo '</select>';
  12.             $result->free();  // Liberamos da memoria os recursos
  13.         }
  14.         else {
  15.             echo "Non hai datos que amosar!!!!";
  16.         }
  17.         $conex->close();  // Pechamos a conexión
  18.        
  19. ?>        
  20.     </body>
  • Liña 7: Engadimos unha entrada en branco un valor -1 no seu value, para 'obrigar' a escoller un valor e que por defecto non apareza cun libro seleccionado.
Dependerá da aplicación, podedes ter que implementar variantes como ter unha entrada que indique 'TODAS' ou facer que por defecto estea seleccionada algunha das entradas...
Resultado:
Php BD 9.jpg



  • Arquivo bd.inc.php
Definimos nunha constate a consulta que vai obter os exemplares do libro seleccionado:
define ('CONSULTAR_EXEMPLARES_POR_LIBRO','select libro_id,id_exemplar,imaxe,prezo,estado_id from EXEMPLARES where libro_id=%d order by estado_id');
Como vemos imos enviar o código por medio dun parámetro de tipo numérico (usaremos a función sprintf).
Definimos nunha constante onde se atopan as imaxes relacionados cos libros:
define ('CARTAFOL_IMAXES_WEB','http://www.meusitio.es/IMAXES/');
Teredes que cambiar a url pola vosa.


  • Para facer que cando se seleccione un elemento do lista se 'active' o formulario, existen múltiples formas, unha delas pode ser:
echo "<select name='lstLibros' onchange='this.form.submit()'>";
  • O código que vai amosar os exemplares do libro seleccionado será o seguinte:
  1.         <?php        
  2.         // CONSULTAMOS OS EXEMPLARES DUN LIBRO SELECCIONADO
  3.         if (!empty($_POST['lstLibros']) && filter_var($_POST['lstLibros'],FILTER_VALIDATE_INT) && $_POST['lstLibros']!=-1){
  4.            
  5.             $query=sprintf(CONSULTAR_EXEMPLARES_POR_LIBRO,$_POST['lstLibros']);
  6.             $result=$conex->query($query);
  7.             if ($result && ($result->num_rows>0)){
  8.                 echo "<table border='1'>";
  9.                 echo "<th>Num. Exemplar</th><th>Imaxe</th><th>Prezo</th><th>Estado</th>";
  10.                 while($row = $result->fetch_assoc()) {
  11.                     echo '<tr>';
  12.                     printf("<td>%d</td><td><img alt='%s' src='%s' width='42' height='42' /></td><td>%s</td><td>%d</td>",$row["id_exemplar"],$row["imaxe"],CARTAFOL_IMAXES_WEB . $row["imaxe"],$row["prezo"],$row["estado_id"]);
  13.                     echo '</tr>';
  14.                 }
  15.                 echo '</table>';
  16.                 $result->free();  // Liberamos da memoria os recursos
  17.             }
  18.             else {
  19.                 echo "Non hai datos que amosar!!!!";
  20.             }
  21.            
  22.         }
  23.         $conex->close();  // Pechamos a conexión
  24.        
  25.         ?>
  • Liña 3: Comprobamos que veñan datos no formulario (que se escollera algún exemplar da lista) e que sexa de tipo numérico. Tamén comprobamos se seleccionamos o elemento baleiro (valor -1 na combo).
  • Liña 5: Definimos a consulta a realizar contra a base de datos, utilizando a constante definida e substituíndo o parámetro polo código do libro seleccionado.
  • Liñas 7-17: Construímos a táboa para que amose os datos. Fixarse que na imaxe temos que definir o atributo 'alt' para que cumpra o estándar XHTML, polo que enviamos dúas veces o nome do arquivo. No dato que vai no atributo 'src' enviamos o nome do arquivo xunto coa URL onde se atopan as imaxes.
  • Liña 23: Pechamos a conexión. Fixarse que esta liña debe aparecer unha vez. Se non teremos que volver a abrir a conexión á base de datos.



  • Facemos que se manteña seleccionado o libro:
  1.         <?php        
  2.         $result=$conex->query(CONSULTAR_LIBROS);
  3.         if ($result && ($result->num_rows>0)){
  4.             echo "<select name='lstLibros' onchange='this.form.submit()'>";
  5.             echo "<option value='-1'></option>";
  6.             while($row = $result->fetch_assoc()) {
  7.                 if(isset($_POST['lstLibros']) && $row['id_libro']==$_POST['lstLibros']){
  8.                     $cadea="selected='selected'";
  9.                 }
  10.                 else{
  11.                     $cadea='';
  12.                 }
  13.                 printf("<option value='%d' %s>%s</option>",$row["id_libro"],$cadea,$row["titulo"]);
  14.             }
  15.             echo '</select>';
  16.             $result->free();  // Liberamos da memoria os recursos
  17.         }
  18.         else {
  19.             echo "Non hai datos que amosar!!!!";
  20.         }
  21.        
  22.         ?>




  • O código completo sería o seguinte:

Arquivo: bd.inc.php:

  1. <?php
  2.  
  3. define ('SERVIDOR','localhost');
  4. define ('USUARIO','user_php');
  5. define ('PASSWORD','user_php');
  6. define ('BD','PHP');
  7.  
  8. define ('CARTAFOL_IMAXES_WEB','http://www.meusitio.es/IMAXES/');
  9.  
  10. define ('CONSULTAR_LIBROS','select id_libro,titulo from LIBROS order by titulo');
  11. define ('CONSULTAR_EXEMPLARES_POR_LIBRO','select libro_id,id_exemplar,imaxe,prezo,estado_id from EXEMPLARES where libro_id=%d order by estado_id');


Arquivo: Consultar_Exemplares_Por_Libro.php:

  1. <?php
  2.  
  3. require ('/var/www/confBD/bd.inc.php');
  4. //error_reporting(0);
  5.  
  6. $conex = new mysqli(SERVIDOR,USUARIO,PASSWORD,BD);
  7. $conex->set_charset('utf8');
  8.  
  9. if ($conex->connect_error){
  10.      die('Erro de Conexi&oacute;n (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
  11. }
  12. ?>
  13.  
  14.  
  15. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  16.     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  17. <html xmlns="http://www.w3.org/1999/xhtml" lang="es" xml:lang="es">
  18.     <head>
  19.         <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  20.         <title>Operacións BD</title>
  21.     </head>
  22.  
  23.     <body>
  24.         <form action="<?php echo $_SERVER['PHP_SELF'] ?>" method='post'>
  25.  
  26.         <?php        
  27.         $result=$conex->query(CONSULTAR_LIBROS);
  28.         if ($result && ($result->num_rows>0)){
  29.             echo "<select name='lstLibros' onchange='this.form.submit()'>";
  30.             echo "<option value='-1'></option>";
  31.             while($row = $result->fetch_assoc()) {
  32.                 if(isset($_POST['lstLibros']) && $row['id_libro']==$_POST['lstLibros']){
  33.                     $cadea="selected='selected'";
  34.                 }
  35.                 else{
  36.                     $cadea='';
  37.                 }
  38.                 printf("<option value='%d' %s>%s</option>",$row["id_libro"],$cadea,$row["titulo"]);
  39.             }
  40.             echo '</select>';
  41.             $result->free();  // Liberamos da memoria os recursos
  42.         }
  43.         else {
  44.             echo "Non hai datos que amosar!!!!";
  45.         }
  46.        
  47.         ?>        
  48.         </form>
  49.        
  50.        
  51.         <?php        
  52.         // CONSULTAMOS OS EXEMPLARES DUN LIBRO SELECCIONADO
  53.         if (!empty($_POST['lstLibros']) && filter_var($_POST['lstLibros'],FILTER_VALIDATE_INT) && $_POST['lstLibros']!=-1){
  54.            
  55.             $query=sprintf(CONSULTAR_EXEMPLARES_POR_LIBRO,$_POST['lstLibros']);
  56.             $result=$conex->query($query);
  57.             if ($result && ($result->num_rows>0)){
  58.                 echo "<table border='1'>";
  59.                 echo "<th>Num. Exemplar</th><th>Imaxe</th><th>Prezo</th><th>Estado</th>";
  60.                 while($row = $result->fetch_assoc()) {
  61.                     echo '<tr>';
  62.                     printf("<td>%d</td><td><img alt='%s' src='%s' width='42' height='42' /></td><td>%s</td><td>%d</td>",$row["id_exemplar"],$row["imaxe"],CARTAFOL_IMAXES_WEB . $row["imaxe"],$row["prezo"],$row["estado_id"]);
  63.                     echo '</tr>';
  64.                 }
  65.                 echo '</table>';
  66.                 $result->free();  // Liberamos da memoria os recursos
  67.             }
  68.             else {
  69.                 echo "Non hai datos que amosar!!!!";
  70.             }
  71.            
  72.         }
  73.         $conex->close();  // Pechamos a conexión
  74.        
  75.         ?>        
  76.        
  77.        
  78.     </body>    
  79. </html>




  • Imos realizar unha mellora nos datos que se visualizan.
Como vemos, aparece o código do estado do libro. O que queremos amosar é a descrición, polo que teremos que facer un Join coa táboa ESTADOS.

Arquivo bd.inc.php:

  1. define ('CONSULTAR_EXEMPLARES_POR_LIBRO','select libro_id,id_exemplar,imaxe,prezo,estado_id,ESTADOS.descripcion as desc_estado'
  2.         . ' from EXEMPLARES INNER JOIN ESTADOS ON (ESTADOS.id_estado=EXEMPLARES.estado_id)'
  3.         . ' where libro_id=%d order by desc_estado');


Arquivo: Consultar_Exemplares_Por_Libro.php:

  1.          ..................
  2.  
  3.         <?php        
  4.         // CONSULTAMOS OS EXEMPLARES DUN LIBRO SELECCIONADO
  5.         if (!empty($_POST['lstLibros']) && filter_var($_POST['lstLibros'],FILTER_VALIDATE_INT) && $_POST['lstLibros']!=-1){
  6.            
  7.             $query=sprintf(CONSULTAR_EXEMPLARES_POR_LIBRO,$_POST['lstLibros']);
  8.             $result=$conex->query($query);
  9.             if ($result && ($result->num_rows>0)){
  10.                 echo "<table border='1'>";
  11.                 echo "<th>Num. Exemplar</th><th>Imaxe</th><th>Prezo</th><th>Estado</th>";
  12.                 while($row = $result->fetch_assoc()) {
  13.                     echo '<tr>';
  14.                     printf("<td>%d</td><td><img alt='%s' src='%s' width='42' height='42' /></td><td>%s</td><td>%s</td>",$row["id_exemplar"],$row["imaxe"],CARTAFOL_IMAXES_WEB . $row["imaxe"],$row["prezo"],$row["desc_estado"]);
  15.                     echo '</tr>';
  16.                 }
  17.                 echo '</table>';
  18.                 $result->free();  // Liberamos da memoria os recursos
  19.             }
  20.             else {
  21.                 echo "Non hai datos que amosar!!!!";
  22.             }
  23.            
  24.         }
  25.         $conex->close();  // Pechamos a conexión
  26.        
  27.         ?>        
  28.  
  29.          ..................
  • Liña 14: Modificamos o tipo de parámetro que pasa de ser numérico (%d) a cadea (%s) e amosamos a descrición do estado (as dúas columnas do select non deben ter o mesmo nome, descrición do libro e descrición do estado).


  • Resultado:
Php BD 12.jpg




EXERCICIO PROPOSTO:

Crea unha páxina de nome Consultar_Exemplares_Por_Estado.php no que aparezan en forma de radiobutton os estados da táboa ESTADOS.
Ao seleccionar un estado, amosaranse en forma de lista ordenada os exemplares (libro_id,num_exemplar,prezo) ordenados por libro_id (quen queira pode facer un join coa táboa LIBROS e amosar o título en vez do libro_id e ordenar os resultados polo título).
O radiobutton debe manterse seleccionado.



POSIBLE SOLUCIÓN:

Arquivo bd.inc.php

  1. define ('CONSULTAR_ESTADOS','select id_estado,descripcion from ESTADOS order by descripcion');
  2. define ('CONSULTAR_EXEMPLARES_POR_ESTADO','select titulo,id_exemplar,prezo '
  3.         . ' from EXEMPLARES INNER JOIN LIBROS ON (LIBROS.id_libro=EXEMPLARES.libro_id)'
  4.         .                 ' INNER JOIN ESTADOS ON (ESTADOS.id_estado=EXEMPLARES.estado_id)'
  5.         . ' where estado_id=%d order by titulo');
Definimos dúas consultas. Unha para buscar os estados e outra para buscar os exemplares que teñan un estado determinado (pasado como parámetros)


Arquivo Consultar_Estados_Por_Estado.php

  1. <?php
  2.  
  3. require ('/var/www/confBD/bd.inc.php');
  4. //error_reporting(0);
  5.  
  6. $conex = new mysqli(SERVIDOR,USUARIO,PASSWORD,BD);
  7. $conex->set_charset('utf8');
  8.  
  9. if ($conex->connect_error){
  10.      die('Erro de Conexi&oacute;n (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
  11. }
  12. ?>
  13.  
  14.  
  15. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  16.     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  17. <html xmlns="http://www.w3.org/1999/xhtml" lang="es" xml:lang="es">
  18.     <head>
  19.         <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  20.         <title>Operacións BD</title>
  21.     </head>
  22.  
  23.     <body>
  24.         <form action="<?php echo $_SERVER['PHP_SELF'] ?>" method='post'>
  25.  
  26.         <?php        
  27.         $cont=0;    // Conta o número de estados impresos e cando leve múltiplo de dous salta de liña
  28.         $result=$conex->query(CONSULTAR_ESTADOS);
  29.         if ($result && $result->num_rows>0){
  30.             while($row = $result->fetch_assoc()) {
  31.                 $cont++;
  32.                 if(isset($_POST['rbgrpEstado']) && $row['id_estado']==$_POST['rbgrpEstado']){
  33.                     $cadea="checked='checked'";
  34.                 }
  35.                 else{
  36.                     $cadea='';
  37.                 }
  38.                 printf("<input onclick='javascript:this.form.submit()' type='radio' name='rbgrpEstado' value='%d' %s>%s</input>",$row["id_estado"],$cadea,$row["descripcion"]);
  39.                 if(($cont%2)==0){
  40.                     echo "<br />";
  41.                 }
  42.             }
  43.             $result->free();  // Liberamos da memoria os recursos
  44.         }
  45.         else {
  46.             echo "Non hai estados que amosar!!!!";
  47.         }
  48.        
  49.         ?>        
  50.         </form>
  51.        
  52.        
  53.         <?php        
  54.         // CONSULTAMOS OS EXEMPLARES DUN ESTADO SELECCIONADO
  55.         if (!empty($_POST['rbgrpEstado']) && filter_var($_POST['rbgrpEstado'],FILTER_VALIDATE_INT)){
  56.            
  57.             $query=sprintf(CONSULTAR_EXEMPLARES_POR_ESTADO,$_POST['rbgrpEstado']);
  58.             $result=$conex->query($query);
  59.             if ($result && ($result->num_rows>0)){
  60.                 echo "<ol>";
  61.                 while($row = $result->fetch_assoc()) {
  62.                     echo '<li>';
  63.                     printf("T&iacute;tulo:%s - Num_exemplar:%d - Prezo:%s",$row["titulo"],$row["id_exemplar"],$row["prezo"]);
  64.                     echo '</li>';
  65.                 }
  66.                 echo '</ol>';
  67.                 $result->free();  // Liberamos da memoria os recursos
  68.             }
  69.             else {
  70.                 echo "Non hai datos que amosar!!!!";
  71.             }
  72.            
  73.         }
  74.      
  75.         $conex->close();  // Pechamos a conexión
  76.        
  77.         ?>        
  78.        
  79.        
  80.     </body>    
  81. </html>



  • Resultado:
Php BD 13.jpg

Consulta enviando parámetros de tipo cadea

  • Cando utilicemos datos de tipo cadea, debemos lembrar poñelos entre comillas simples ('dato') na orde SQL.


Nota: Lembra que no paso de datos temos que ter en conta os posibles 'ataques' que poidan intentar facerse. Repasar o dado na wiki anteriormente.


  • Cando facemos unha consulta ou operación contra base de datos (alta, baixa e modificación) imos ter que 'enviar' datos.
A forma de facelo é utilizando a consulta SQL cun parámetro e substituíndo dito parámetro polo valor que queiramos enviar.
  • Así: SELECT col1, col2 FROM TABOA_1 WHERE col2='%d'
No exemplo, col2 é de tipo cadea (por exemplo, varchar ou char).
  1. define ('CONSULTA_CON_PARAM_CADEA',"SELECT col1, col2 FROM TABOA_1 WHERE col2='%s'");
  2.  
  3. $query = sprintf(CONSULTA_CON_PARAM_CADEA,'valor_cadea_a_enviar');
Fixarse como o parámetro col2 vai entrecomillado e o formato do parámetro é '%s'.


Agora facemos o mesmo código que no exemplo anterior.
  1.  ......
  2.  $result=$conex->query(CONSULTA_CON_PARAM_CADEA);
  3.  ......


Caso práctico: Enviando parámetros de tipo cadea

  • Imos a aplicalo ao noso exemplo e imos crear unha páxina no que buscaremos libros en base a un nome do autor.
A busca farase utilizando a orde SQL like, que busca patróns de cadea. Este operador non distingue maiúsculas de minúsculas.
Se queremos distinguir maiúsculas de minúsculas utilizaremos o operador like binary.
O operador like o imos aplicar ao nome completo do autor, polo que teremos que concatenar o nome e apelido. Usaremos a función concat_ws ou concat.


  • O usuario introducirá nunha caixa de texto o nome (ou parte del) do autor.
Nota: Lembra que no paso de datos temos que ter en conta os posibles 'ataques' que poidan intentar facerse. Repasar o dado na wiki anteriormente.
Mysql incorpora tamén unha función para codificar os caracteres (como espazos en branco, saltos de liña,...Función string mysqli::real_escape_string ( string $escapestr ). Devolve a cadea 'codificada'.
Por exemplo:
  1. $cidade = "'s Hertogenbosch";
  2.  
  3. /* esta consulta fallará debido a que no escapa $ciudad */
  4. if (!$mysqli->query("SELECT nome FROM CIDADES where nome_cidade = '$cidade'")) {
  5.     printf("Erro: %s\n", $mysqli->sqlstate);
  6. }
  7.  
  8. $cidade = $mysqli->real_escape_string($cidade);
  9.  
  10. /* esta consulta con $ciudad escapada funcionará */
  11. if (!$mysqli->query("SELECT nome FROM CIDADES where nome_cidade = '$cidade'")) {
  12.     printf("Fila inxerida.\n");
  13. }
Exemplo obtido de php.net


  • Pode suceder que un mesmo autor teña varios libros. Como imos a amosar os datos dentro dun grid no que na primeira columna vai ser o nome do autor e a segunda o nome do libro, vai repetirse o nome do autor como se amosa na seguinte imaxe:
Php BD 10.jpg
Para evitalo, teremos que gardar en cada iteración unha vez 'impresa' a liña do autor, o nome do autor e comparalo cando carguemos a seguinte fila de datos. Se o nome do autor da nova liña de datos é igual ao anterior, entón non o debemos de amosar.
O código sería algo parecido a isto:
  1.                     $autor='';  // A utilizamos para que non se repita o autor na táboa cando hai varios libros do mesmo autor
  2.                     $last_autor=''; // A utilizamos para que non se repita o autor na táboa cando hai varios libros do mesmo autor
  3.                     while($row = $result->fetch_assoc()) {     // PROCESAMOS AS FILAS DO RESULTADO DA CONSULTA
  4.                         if ($last_autor==$row["nome_autor"]){  // Comprobamos se o novo autor é igual ao anterior procesado
  5.                             $autor='';           // Se é igual entón quere dicir que estamos a ler unha fila co mesmo autor que a anterior. Polo tanto non 'imprimimos o autor'
  6.                         }
  7.                         else {
  8.                             $autor=$row["nome_autor"];  // En caso contrario quere dicir que o autor actual é diferente ao anterior e polo tanto debemos sacalo por pantalla.
  9.                         }
  10.  
  11.                         printf(AMOSAMOS OS DATOS DO LIBRO E DA VARIABLE $autor);
  12.  
  13.                         $last_autor=$row["nome_autor"];   // Gardamos o nome do autor para comparar có autor que veña na seguinte fila de datos.
  14.                     }


  • Imos facer que cada libro sexa un enlace para abrir unha páxina nova de nome Detalle_Libro.php. No resultado da busca soamente imos amosar o nome do libro, de tal forma que cando o usuario preme sobre o enlace, apareza unha nova xanela cos datos completos dese libro, incluíndo os exemplares).
Para facelo imos facer uso de javascript e da función window.open.
Crearei unha función en javascript que espere recibir un parámetro, que sexa a url a abrir e chamarei a window.open cunha serie de modificacións, como o tamaño da xanela, que non poida modificar a url,...
O código será o seguinte:
  1.     <head>
  2.         <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  3.         <title>Operacións BD</title>
  4.         <script type="text/javascript">
  5.             function abrirVentana($url){
  6.                 window.open($url,'_blank','channelmode=0,status=0;directories=0,location=0,menubar=0,titlebar=0,toolbar=0,width=600,height=400');
  7.             }
  8.         </script>
  9.        
  10.     </head>


Para chamar a esta función usaremos a etiqueta <a> e o evento onclick, coma o seguinte exemplo:
  1. <a href='#' onclick="javascript:abrirVentana('Detalle_Libro.php?libro=1');">Título Libro</a>
Hai que ter moito coidado cando estamos a traballar con PHP-Javascript polo tema das comillas xa que teremos que 'xogar' coas simples e dobres para que se pechen correctamente.


  • SOLUCIÓN

Arquivo: bd.inc.php

Nota: Definimos a consulta a realizar á base de datos (lembrar que é mellor facer dita consulta directamente no Mysql e despois copiala ao código PHP pasando os parámetros correspondentes)

<?php

define ('SERVIDOR','localhost');
define ('USUARIO','user_php');
define ('PASSWORD','user_php');
define ('BD','PHP');

define ('CARTAFOL_IMAXES_WEB','http://www.meusitio.es/IMAXES/');

define ('CONSULTAR_LIBROS','select id_libro,titulo from LIBROS order by titulo');
define ('CONSULTAR_EXEMPLARES_POR_LIBRO','select libro_id,id_exemplar,imaxe,prezo,estado_id,ESTADOS.descripcion as desc_estado'
        . ' from EXEMPLARES INNER JOIN LIBROS ON (LIBROS.id_libro=EXEMPLARES.libro_id)'
        .                 ' INNER JOIN ESTADOS ON (ESTADOS.id_estado=EXEMPLARES.estado_id)'
        . ' where id_libro=%d order by titulo,desc_estado');
define ('CONSULTAR_LIBROS_POR_AUTOR',"select CONCAT(apelido,',',nome) as nome_autor,id_libro,titulo from LIBROS " .
            ' INNER JOIN LIBROS_AUTORES ON (LIBROS_AUTORES.libro_id=LIBROS.id_libro) ' .
            ' INNER JOIN AUTORES ON (LIBROS_AUTORES.autor_id=AUTORES.id_autor)' .
        " where CONCAT(AUTORES.nome,' ',AUTORES.apelido) like '%%%s%%' " .
        'order by nome_autor,titulo');
Nota: Fixarse como o parámetro que recibe 'like' é de tipo cadea e polo tanto vai entre comillas.


Arquivo Buscar_Libros_Por_Autor.php

  1. <?php
  2.  
  3. require ('/var/www/confBD/bd.inc.php');
  4. //error_reporting(0);
  5.  
  6. $conex = new mysqli(SERVIDOR,USUARIO,PASSWORD,BD);
  7. $conex->set_charset('utf8');
  8.  
  9. if ($conex->connect_error){
  10.      die('Erro de Conexi&oacute;n (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
  11. }
  12. $erro="";   // Variable que a usaremos para amosar os posibles erros que poidan darse
  13. ?>
  14.  
  15.  
  16. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  17.     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  18. <html xmlns="http://www.w3.org/1999/xhtml" lang="es" xml:lang="es">
  19.     <head>
  20.         <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  21.         <title>Operacións BD</title>
  22.         <script type="text/javascript">
  23.             function abrirVentana($url){
  24.                 window.open($url,'_blank','channelmode=0,status=0;directories=0,location=0,menubar=0,titlebar=0,toolbar=0,width=600,height=400');
  25.             }
  26.         </script>
  27.        
  28.     </head>
  29.  
  30.     <body>
  31.         <form action="<?php echo $_SERVER['PHP_SELF'] ?>" method='post'>
  32.             Introduce o nome do autor a buscar:
  33.             <input type='text' size='50' maxlength="100" name="txtAutor" value='<?php echo (isset($_POST['txtAutor']) ? $_POST["txtAutor"]:""); ?>'/>
  34.             <input type="submit" value="BUSCAR" />
  35.             <input type='button' onclick="javascript:this.form['txtAutor'].value='';" value="LIMPAR" />
  36.            
  37.            
  38.         <?php        
  39.         if (!empty($_POST['txtAutor'])){  // Premimos o botón de busca
  40.             $textoBuscar = filter_var($_POST['txtAutor'],FILTER_SANITIZE_STRING);
  41.             if (strlen($textoBuscar)>100){
  42.                 $erro="<h2>A cadea a buscar non pode ser maior de 100 caracteres</h2>";
  43.             }
  44.             else {
  45.                 $query=sprintf(CONSULTAR_LIBROS_POR_AUTOR,$textoBuscar);
  46.                 $result=$conex->query($query);
  47.                 if ($result->num_rows>0){
  48.                     $autor='';  // A utilizamos para que non se repita o autor na táboa cando hai varios libros do mesmo autor
  49.                     $last_autor=''; // A utilizamos para que non se repita o autor na táboa cando hai varios libros do mesmo autor
  50.                     echo "<table border='1'>";
  51.                     echo "<th>Autor</th><th>Libro</th>";
  52.                     while($row = $result->fetch_assoc()) {
  53.                         if ($last_autor==$row["nome_autor"]){
  54.                             $autor=$row["nome_autor"];
  55.                         }
  56.                         else {
  57.                             $autor=$row["nome_autor"];
  58.                         }
  59.                         echo '<tr>';
  60.                         $onclick=sprintf("javascript:abrirVentana(\"Detalle_Libro.php?libro=%d\");",$row["id_libro"]);
  61.                         printf("<td>%s</td><td><a href='#' onclick='%s'>%s</a></td>",$autor,$onclick,$row['titulo']);
  62.                         echo '</tr>';
  63.                         $last_autor=$row["nome_autor"];
  64.                     }
  65.                     $result->free();
  66.                     echo "</table>";
  67.                 }
  68.                 else {
  69.                     echo "<h2><b>Non hai resultados</b></h2>";
  70.                 }
  71.                
  72.                
  73.             }
  74.         }
  75.        
  76.         ?>        
  77.         </form>
  78.        
  79.        
  80.         <?php        
  81.         if ($erro!='') {
  82.             printf("<h3>%s</h3>",$erro);
  83.         }
  84.         $conex->close();  // Pechamos a conexión
  85.        
  86.         ?>        
  87.        
  88.        
  89.     </body>    
  90. </html>
  • Fixarse que nesta páxina non necesitamos conectar ao principio, xa que soamente se carga a caixa de texto para que o usuario introduza o nome do autor a buscar.
Polo tanto a conexión e o peche da base de datos se podería poñer dentro do if da liña 39.
  • Fixarse que o botón de limpar non é de tipo 'reset' xa que ao cargar o que veña en $_POST o botón tipo reset volvería a amosar dito valor.


  • Resultado:
Php BD 11.jpg

Consulta chamando a procedementos almacenados

  • Un procedemento almacenado ven a ser o equivalente a unha función en PHP, coa diferenza que as ordes que conforman o procedemento van permitir realizar accións (inserción, borrado, modificación e selección) sobre a base de datos.
  • Como comentamos anteriormente nesta wiki, unha das mellores formas de evitar ataques de tipo 'inxección de CSS' é utilizar procedementos almacenados.
Tamén é unha forma de aumentar a seguridade de acceso á base de datos Mysql, xa que teremos que dar permiso de execución sobre o procedemento almacenado e o usuario non vai poder acceder ás táboas da base de datos nin examinar a estrutura das mesmas.
Desta forma podemos cambiar a estrutura da táboa (como por exemplo cambiar o nome dunha columna) e simplemente cambiando a sentenza SQL que está dentro do procedemento almacenado e poñendo un alias có nome antigo, a aplicación no cliente segue funcionando.
Outra vantaxe é que as sentencias SQL non están definidas nas páxinas PHP e polo tanto se realizamos un cambio o faremos no Mysql sendo 'transparente' cara ao usuario.


  • O primeiro que temos que facer é crear o procedemento almacenado.
  • A orde SQL é CREATE PROCEDURE.
  • Creamos o procedemento almacenado:
  • Cando definimos un procedemento almacenado debemos facer uso da palabra DELIMITER para indicarlle ao Mysql onde acaba a definición do procedemento almacenado. Máis información neste enlace.

Creamos un procedemento almacenado dentro do MysqlWorkBench

  1. DELIMITER $$
  2.  
  3. CREATE PROCEDURE `NOME_PROCEDEMENTO` ()
  4. BEGIN
  5.  
  6.    select col1,col2
  7.    from TABOA_1
  8.    order by col1;
  9.  
  10.  
  11. END
  12. $$
Como vemos o procedemento está formado por unha consulta SELECT (pode poñerse calquera)


  • Agora dende PHP temos que facer uso do comando CALL 'NOME_PROCEDEMENTO'.
Isto é o que teriamos que poñer se dende Mysql queredes chamar ao procedemento....
  1.         $conex = new mysqli('localhost','user_php','user_php','PHP');
  2.         $conex->set_charset('utf8');
  3.          
  4.         if (!$conex->select_db('OutraBD')){
  5.              die('Erro ao cambiar de base de datos');
  6.         }
  7.  
  8.  
  9.         $proc = sprintf("CALL %s",NOME_PROCEDEMENTO);


  • A diferenza do caso anterior, cando chamemos a un procedemento almacenado teremos que facer uso dunha das seguintes funcións:
mysqli::real_query ou mysqli::multi_query
Nos imos facer uso da chamada a multi_query xa que tamén a imos poder utilizar cando dentro dun mesmo procedemento almacenado teñamos varios select´s e queiramos recuperar o contido de todos eles (cada un será un conxunto de resultados).
  1.         ........................
  2.         $proc = sprintf("CALL %s",NOME_PROCEDEMENTO);
  3.         if ($conex->multi_query($proc)){
  4.             $result = $conex->store_result();
  5.             if ($result && ($result->num_rows>0)){
  6.                  ................
  7.             }
  8.  
  9.         }


Unha vez temos o conxunto de resultados ($result) xa operamos como nos casos anteriores.


  • IMPORTANTE Se estamos a utilizar procedementos almacenados, pode darse o caso de que despois de chamar a un procedemento que devolva un conxunto de resultados, ao intentar chamar a outro procedemento vos dea un erro '2014 - Commands out of sync; you can't run this command now' ou simplemente a chamada ao seguinte procedemento non vos devolva resultados.
A resposta a ese erro a tedes neste enlace.


  • Ao parecer, existen aínda conxuntos de resultados sen 'recoller' (aínda que o procedemento soamente devolva un) e non vos deixará realizar outra chamada ata que leades todos os conxuntos de resultados.
Para facelo, podedes engadir o seguinte código:
  1.         ........................
  2.         $proc = sprintf("CALL %s",NOME_PROCEDEMENTO);
  3.         if ($conex->multi_query($proc)){
  4.             $result = $conex->store_result();
  5.             if ($result && ($result->num_rows>0)){
  6.                  ................
  7.                  // Procesamos cada fila
  8.             }
  9.             while($conex->next_result() && $conex->store_result());
  10.  
  11.         }



  • Como comentamos anteriormente, un procedemento pode estar conformado por múltiples 'select'.
Se queremos 'pasar' ao seguinte 'conxunto de resultados' temos que chamar á función mysqli::next_result desta forma:
  1.         ........................
  2.         $proc = sprintf("CALL %s",NOME_PROCEDEMENTO);
  3.         if ($conex->multi_query($proc)){
  4.             $result = $conex->store_result();
  5.             if ($result && ($result->num_rows>0)){
  6.                  ................
  7.             }
  8.             if ($conex->next_result()){
  9.                $result=$conex->store_result();
  10.             }
  11.  
  12.         }
Se imos procesar todos os conxuntos de resultados da mesma forma poderíamos utilizar un while{}...


Caso Práctico: Consulta chamando a procedementos almacenados

  • Vexamos un exemplo. Imos copiar o código da páxina Consultar_Libros.php e imos crear unha nova de nome Consultar_Libros_Proc co mesmo código, facendo as modificacións que se indican a continuación para que faga uso da chamada a un procedemento almacenado.


  • Creamos o procedemento almacenado:

Creamos un procedemento almacenado dentro do MysqlWorkBench

  1. DELIMITER $$
  2.  
  3. CREATE PROCEDURE `CONSULTAR_LIBROS` ()
  4. BEGIN
  5.  
  6.    select id_libro,titulo
  7.    from LIBROS
  8.    order by titulo;
  9.  
  10.  
  11. END
  12. $$


  • Definimos nunha constante o nome do procedemento almacenado dentro do arquivo bd.inc.php.
Arquivo: bd.inc.php
  1. define('PROCEDEMENTO_CONSULTAR_LIBROS','CONSULTAR_LIBROS()');   // Se levara parámetros faríamos o mesmo que nos casos anteriores coas sentencias select...


Nos imos facer uso da chamada a multi_query xa que tamén a imos poder utilizar cando dentro dun mesmo procedemento almacenado teñamos varios select´s e queiramos recuperar o contido de todos eles (cada un será un conxunto de resultados).
Arquivo: Consultar_Libros_Proc.php
  1.     <body>
  2.         <form action="<?php echo $_SERVER['PHP_SELF'] ?>" method='post'>
  3.         <?php        
  4.         $proc = sprintf("CALL %s",PROCEDEMENTO_CONSULTAR_LIBROS);
  5.         if ($conex->multi_query($proc)){
  6.             $result = $conex->store_result();
  7.             if ($result && ($result->num_rows>0)){
  8.                 echo "<select name='lstLibros' onchange='this.form.submit()'>";
  9.                 echo "<option value='-1'></option>";
  10.                 while($row = $result->fetch_assoc()) {
  11.                     if(isset($_POST['lstLibros']) && $row['id_libro']==$_POST['lstLibros']){
  12.                         $cadea="selected='selected'";
  13.                     }
  14.                     else{
  15.                         $cadea='';
  16.                     }
  17.                     printf("<option value='%d' %s>%s</option>",$row["id_libro"],$cadea,$row["titulo"]);
  18.                 }
  19.                 echo '</select>';
  20.                 $result->free();  // Liberamos da memoria os recursos
  21.             }
  22.             else {
  23.                 echo "Non hai datos que amosar...";
  24.             }
  25.                
  26.         }
  27.         else {
  28.             echo "Houbo un erro na chamado ao procedemento:" . $conex->error; // A mensaxe do erro ($conex->error) non a teremos que amosar coa aplicación funcionando
  29.         }
  30.         ?>        
  31.         </form>
  • Liña 4: Creamos a cadea que vai chamar ao procedemento almacenado: CALL CONSULTAR_LIBROS()
  • Liña 5: Modificamos a liña e agora chamamos ao procedemento mysqli::multi_query(nome_proc). Dita chamada devolve false en caso de erro.
  • Liña 6: Gardamos o resultado (o primeiro select do procedemento almacenado) no obxecto $result e xa operamos igual que no caso anterior cando usamos as sentencias 'select' directamente.

Consulta chamando a procedementos almacenados con parámetros de entrada

  • Podemos chamar a un procedemento almacenado enviando datos ao mesmo en forma de parámetros.
Indicar que en case todos os xestores os parámetros poden ser de 3 tipos: de entrada, de saída e de entrada/saída.
  • Os parámetros de saída (OUT) son variables que van poder ser modificadas no interior do procedemento e cuxo valor vai poder ser lido dentro do código da páxina php.
  • Os parámetros de entrada (IN) son variables que van poder ser utilizadas dentro do procedemento (chegan en forma de parámetros) pero as modificacións que se fagan no seu valor non chegan á paxina PHP.
  • Os parámetros de entrada/entrada (INOUT) son unha combinación dos dous anteriores.
Máis información neste enlace.
  • Por defecto, se non especificamos nada, todos os parámetros dun procedemento almacenado son considerados 'de entrada'
  • Primeiro teremos que definir o procedemento en Mysql:
  1. DELIMITER $$
  2.  
  3. CREATE PROCEDURE `PROCEDEMENTO_CON_PARAMETROS`(IN _param1 varchar(100))
  4. BEGIN
  5.  
  6.     SELECT col1,col2
  7.     FROM TABOA_1
  8.     WHERE col2=_param1;
  9.  
  10. END
Como vemos, xunto ao nome do procedemento hai que especificar o tipo (dentro dos tipos de mysql).
Unha vez definidos podemos utilizalos dentro do procedemento almacenado.
Calquera modificación do valor do parámetro non se verá reflectidos no código PHP. Se no código PHP 'recuperamos' o valor do parámetro, este terá o mesmo valor que cando enviamos o dato ao procedemento.


Podemos definir máis parámetros, separando por comas cada un deles, por exemplo:
  1. DELIMITER $$
  2.  
  3. CREATE PROCEDURE `PROCEDEMENTO_CON_PARAMETROS`(IN _param1 varchar(100),_param2 int)
  4. BEGIN
  5.  
  6.     SELECT col1,col2
  7.     FROM TABOA_1
  8.     WHERE col2=_param1 and
  9.           col1=_param2
  10.  
  11. END


  • Unha vez definido teremos que enviar os datos que queiramos dende PHP desta forma:
  1. define('CHAMAR_PROCEDEMENTO_CON_PARAMETROS','PROCEDEMENTO_CON_PARAMETROS(%d,"%s")');
Fixarse como no caso de datos de tipo numérico non enviamos o dato con comiñas.
Fixarse como 'xogamos' coas comiñas simples de dobres para pechar correctamente a cadea.
  1. define('CHAMAR_PROCEDEMENTO_CON_PARAMETROS','PROCEDEMENTO_CON_PARAMETROS(%d,"%s")');
  2.  
  3.          ........................
  4.  
  5.         $proc = sprintf(CHAMAR_PROCEDEMENTO_CON_PARAMETROS,valor_numérico,'valor_cadea');
  6.         $proc = sprintf("CALL %s",$proc);
  7.         if ($conex->multi_query($proc)){
  8.             $result = $conex->store_result();
  9.             if ($result && ($result->num_rows>0)){
  10.                  ................
  11.             }
  12.         }
Fixarse que ao final o que temos que conseguir é esta cadea: CALL PROCEDEMENTO_CON_PARAMETROS(5,'valor') // 5 e valor son datos de exemplo
Isto o podemos conseguir de diferentes formas. Eu vos amoso unha delas.


Caso Práctico: Consulta chamando a procedementos almacenados con parámetros de entrada

  • Seguindo có noso exemplo...imos crear unha nova páxina de nome Buscar_Libros_Por_Autor_Proc que faga uso de procedementos almacenados en vez de sentencias SQL directamente no código.

Solución:

  • Procedemento almacenado CONSULTAR_LIBROS_POR_AUTOR en Mysql
  1. DELIMITER $$
  2.  
  3. CREATE PROCEDURE `CONSULTAR_LIBROS_POR_AUTOR`(IN _autor_buscar varchar(100))
  4. BEGIN
  5.  
  6. select CONCAT(apelido,',',nome) as nome_autor,id_libro,titulo from LIBROS
  7.             INNER JOIN LIBROS_AUTORES ON (LIBROS_AUTORES.libro_id=LIBROS.id_libro)
  8.             INNER JOIN AUTORES ON (LIBROS_AUTORES.autor_id=AUTORES.id_autor)
  9. where CONCAT(AUTORES.nome,' ',AUTORES.apelido) like  CONCAT('%', _autor_buscar , '%')
  10. order by nome_autor,titulo;
  11.  
  12. END
Nota: Para comprobar se funciona facede primeiro unha chamada dende o propio Mysql coa orde: call CONSULTAR_LIBROS_POR_AUTOR('dru');
Fixarse como o parámetro é de tipo IN (valor por defecto, podería non poñerse nada).


  • Definimos a constante en PHP no que está definido o nome do procedemento e o parámetro que vai levar.
Arquivo: bd.inc.php
  1. define('PROCEDEMENTO_CONSULTAR_LIBROS_POR_AUTOR','CONSULTAR_LIBROS_POR_AUTOR("%s")');
Nota: Fixarse como o parámetro ao ser de tipo 'cadea' debe ir entre comillas.


  • Arquivo Buscar_Libros_Por_Autor_Proc
  1. <?php
  2.  
  3. require ('/var/www/confBD/bd.inc.php');
  4. //error_reporting(0);
  5.  
  6. $conex = new mysqli(SERVIDOR,USUARIO,PASSWORD,BD);
  7. $conex->set_charset('utf8');
  8.  
  9. if ($conex->connect_error){
  10.      die('Erro de Conexi&oacute;n (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
  11. }
  12. $erro="";   // Variable que a usaremos para amosar os posibles erros que poidan darse
  13. ?>
  14.  
  15.  
  16. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  17.     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  18. <html xmlns="http://www.w3.org/1999/xhtml" lang="es" xml:lang="es">
  19.     <head>
  20.         <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  21.         <title>Operacións BD</title>
  22.         <script type="text/javascript">
  23.             function abrirVentana($url){
  24.                 window.open($url,'_blank','channelmode=0,status=0;directories=0,location=0,menubar=0,titlebar=0,toolbar=0,width=600,height=400');
  25.             }
  26.         </script>
  27.        
  28.     </head>
  29.  
  30.     <body>
  31.         <form action="<?php echo $_SERVER['PHP_SELF'] ?>" method='post'>
  32.             Introduce o nome do autor a buscar:
  33.             <input type='text' size='50' maxlength="100" name="txtAutor" value='<?php echo (isset($_POST['txtAutor']) ? $_POST["txtAutor"]:""); ?>'/>
  34.             <input type="submit" value="BUSCAR" />
  35.             <input type='button' onclick="javascript:this.form['txtAutor'].value='';" value="LIMPAR" />
  36.            
  37.            
  38.         <?php        
  39.         if (!empty($_POST['txtAutor'])){  // Premimos o botón de busca
  40.             $textoBuscar = filter_var($_POST['txtAutor'],FILTER_SANITIZE_STRING);
  41.             if (strlen($textoBuscar)>100){
  42.                 $erro="<h2>A cadea a buscar non pode ser maior de 100 caracteres</h2>";
  43.             }
  44.             else {
  45.                 $proc = sprintf(PROCEDEMENTO_CONSULTAR_LIBROS_POR_AUTOR,$textoBuscar); // Cambiamos o texto a buscar polo parámetro definido na constante
  46.                 $proc = sprintf("CALL %s",$proc);
  47.                 if ($conex->multi_query($proc)){
  48.                     $result = $conex->store_result();
  49.                     if ($result->num_rows>0){
  50.                         $autor='';  // A utilizamos para que non se repita o autor na táboa cando hai varios libros do mesmo autor
  51.                         $last_autor=''; // A utilizamos para que non se repita o autor na táboa cando hai varios libros do mesmo autor
  52.                         echo "<table border='1'>";
  53.                         echo "<th>Autor</th><th>Libro</th>";
  54.                         while($row = $result->fetch_assoc()) {
  55.                             if ($last_autor==$row["nome_autor"]){
  56.                                 $autor='';
  57.                             }
  58.                             else {
  59.                                 $autor=$row["nome_autor"];
  60.                             }
  61.                             echo '<tr>';
  62.                             $onclick=sprintf("javascript:abrirVentana(\"Detalle_Libro.php?libro=%d\");",$row["id_libro"]);
  63.                             printf("<td>%s</td><td><a href='#' onclick='%s'>%s</a></td>",$autor,$onclick,$row['titulo']);
  64.                             echo '</tr>';
  65.                             $last_autor=$row["nome_autor"];
  66.                         }
  67.                         $result->free();
  68.                         echo "</table>";
  69.                     }
  70.                     else {
  71.                         echo "<h2><b>Non hai resultados</b></h2>";
  72.                     }
  73.                 }
  74.                 else{
  75.                     echo "Houbo un erro na chamado ao procedemento:" . $conex->error; // A mensaxe do erro non a teremos que amosar coa aplicación funcionando
  76.                 }
  77.                
  78.             }
  79.         }
  80.        
  81.         ?>        
  82.         </form>
  83.        
  84.        
  85.         <?php        
  86.         if ($erro!='') {
  87.             printf("<h3>%s</h3>",$erro);
  88.         }
  89.         $conex->close();  // Pechamos a conexión
  90.        
  91.         ?>        
  92.        
  93.        
  94.     </body>    
  95. </html>
  • Liñas 45 e 46: Creamos a cadea que vai chamar ao procedemento almacenado de Mysql: CALL CONSULTAR_LIBROS_POR_AUTOR('dru');

Consulta chamando a procedementos almacenados con parámetros de saída

  • Agora imos aplicar o concepto de parámetros de saída, que como indicamos no punto anterior, son parámetros que van poder ser modificados o seu valor dentro do procedemento almacenado e recoller o seu valor modificado dende a parte de PHP.
  • Primeiro teremos que definir o procedemento en Mysql:
  1. DELIMITER $$
  2.  
  3. CREATE PROCEDURE `PROCEDEMENTO_CON_PARAMETROS`(OUT _param1 varchar(100))
  4. BEGIN
  5.  
  6.     SELECT col2
  7.     INTO _param1
  8.     FROM TABOA_1
  9.     WHERE col1=1;
  10.  
  11. END
Agora especificamos que o parámetro é de saída antepoñendo a palabra OUT ao seu nomo.
Neste exemplo estamos a supoñer que a consulta devolve unha única fila e estamos gardando o resultado da consulta no parámetro de saída.
Podemos modificar o seu valor dentro do procedemento coa orde: SET _param1=valor;


  • Unha vez definido teremos que construír a chamada ao procedemento enviando o nome dunha variable (para recoller o valor modificado).
O nome da variable terá este formato: @nomeVariable:
  1. define('CHAMAR_PROCEDEMENTO_CON_PARAMETROS','PROCEDEMENTO_CON_PARAMETROS("@nomeVariable")');
Fixarse como a variable é de tipo cadea e vai entre comiñas.


  • Agora a chamada se fai igual que no caso anterior, pero fixarse que neste caso O PROCEDEMENTO NON DEVOLVE UN CONXUNTO DE RESULTADOS. Podería ser e nese caso obraríamos igual que no caso anterior, a maiores do que imos ver agora.
Para recoller o dato da variable teremos que facer unha consulta SELECT da forma: SELECT @nomeVariable as valorDevolto
  1. <?php
  2.          define('CHAMAR_PROCEDEMENTO_CON_PARAMETROS','PROCEDEMENTO_CON_PARAMETROS("@nomeVariable")');
  3.  
  4.          ........................
  5.  
  6.         $proc = sprintf("CALL %s",CHAMAR_PROCEDEMENTO_CON_PARAMETROS);
  7.         if ($conex->multi_query($proc)){
  8.             $result = $conex->store_result();
  9.  
  10.             if (!($resultado = $conex->query("SELECT @nomeVariable as valorDevolto"))) {
  11.                  echo "Fallou a obtención: (" . $conex->errno . ") " . $conex->error;
  12.             }
  13.             else {
  14.                  $fila = $resultado->fetch_assoc();
  15.                  echo $fila['valorDevolto']);
  16.             }
  17.  
  18.         }
  19.          ........................
  20.  
  21.  
  22. ?>
Fixarse que ao final o que temos que conseguir é esta cadea: CALL PROCEDEMENTO_CON_PARAMETROS(5,'valor') // 5 e valor son datos de exemplo
Isto o podemos conseguir de diferentes formas. Eu vos amoso unha delas.


Nota: Non fai falla facer un alias do nome da variable. Podemos empregar o propio nome no fetch_assoc().
Podemos poñer:
  1.         $proc = sprintf("CALL %s",CHAMAR_PROCEDEMENTO_CON_PARAMETROS);
  2.         if ($conex->multi_query($proc)){
  3.             $result = $conex->store_result();
  4.  
  5.             if (!($resultado = $conex->query("SELECT @nomeVariable"))) {
  6.                  echo "Fallou a obtención: (" . $conex->errno . ") " . $conex->error;
  7.             }
  8.             else {
  9.                  $fila = $resultado->fetch_assoc();
  10.                  echo $fila['@nomVariable']);
  11.             }
  12.  
  13.         }


Caso práctico: Consulta chamando a procedementos almacenados con parámetros de saída

  • Segundo o noso exemplo, imos crear un procedemento almacenado que devolva nun parámetro de saída o número de libros que temos na nosa tenda.


  • Procedemento almacenado CONSULTAR_NUM_LIBROS en Mysql
  1. DELIMITER $$
  2.  
  3. CREATE DEFINER=`root`@`localhost` PROCEDURE `CONSULTAR_NUM_LIBROS`(OUT _num_libros int)
  4. BEGIN
  5.         SELECT COUNT(*)
  6.         INTO _num_libros
  7.         FROM LIBROS;
  8.  
  9. END
  10. $$


Como se pode ver leva un parámetro de saída (OUT). Indicar que podemos poñer calquera combinación de parámetros na definición dun procedemento almacenado.


  • Arquivo bd.inc.php:
  • Definimos a chamada ao procedemento cunha variable no seu interior que vai ser a que garde o valor modificado de dentro do procedemento.
  1. define('PROCEDEMENTO_CONSULTAR_NUM_LIBROS','CONSULTAR_NUM_LIBROS(@numero_libros)');


  • Arquivo Consultar_Num_Libros.php
Esta páxina está a modo de exemplo. Loxicamente isto podería ser utilizado dentro dunha páxina para obter o número de libros sen necesidade de crear unha páxina específica.
  1. <?php
  2.  
  3. require ('/var/www/confBD/bd.inc.php');
  4. //error_reporting(0);
  5.  
  6. $conex = new mysqli(SERVIDOR,USUARIO,PASSWORD,BD);
  7. $conex->set_charset('utf8');
  8.  
  9. if ($conex->connect_error){
  10.      die('Erro de Conexi&oacute;n (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
  11. }
  12.  
  13. ?>
  14.  
  15.  
  16. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  17.     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  18. <html xmlns="http://www.w3.org/1999/xhtml" lang="es" xml:lang="es">
  19.     <head>
  20.         <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  21.         <title>Operacións BD</title>
  22.        
  23.     </head>
  24.  
  25.     <body>
  26.  
  27.         <?php
  28.             $proc= sprintf("CALL %s",PROCEDEMENTO_CONSULTAR_NUM_LIBROS);
  29.             if ($conex->multi_query($proc)){
  30.                 $result = $conex->store_result();
  31.                 if (!($resultado = $conex->query("SELECT @numero_libros as _p_out"))) {
  32.                      echo "Fallou a obtención: (" . $conex->errno . ") " . $conex->error;
  33.                 }
  34.                 else {
  35.                      $fila = $resultado->fetch_assoc();
  36.                      printf("<h1>NUMERO DE LIBROS A VENDA:%d",$fila['_p_out']);
  37.                 }
  38.             }
  39.             else {
  40.                  echo "Fallou a chamada ao proc.: (" . $conex->errno . ") " . $conex->error;
  41.             }
  42.  
  43.        
  44.             $conex->close();  // Pechamos a conexión
  45.        
  46.         ?>        
  47.        
  48.        
  49.     </body>    
  50. </html>

Consulta chamando a funcións

  • Dende Mysql pódense definir funcións, que son procedementos almacenados que devolven un valor dun tipo de datos.
Definicións de funcións dende Mysql.


  • Vexamos un exemplo:
  1. DELIMITER $$
  2.  
  3. CREATE FUNCTION `EXEMPLO_FUNCION`() RETURNS int
  4. BEGIN
  5.     declare variable int;
  6.  
  7.         SELECT col2
  8.         INTO variable
  9.         FROM TABLA_1
  10.         WHERE col1=1;
  11.  
  12.         return variable;
  13.  
  14.  
  15. END
  16. $$
Como vemos, esta función vai devolver un número de tipo integer.


  • Para chamar á función dende Mysql teremos que 'engadila' dentro dunha cláusula SELECT.
  1. define('FUNCION_EXEMPLO_MYSQL','select EXEMPLO_FUNCION() as valor_devolto');
Como vemos, a tratamos coma dunha cláusula SELECT calquera, dándolle un nome (alias) ao resultado da execución da función.



  1. <?php
  2.  
  3. define('FUNCION_EXEMPLO_MYSQL','select EXEMPLO_FUNCION() as valor_devolto');
  4.  
  5. $conex = new mysqli('SERVIDOR','USUARIO','PASSWORD','BD');
  6. $conex->set_charset('utf8');
  7.  
  8. if ($conex->connect_error){
  9.      die('Erro de Conexi&oacute;n (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
  10. }
  11.  
  12. ?>
  13.  
  14.  
  15.         <?php
  16.             $result=$conex->query(FUNCION_EXEMPLO_MYSQL);
  17.             if ($result || ($result->num_rows>0)){
  18.                 $row = $result->fetch_assoc();
  19.                 printf("<h1>Dato devolto:%d</h1>",$row['valor_devolto']);
  20.                 $result->free();
  21.             }
  22.             else {
  23.                  echo "Fallou a chamada á función: (" . $conex->errno . ") " . $conex->error;
  24.             }
  25.  
  26.        
  27.             $conex->close();  // Pechamos a conexión
  28.        
  29.         ?>
Nota: Fixarse como na liña 19 a referencia que utilizamos é o alias posto no DEFINE.


Caso Práctico: Consulta chamando a funcións

  • Imos facer un exemplo aplicado ao noso proxecto.
Imos facer que o número de libros da nosa tenda sexa obtido utilizando unha función e non o procedemento almacenado con parámetro de saída da sección anterior.
  • Creamos a función en Mysql
  1. DELIMITER $$
  2.  
  3. CREATE FUNCTION `CONSULTAR_NUM_LIBROS`() RETURNS int
  4. BEGIN
  5.     declare num_libros int;
  6.  
  7.         SELECT COUNT(*)
  8.         INTO num_libros
  9.         FROM LIBROS;
  10.  
  11.         return num_libros;
  12.  
  13.  
  14. END
  15. $$


  • Aqruivo bd.inc.php
Definimos a constante que vai chamar á función.
  1. define('FUNCION_CONSULTAR_NUM_LIBROS','select CONSULTAR_NUM_LIBROS() as num_libros');
Fixarse no alias da chamada á función.


  • Creamos unha páxina Consultar_Num_Libros_Con_FuncionMYSQL.php
  1. <?php
  2.  
  3. require ('/var/www/confBD/bd.inc.php');
  4. //error_reporting(0);
  5.  
  6. $conex = new mysqli(SERVIDOR,USUARIO,PASSWORD,BD);
  7. $conex->set_charset('utf8');
  8.  
  9. if ($conex->connect_error){
  10.      die('Erro de Conexi&oacute;n (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
  11. }
  12.  
  13. ?>
  14.  
  15.  
  16. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  17.     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  18. <html xmlns="http://www.w3.org/1999/xhtml" lang="es" xml:lang="es">
  19.     <head>
  20.         <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  21.         <title>Operacións BD</title>
  22.        
  23.     </head>
  24.  
  25.     <body>
  26.  
  27.         <?php
  28.             $result=$conex->query(FUNCION_CONSULTAR_NUM_LIBROS);
  29.             if ($result && ($result->num_rows>0)){
  30.                 $row = $result->fetch_assoc();
  31.                 printf("<h1>N&uacute;mero de libros:%d</h1>",$row['num_libros']);
  32.                 $result->free();
  33.             }
  34.             else {
  35.                  echo "Fallou a chamada ao proc.: (" . $conex->errno . ") " . $conex->error;
  36.             }
  37.  
  38.        
  39.             $conex->close();  // Pechamos a conexión
  40.        
  41.         ?>        
  42.        
  43.        
  44.     </body>    
  45. </html>

Paxinación de resultados

  • En moitas ocasións imos necesitar paxinar os resultados dunha consulta.
Isto é necesario cando o resultado pode devolver unha cantidade moi grande de rexistros que obrigaría ao usuario a facer demasiado scroll.


Dita cláusula pode levar dous parámetros separados por coma: LIMIT numero_fila,tamaño e vai ao final da orde SELECT.
Por exemplo:
  1. SELECT id_libro,titulo
  2. FROM LIBROS
  3. ORDER by titulo
  4. LIMIT 0,2
Este exemplo amosaría os dous primeiros rexistros empezando polo primeiro do conxunto de resultados (rexistros 0 e 1)
Se modifico a orde e poño:
  1. SELECT id_libro,titulo
  2. FROM LIBROS
  3. ORDER by titulo
  4. LIMIT 2,2
Devolverá os rexistros 2 e 3 do conxunto de resultados. Estamos paxinando os resultados de dous en dous !!!!


  • Polo tanto para paxinar os datos dunha consulta terei que:
  • Establecer o número de rexistros por páxina. Isto será o que leve a cláusula LIMIT como segundo dato.
  • Gardar a páxina actual e mandar dito dato entre as diferentes peticións da páxina PHP (normalmente enlaces có número de páxina).
  • Para saber o número de páxinas terei que obter o número de rexistros en total, xa que o número de páxinas será igual ao número de rexistros dividido (parte enteira) entre o número de rexistros por páxina.
Para isto faremos uso da función ceil que devolve o número enteiro redondeado cara arriba.
Así, se teño 10 elementos e quero paxinar 3 elementos por páxina: ceil(10/3) = 4 que serán o número de páxinas.


Caso Práctico: Paxinación de resultados

  • Imos crear unha nova páxina de nome Consultar_Libros_Con_Paxinacion.php que partindo do código da páxina 'Consultar_Libros.php' feita previamente, amose os libros con paxinación.
O faremos utilizando un procedemento almacenado (de nome CONSULTAR_LIBROS_PAXINACION), que vai levar dous parámetros, o número de páxina actual e o número de libros por páxina (tamén se podería facer directamente cun select dende PHP sen utilizar procedementos almacenados, pero xa vimos que é máis seguro).


  • Aplicado ao noso exemplo, imos engadir máis datos para poder facer unha paxinación.
Táboa AUTORES
INSERT INTO `PHP`.`AUTORES` (`apelido`, `nome`) VALUES ('ABBOTT', 'RACHEL');
INSERT INTO `PHP`.`AUTORES` (`apelido`, `nome`) VALUES ('HJORTH', 'MICHAEL');
INSERT INTO `PHP`.`AUTORES` (`apelido`, `nome`) VALUES ('LAURENS', 'STEPHANIE');
INSERT INTO `PHP`.`AUTORES` (`apelido`, `nome`) VALUES ('ESTRINGANA', 'MORUENA');
INSERT INTO `PHP`.`AUTORES` (`apelido`, `nome`) VALUES ('NIXON', 'RITA');


Táboa LIBROS
INSERT INTO `PHP`.`LIBROS` (`titulo`) VALUES ('COMO UNA EXTRAÑA');
INSERT INTO `PHP`.`LIBROS` (`titulo`) VALUES ('CRÍMENES DUPLICADOS');
INSERT INTO `PHP`.`LIBROS` (`titulo`) VALUES ('A SALVO CON SU CAPTOR');
INSERT INTO `PHP`.`LIBROS` (`titulo`) VALUES ('MI ERROR FUE NO SER YO MISMA. PARTE II');
INSERT INTO `PHP`.`LIBROS` (`titulo`) VALUES ('EL AMOR NO ES UN INVENTO DE LOS POETAS');


Táboa LIBROS_AUTORES
Teredes que ter en conta os identificadores autonuméricos xerados nos insert´s anteriores.
INSERT INTO `PHP`.`LIBROS_AUTORES` (`libro_id`, `autor_id`) VALUES ('3', '2');
INSERT INTO `PHP`.`LIBROS_AUTORES` (`libro_id`, `autor_id`) VALUES ('4', '3');
INSERT INTO `PHP`.`LIBROS_AUTORES` (`libro_id`, `autor_id`) VALUES ('5', '4');
INSERT INTO `PHP`.`LIBROS_AUTORES` (`libro_id`, `autor_id`) VALUES ('6', '5');
INSERT INTO `PHP`.`LIBROS_AUTORES` (`libro_id`, `autor_id`) VALUES ('7', '6');


Táboa EXEMPLARES
Teredes que ter en conta os identificadores autonuméricos xerados nos insert´s da táboa libros.
INSERT INTO `PHP`.`EXEMPLARES` (`libro_id`, `id_exemplar`, `prezo`, `estado_id`) VALUES ('3', '1', '9,99', '1');
INSERT INTO `PHP`.`EXEMPLARES` (`libro_id`, `id_exemplar`, `prezo`, `estado_id`) VALUES ('3', '2', '9,99', '1');
INSERT INTO `PHP`.`EXEMPLARES` (`libro_id`, `id_exemplar`, `imaxe`, `prezo`, `estado_id`) VALUES ('4', '1', '', '29,99', '1');
INSERT INTO `PHP`.`EXEMPLARES` (`libro_id`, `id_exemplar`, `prezo`, `estado_id`) VALUES ('5', '1', '23,35', '1');
INSERT INTO `PHP`.`EXEMPLARES` (`libro_id`, `id_exemplar`, `imaxe`, `prezo`, `estado_id`) VALUES ('6', '1', '', '34,76', '1');
INSERT INTO `PHP`.`EXEMPLARES` (`libro_id`, `id_exemplar`, `imaxe`, `prezo`, `estado_id`) VALUES ('7', '1', '', '22,54', '1');


  • Creamos o procedemento almacenado en Mysql para que devolva os libros con paxinación.
Poderíamos modificar o que xa tiñamos, comprobando se enviamos valores aos parámetros ou non (valor null) e facendo un select ou outro en función dos valores...

Procedemento almacenado CONSULTAR_LIBROS_PAXINACION en Mysql

  1. DELIMITER $$
  2.  
  3. CREATE DEFINER=`root`@`localhost` PROCEDURE `CONSULTAR_LIBROS_PAXINACION`(IN _num_rexistro int,IN _tam_paxina INT)
  4. BEGIN
  5.         SELECT id_libro,titulo
  6.         FROM LIBROS
  7.         ORDER BY titulo
  8.         LIMIT _num_rexistro,_tam_paxina;
  9.  
  10. END


  • Creamos a constante que vai facer uso deste procedemento.

Arquivo: bd.inc.php

define('PROCEDEMENTO_CONSULTAR_LIBROS_PAXINACION','CONSULTAR_LIBROS_PAXINACION(%d,%d)');
Como vemos imos enviar dous parámetros.



  • Partimos do código da páxina Consultar_Libros.php e creamos unha nova páxina de nome Consultar_Libros_Con_Paxinacion.php
Arquivo: Consultar_Libros_Con_Paxinacion.php
  1. <?php
  2.  
  3.     require ('/var/www/confBD/bd.inc.php');
  4.     define('CONST_NUM_LIBROS_PAXINA',2);
  5.     //error_reporting(0);
  6.  
  7.     $conex = new mysqli(SERVIDOR,USUARIO,PASSWORD,BD);
  8.     $conex->set_charset('utf8');
  9.  
  10.     if ($conex->connect_error){
  11.          die('Erro de Conexi&oacute;n (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
  12.     }
  13. ?>
  14.  
  15.              
  16. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  17.     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  18. <html xmlns="http://www.w3.org/1999/xhtml" lang="es" xml:lang="es">
  19.     <head>
  20.         <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  21.         <title>Operacións BD</title>
  22.     </head>
  23.  
  24.     <body>
  25.                
  26.             <?php
  27.                 // OBTEMOS O NUMERO DE LIBROS
  28.                 // Este código ven dun caso práctico anterior que devolve o número de libros
  29.                 //   facendo uso dun procedemento almacenado e dun parámetro de saída.
  30.                 $numLibros=0;
  31.                 $proc= sprintf("CALL %s",PROCEDEMENTO_CONSULTAR_NUM_LIBROS);
  32.                 if ($conex->multi_query($proc)){
  33.                     $result = $conex->store_result();
  34.                     if (!($resultado = $conex->query("SELECT @numero_libros as _p_out"))) {
  35.                          die ("Fallou a obtención: (" . $conex->errno . ") " . $conex->error);
  36.                     }
  37.                     else {
  38.                          $fila = $resultado->fetch_assoc();
  39.                          $numLibros = $fila['_p_out'];
  40.                     }
  41.                 }
  42.                 else {
  43.                      die("Fallou a chamada ao proc.: (" . $conex->errno . ") " . $conex->error);
  44.                 }
  45.             ?>
  46.  
  47.              // $numLibros ten o número de libros da nosa táboa
  48.             <?php
  49.                 // AMOSAMOS AS PAXINAS EN BASE AO NUMERO TOTAL E O NUMERO POR PAXINA
  50.                 $total_paxinas = ceil($numLibros/CONST_NUM_LIBROS_PAXINA);
  51.                
  52.                 // Establecemos cal é a páxina actual. Se o usuario elixiu algunha virá en forma de URL $_GET
  53.                 if(!empty($_GET['paxina'])){
  54.                     $paxina_actual=$_GET['paxina'];
  55.                 }
  56.                 else{
  57.                     $paxina_actual=1;  // A primeira vez o usuario non pulsou ningunha páxina e polo tanto a páxina inicial é a 1
  58.                 }
  59.                
  60.                 // 'Imprimimos as páxinas...
  61.                 echo "<div>";
  62.                 for($cont=1;$cont<=$total_paxinas;$cont++){
  63.                     if ($cont==$paxina_actual){   // A páxina actual non leva link
  64.                         echo $cont . ' ';
  65.                     }
  66.                     else{
  67.                         printf("<a href='%s?paxina=%d'>%d</a> ",$_SERVER['PHP_SELF'],$cont,$cont);
  68.                     }
  69.                 }
  70.                 echo "</div>";
  71.             ?>
  72.             <?php        
  73.                 $rexistro_actual=($paxina_actual-1)*CONST_NUM_LIBROS_PAXINA;   // Restamos 1 á páxina actual xa que a páxina 1 empeza no rexistro 0
  74.                 $proc=sprintf(PROCEDEMENTO_CONSULTAR_LIBROS_PAXINACION,$rexistro_actual,CONST_NUM_LIBROS_PAXINA);    
  75.                 $proc = sprintf("CALL %s",$proc);
  76.                 if ($conex->multi_query($proc)){
  77.                     $result = $conex->store_result();
  78.                     if ($result->num_rows>0){
  79.                         echo "<table border='1'>";
  80.                         while($row = $result->fetch_assoc()) {
  81.                             echo '<tr>';
  82.                             printf("<td>%s</td>",$row["titulo"]);
  83.                             echo '</tr>';
  84.                         }
  85.                         echo '</table>';
  86.                         $result->free();  // Liberamos da memoria os recursos
  87.                     }
  88.                     else {
  89.                         echo "Non hai datos que amosar!!!!";
  90.                     }
  91.                 }
  92.                
  93.                 $conex->close();  // Pechamos a conexión
  94.                    
  95.                    
  96.             ?>        
  97.     </body>    
  98. </html>
  • Liña 4: Indicamos o número de libros a amosar en cada páxina.
  • Liñas 27-44: Obtemos o número de libros que temos na base de datos. Estamos a aproveitar o código dun exercicio anterior no que amosábamos como traballar cun parámetro de saída, pero neste caso sería máis sinxelo crear un novo procedemento ou unha función, que devolvera o número de libros.
  • Liñas 50-71: Amosamos as páxinas en base ao número de rexistros totais e o número de libros a amosar por páxina.
  • Liñas 73-74: Pasamos ao procedemento almacenado a partires de que número de libro e a cantidade de libros que vai devolver o select no procedemento almacenado.



Optimización
  • O exercicio anterior poderíamos optimizalo para non ter que repetir a chamada a consultar o número de rexistros totais.
Esta chamada a estamos a facer en cada consulta á páxina.
Se non estamos nun entorno multi-usuario no que se poida dar o caso de que se estean a dar de alta novos rexistros de forma continua, podemos optimizar o código e facer que a chamada a consultar o número de rexistros totais se faga unha vez.


  • Deberemos gardar nalgunha variable o número de rexistros totais e que dita información se manteña entre chamadas cando cambiamos de número de páxina.
  • Posibles solucións:
  • Utilizar coookies.
  • Utilizar variables de sesión.
  • Aproveitar o formulario da paxinación e enviar en forma de campo oculto o número de rexistros totais.
  • Enviar o dato na URL a maiores do número de páxina.



  • Posible solución utilizando variables de sesión:

Arquivo Consultar_Libros_Con_Paxinacion.php

  1. <?php
  2.   session_start();   // Iría ao principio de todo
  3.  
  4. ?>
  5. .......................
  6.  
  7.  
  8.             <?php
  9.                 if (empty($_SESSION['numRexistrosTotais']) {
  10.                   // OBTEMOS O NUMERO DE LIBROS
  11.                   $numLibros=0;
  12.                   $proc= sprintf("CALL %s",PROCEDEMENTO_CONSULTAR_NUM_LIBROS);
  13.                   if ($conex->multi_query($proc)){
  14.                       $result = $conex->store_result();
  15.                       if (!($resultado = $conex->query("SELECT @numero_libros as _p_out"))) {
  16.                            die ("Fallou a obtención: (" . $conex->errno . ") " . $conex->error);
  17.                       }
  18.                       else {
  19.                            $fila = $resultado->fetch_assoc();
  20.                            $numLibros = $fila['_p_out'];
  21.                            $_SESSION['numRexistrosTotais'] = $numLibros;
  22.                       }
  23.                   }
  24.                   else {
  25.                        die("Fallou a chamada ao proc.: (" . $conex->errno . ") " . $conex->error);
  26.                   }
  27.                 }
  28.                 else {
  29.                      $numLibros = $_SESSION['numRexistrosTotais'];
  30.                 }
  31.             ?>
  32.             ...................

Exercicio proposto

  • Crea unha páxina de nome Buscar_Libros.php no que se amose nun formulario as seguintes opcións de busca de libros:
  • Busca por título: Será unha caixa de texto limitada a 50 caracteres que fará uso de like (busca por patróns)
  • Busca por prezo: Estará implementada en forma de caixa limitada a 6 caracteres (tres números, unha coma e dous decimais). Deberedes verificar que o formato sexa correcto.
  • Busca por estado: Estará implementada en forma de lista (<select> en html con size) (opcionalmente podedes permitir que o usuario poida escoller varios estados).
  • Busca por autor: Estará implementada en forma de combobox (<select> en html)
  • Criterios de ordenación: Por título, prezo e autor (implementalo con radiobuttons).
  • O usuario vai poder escoller calquera das opcións combinándoas como prefira.
  • Os resultados van poder amosarse paxinados. O número de libros por páxina estará indicado nunha caixa de texto co seu propio formulario e gardado nunha variable de sesión.
  • O resultado vai ser unha lista de libros, os seus autores e os exemplares, ordenados por título
  • Implementa a solución utilizando consultas.
  • Quen queira tamén pode intentalo facer con procedementos almacenados (Buscar_Libros_Proc.php), pero a consulta teredes que facela dinamicamente dentro do procedementos.
Podedes consultar como facelo neste enlace.

Alta

O proceso é moi parecido ao que fixemos coas consultas, coa diferenza que no caso dunha instrución INSERT non temos rexistros de volta.
Soamente debemos comprobar se a inserción a fixo correctamente, comprobando se a chamado ao método query devolve false.
  • Comentar que tamén pode sernos útil a instrución INSERT IGNORE. Esta instrución non vai emitir ningún erro se intentamos inserir unha clave duplicada nunha táboa. Loxicamente a operación non se fai.


  • O proceso para realizar unha inserción é o seguinte:
  • Abrimos a conexión á base de datos.
  1. $conex = new mysqli(SERVIDOR,USUARIO,PASSWORD,BD);
  2. $conex->set_charset('utf8');


  • Construímos a orde SQL: INSERT INTO TABOA_1 (col1,col2) VALUES (valor1,'valor2')
Neste caso a TABOA_1 está constituída por dúas columnas, a primeira numérica e a segunda de tipo cadea.
  • Executamos a orde e comprobamos que a operación foi correcta:
  1. $query = "INSERT INTO TABOA_1 (col1,col2) VALUES (valor1,'valor2')";
  2. $result=$conex->query($query);
  3. if ($result) {
  4.   echo "Rexistro dado de alta correctamente";
  5. }
  6. else {
  7.   echo "Erro na inserción:" . $conex->error;
  8. }
Fixarse como a consulta a gardamos nunha variable para non ter problemas coas comiñas na chamada á función query.
  • Pechamos a conexión.
  1. $conex->close();  // Pechamos a conexión



  • Cando o rexistro foi dado de alta correctamente podemos comprobar o número de rexistros afectados (no caso dun único rexistro sempre vai devolver 1, pero tamén se podería aplicar a un insert multifila).
  1. ..................
  2. $result=$conex->query($query);
  3. if ($result) {
  4.   echo "Rexistro dado de alta correctamente";
  5.   echo "Num. rexistros afectados:" . $conex->affected_rows;
  6. }
  7. else {
  8.   echo "Erro na inserción:" . $conex->error;
  9. }
  10. $conex->close();  // Pechamos a conexión


Alta utilizando procedementos almacenados

  • O proceso de alta é o mesmo que o visto nas consultas, pero utilizando a orde SQL INSERT dentro do procedemento.


  • Se estamos a utilizar procedementos almacenados, lembrar que en caso de erro '2014 - Commands out of sync; you can't run this command now', teredes que engadir debemos de engadir a seguinte liña despois de chamar a un procedemento almacenado:
  1.                     if ($conex->multi_query($proc)){
  2.                         $result = $conex->store_result();
  3.                         if ($result && ($result->num_rows>0)){
  4.                             while($fila=$result->fetch_assoc()){
  5.                                 // Procesamos cada fila
  6.                             }
  7.                             $result->free();
  8.                         }
  9.                         while($conex->next_result() && $conex->store_result());


  • IMPORTANTE: Se o procedemento de alta non vai devolver datos (cun select) podemos facer uso da orde $conex->query($proc) sendo $proc='CALL NOME_PROC(parámetros)'


  • Nota: Hai que ter unha correspondencia entre o tipo de dato e tamaño do parámetro do procedemento almacenado e a columna da táboa onde imos a inserir o dato.
Así, se a táboa TABOA_1 ten de columnas col1 int, col2 varchar(100)
Se defino un procedemento almacenado para dar de alta unha nova fila: CREATE PROCEDURE ALTA_FILA_TABOA_1(_param1 int, _param2 varchar(100))


  • Indicar que dentro dun procedemento almacenado imos poder ter sentenzas:

Obtendo o valor dun campo autonumérico

  • Normalmente cando deseñamos unha base de datos, certos campos que son claves os definimos como autonuméricos.
Ao facelo, cando engadimos unha nova fila a un táboa, xérase un novo valor e en certas ocasións necesitamos obter dito valor xa que o podemos utilizar para gardar outros datos noutras táboas que o necesitan por estar relacionadas.
  • Non vale a solución de engadir un dato e inmediatamente facer un 'select' buscando o valor máis alto, xa que non entorno multiusuario, dito valor non ten porqué ser o último engadido.
Dita función devolve o id autoxerado que se utilizou na última operación.
  • Por exemplo, imaxinemos que temos a táboa TABLE_1 con dúas columnas, col1 int primary key autogenerate e col2 varchar(100).
Cando executamos a orde: INSERT INTO TABLE_1 (col2) VALUES ('valor1')
  • O proceso é o seguinte:
  • Abrimos a conexión á base de datos.
  • Executamos a orde SQL que engade unha nova fila.
  1. $result=$conex->query("INSERT INTO TABLE_1 (col2) VALUES ('valor')");
  2. if ($result){
  3.   // Operación correcta
  4.   printf ("Novo rexistro có id %d.\n", $conex->insert_id);
  5. }
  6. else {
  7.   echo 'Erro na execución da operación';
  8. }
  • Pechamos a conexión.
  1. $conex->close();  // Pechamos a conexión


Caso práctico: Obtendo o valor dun campo autonumérico

  • Imos aplicar isto ao noso exemplo.
Imos crear unha páxina de nome Xestionar_Estados.php. Dita páxina terá un formulario onde indicaremos cun opcións de radio-button se queremos dar de alta, baixa ou modificar un estado da táboa ESTADOS.
Nos imos implementar a opción de 'Alta' de novos estados.
  • Ao seleccionar dita opción, aparecerá un novo formulario no que introduciremos nunha caixa de texto o novo estado.
Ao premer o botón de alta, chamaremos a un procedemento almacenado de nome 'INSERIR_ESTADO()' pasando como parámetro o novo estado.
Dito procedemento verificará que o estado xa non estea dado de alta previamente. Nese caso devolverá en forma de 'SELECT' o valor -1. En caso de que todo vaia ben, devolverá o id xerado utilizando un SELECT.


Nota: Fixarse que se non utilizáramos procedementos almacenados e o fixeramos con consultas directamente, teríamos que facer un consulta para comprobar se o estado estaba dado de alta previamente (por exemplo con: select count(*) as existe from ESTADOS where upper(descripcion)=upper('valor_enviad') e outra sentenza para engadir o novo estado en caso de que non exista.
No exemplo anterior emprégase a función UPPER para converter o texto a maiúsculas.
Cando traballades con tipos de datos cadeas será algo que teredes que ter en conta. Vai depender se no formulario de entrada de datos permitides ou non as minúsculas, ides necesitar empregar esta copón.


  • Outro aspecto importante é que ten que haber unha correspondencia de tipo e tamaño entre o campo da táboa onde imos facer a operación (neste caso a táboa ESTADOS, campo descripcion, que é un varchar(100)) e o parámetro que imos definir no procedemento almacenado.


Posible Solución: Utilizando orden INSERT
  • Lembrar que dependendo dos casos, teremos que verificar que o valor a engadir non estea dado de alta previamente...

Páxina bd.inc.php

  1. define('CONSULTAR_ESTADO_NUM',"SELECT count(*) as existe FROM ESTADOS WHERE upper(descripcion)=upper('%s')");
  2. define('INSERIR_ESTADO',"INSERT INTO ESTADOS (descripcion) VALUES ('%s')");


Páxina Xestionar_Estados.php

  1. <?php
  2.  
  3. require ('/var/www/confBD/bd.inc.php');
  4. //error_reporting(0);
  5.  
  6. $conex = new mysqli(SERVIDOR,USUARIO,PASSWORD,BD);
  7. $conex->set_charset('utf8');
  8.  
  9. if ($conex->connect_error){
  10.      die('Erro de Conexi&oacute;n (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
  11. }
  12. $erro="";   // Variable que a usaremos para amosar os posibles erros que poidan darse
  13. ?>
  14.  
  15.  
  16. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  17.     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  18. <html xmlns="http://www.w3.org/1999/xhtml" lang="es" xml:lang="es">
  19.     <head>
  20.         <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  21.         <title>Xestionar t&aacute;boa Estados BD</title>
  22.     </head>
  23.  
  24.     <body>
  25.  
  26.  
  27.         <!-- Esta parte poderíase facer con javascript -->
  28.         <form action="<?php echo $_SERVER['PHP_SELF'] ?>" method='post'>
  29.             <h2>Escolle a operación a facer sobre a táboa ESTADOS</h2>
  30.        
  31.             <input <?php echo (isset($_POST['rbtnOperacion']) && $_POST['rbtnOperacion']=='ALTA') ? "checked='checked'" : "";  ?>
  32.                 onclick='javascript:this.form-submit()' type='radio' name="rbtnOperacion" value='ALTA'>ALTA</input>
  33.             <input <?php echo (isset($_POST['rbtnOperacion']) && $_POST['rbtnOperacion']=='BAIXA') ? "checked='checked'" : "";  ?>
  34.                 onclick='javascript:this.form-submit()'  type='radio' name="rbtnOperacion" value='BAIXA'>BAIXA</input>
  35.             <input <?php echo (isset($_POST['rbtnOperacion']) && $_POST['rbtnOperacion']=='MODIFICACION') ? "checked='checked'" : "";  ?>
  36.                 onclick='javascript:this.form-submit()'  type='radio' name="rbtnOperacion" value='MODIFICACION'>MODIFICACI&Oacute;N</input>
  37.         <?php  
  38.        
  39.         if (!empty($_POST['rbtnOperacion'])){  // Premimos unha opción => Xeramos o formulario
  40.             switch ($_POST['rbtnOperacion']){
  41.                 case 'ALTA':
  42.                     printf("<div>Nome do novo estado:");
  43.                     printf("<input name='txtEstado' type='text' size='50' maxlength='100' /></div>");
  44.                     printf("<div><input name='btnOperacion' type='submit' value='ALTA' />");
  45.                     printf("<input type='reset' value='BORRAR' /></div>");
  46.                     break;
  47.             }
  48.         }
  49.  
  50.         // Xestionamos a operacion do segundo formulario => Alta , Baixa ou Modificacion
  51.         if (!empty($_POST['btnOperacion']) && $_POST['btnOperacion']=='ALTA'){  
  52.             if (strlen($_POST['txtEstado'])>100){
  53.                 $erro = ">A lonxitude da cadea non pode ter máis de 100 caracteres...";
  54.             }
  55.             else {
  56.                 // Eliminamos caracteres non permitidos
  57.                 $novoEstado = filter_var($_POST['txtEstado'],FILTER_SANITIZE_STRING);
  58.                 // Escapamos as comillas simples e dobres
  59.                 $novoEstado = $conex->real_escape_string($novoEstado);
  60.                
  61.                 $query=sprintf(CONSULTAR_ESTADO_NUM,$_POST['txtEstado']);
  62.                 $result = $conex->query($query);
  63.                 if ($result && ($result->num_rows>0)){
  64.                     $fila=$result->fetch_assoc();
  65.                     if ($fila['existe']>0) {
  66.                         $erro="Ese valor xa foi engadido...";
  67.                     }
  68.                     $result->free();
  69.                 }
  70.  
  71.                 if($erro==''){
  72.                     $query=sprintf(INSERIR_ESTADO,$_POST['txtEstado']);
  73.                     $result = $conex->query($query);
  74.                     if ($result){
  75.                         printf("<h2>Valor dado de alta correctamente</h2>");
  76.                         printf("<h3>Nova clave %d</h3>",$conex->insert_id);
  77.                     }
  78.                     else{   // Sempre ten que devolver algo como temos implementado o proc.
  79.                         $erro = 'Houbo un erro na chamada á base de datos';
  80.                     }
  81.                 }
  82.             }  
  83.                
  84.         }  // Fin da operacion de ALTA
  85.        
  86.         ?>        
  87.  
  88.         </form>
  89.        
  90.        
  91.         <?php        
  92.         if ($erro!='') {
  93.             printf("<h3>%s</h3>",$erro);
  94.         }
  95.         $conex->close();  // Pechamos a conexión
  96.        
  97.         ?>        
  98.        
  99.        
  100.     </body>    
  101. </html>
Posible Solución: Utilizando procedementos almacenados
  • A diferenza do caso anterior:
  • O id xerado terá que devolvelo o procedemento, mediante SELECT, un parámetro de SAÍDA ou un RETURN se estamos a utilizar unha función.
Teremos que utilizar a función last_insert_id() para obter o id xerado dende o procedemento de Mysql.
  • A comprobación de se o dato existe o facemos dentro do procedemento.


Procedemento almacenado creado en Mysql

  1. DELIMITER $$
  2.  
  3. CREATE DEFINER=`root`@`localhost` PROCEDURE `INSERIR_ESTADO`(_descripcion varchar(100))
  4. etiqueta:BEGIN  -- Asociamos unha etiqueta ao código do procedemento para que poidamos facer algo parecido a un exit
  5.     declare existe int;
  6.        
  7.         SELECT count(*)
  8.         INTO existe
  9.         FROM ESTADOS
  10.         WHERE upper(descripcion)=upper(_descripcion);
  11.  
  12.         IF existe=1 THEN
  13.                 begin
  14.                         SELECT -1 as valor_devolto;
  15.                         leave etiqueta; -- Para que saia do procedemento
  16.                 end;
  17.         END IF;  -- Fin do IF
  18.  
  19.         INSERT INTO ESTADOS (descripcion)
  20.         VALUES (_descripcion);
  21.  
  22.         SELECT last_insert_id() as valor_devolto;
  23.  
  24. END
  • Liña 4: Utilizamos unha etiqueta para que poidamos utilizar a orde LEAVE xa que Mysql non ten outra forma de poder saír dun procedemento almacenado.
Neste caso en concreto quizais podería ser más aconsellable utilizar unha función, xa que estamos a devolver un único dato (neste caso -1 ou a id xerada). Desta maneira, poñendo un return xa sairiamos da función.
  • Liña 16: Utilizamos a funciuón last_insert_id() para obter o último id xerado da inserción.


  • Comentar que dentro dos xestores de base de datos, nos procedementos/funcións podemos empregar as consultas directamente como expresións, por exemplo, no caso anterior:
  1. DELIMITER $$
  2.  
  3. CREATE DEFINER=`root`@`localhost` PROCEDURE `INSERIR_ESTADO`(_descripcion varchar(100))
  4. etiqueta:BEGIN  -- Asociamos unha etiqueta ao código do procedemento para que poidamos facer algo parecido a un exit
  5.  
  6.         IF      (SELECT count(*) FROM ESTADOS WHERE upper(descripcion)=upper(_descripcion))>0 THEN
  7.                 begin
  8.                         SELECT -1 as valor_devolto;
  9.                         leave etiqueta; -- Para que saia do procedemento
  10.                 end;
  11.         END IF;  -- Fin do IF
  12.  
  13.         INSERT INTO ESTADOS (descripcion)
  14.         VALUES (_descripcion);
  15.  
  16.         SELECT last_insert_id() as valor_devolto;
  17.  
  18. END



Páxina bd.inc.php

  1. define('PROCEDEMENTO_INSERIR_ESTADO',"INSERIR_ESTADO('%s')");


Páxina Xestionar_Estados_Proc.php

  1. <?php
  2.  
  3. require ('/var/www/confBD/bd.inc.php');
  4. //error_reporting(0);
  5.  
  6. $conex = new mysqli(SERVIDOR,USUARIO,PASSWORD,BD);
  7. $conex->set_charset('utf8');
  8.  
  9. if ($conex->connect_error){
  10.      die('Erro de Conexi&oacute;n (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
  11. }
  12. $erro="";   // Variable que a usaremos para amosar os posibles erros que poidan darse
  13. ?>
  14.  
  15.  
  16. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  17.     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  18. <html xmlns="http://www.w3.org/1999/xhtml" lang="es" xml:lang="es">
  19.     <head>
  20.         <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  21.         <title>Xestionar t&aacute;boa Estados BD</title>
  22.     </head>
  23.  
  24.     <body>
  25.         <!-- Esta parte poderíase facer con javascript -->
  26.         <form action="<?php echo $_SERVER['PHP_SELF'] ?>" method='post'>
  27.             <h2>Escolle a operación a facer sobre a táboa ESTADOS</h2>
  28.        
  29.             <input <?php echo (isset($_POST['rbtnOperacion']) && $_POST['rbtnOperacion']=='ALTA') ? "checked='checked'" : "";  ?>
  30.                 onclick='javascript:this.form-submit()' type='radio' name="rbtnOperacion" value='ALTA'>ALTA</input>
  31.             <input <?php echo (isset($_POST['rbtnOperacion']) && $_POST['rbtnOperacion']=='BAIXA') ? "checked='checked'" : "";  ?>
  32.                 onclick='javascript:this.form-submit()'  type='radio' name="rbtnOperacion" value='BAIXA'>BAIXA</input>
  33.             <input <?php echo (isset($_POST['rbtnOperacion']) && $_POST['rbtnOperacion']=='MODIFICACION') ? "checked='checked'" : "";  ?>
  34.                 onclick='javascript:this.form-submit()'  type='radio' name="rbtnOperacion" value='MODIFICACION'>MODIFICACI&Oacute;N</input>
  35.         <?php  
  36.        
  37.         if (!empty($_POST['rbtnOperacion'])){  // Premimos unha opción => Xeramos o formulario
  38.             switch ($_POST['rbtnOperacion']){
  39.                 case 'ALTA':
  40.                     printf("<div>Nome do novo estado:");
  41.                     printf("<input name='txtEstado' type='text' size='50' maxlength='100' /></div>");
  42.                     printf("<div><input name='btnOperacion' type='submit' value='ALTA' />");
  43.                     printf("<input type='reset' value='BORRAR' /></div>");
  44.                     break;
  45.             }
  46.         }
  47.  
  48.  
  49.         // Xestionamos a operacion do segundo formulario => Alta , Baixa ou Modificacion
  50.         if (!empty($_POST['btnOperacion']) && $_POST['btnOperacion']=='ALTA'){  
  51.             if (strlen($_POST['txtEstado'])>100){
  52.                 $erro = ">A lonxitude da cadea non pode ter máis de 100 caracteres...";
  53.             }
  54.             else {
  55.                 // Eliminamos caracteres non permitidos
  56.                 $novoEstado = filter_var($_POST['txtEstado'],FILTER_SANITIZE_STRING);
  57.                 // Escapamos as comillas simples e dobres
  58.                 $novoEstado = $conex->real_escape_string($novoEstado);
  59.                 $proc=sprintf(PROCEDEMENTO_INSERIR_ESTADO,$novoEstado);
  60.                 $proc = sprintf("CALL %s",$proc);
  61.                 if ($conex->multi_query($proc)){
  62.                     $result = $conex->store_result();
  63.                     if ($result && ($result->num_rows>0)){
  64.                         $fila=$result->fetch_assoc();
  65.                         switch($fila['valor_devolto']){
  66.                             case -1:
  67.                                 $erro = "Ese valor xa est&aacute; dado de alta na base de datos...";
  68.                                 break;
  69.                             default:
  70.                                 printf("<h2>Valor dado de alta correctamente</h2>");
  71.                                 printf("<h3>Nova clave %d</h3>",$fila['valor_devolto']);
  72.                         }
  73.                         $result->free();
  74.                     }
  75.                     else{   // Sempre ten que devolver algo como temos implementado o proc.
  76.                         $erro = 'Houbo un erro na chamada á base de datos';
  77.                     }
  78.                 }
  79.                 else{
  80.                     $erro = 'Houbo un erro na chamada á base de datos';
  81.                 }
  82.             }  
  83.                
  84.         }  // Fin da operacion de ALTA
  85.        
  86.         ?>        
  87.         </form>
  88.        
  89.        
  90.         <?php        
  91.         if ($erro!='') {
  92.             printf("<h3>%s</h3>",$erro);
  93.         }
  94.         $conex->close();  // Pechamos a conexión
  95.        
  96.         ?>        
  97.        
  98.        
  99.     </body>    
  100. </html>


  • Neste caso é necesario utilizar a opción multi_query xa que recuperamos un dato que devolve o procedemento.



Como tratar aos caracteres 'estraños'

  • Con caracteres 'estraños' queremos dicir caracteres fora do abecedario e caracteres numéricos, coma por exemplo: ', ,\n,....
  • Aquí debemos diferenciar dous casos.
  • Cando queremos gardar caracteres estraños dentro da base de datos.
  • Cando queremos impedir que o usuario poida utilizar ese tipo de caracteres.


Necesitamos gardar caracteres 'estraños' na base de datos

  • Neste caso, teremos que 'codificar' e 'descodificar' os datos.
Codificaremos os datos cando os gardemos (ou modifiquemos) coa orde SQL INSERT e UPDATE.
Decodificaremos: Pode non ser necesario, dependerá do dato e o que queiramos facer con él.
  • As funcións que podemos empregar para isto son:
  • Función htmlentities: Codifica os caracteres en entidades html. Por exemplo, '<' o codifica como '&lt'. Fixarse nas 'opcións' xa que por defecto codifica as comiñas dobres pero non as simples.
  • Función html_entity_decode: Realiza o proceso inverso ao anterior.
  • Por exemplo, imaxinade que o usuario engade nunha caixa de texto un código HTML que queredes gardar na base de datos, pero que o navegador non debe 'interpretar'. Por exemplo a cadea: <script>alert('ola);</script>
Isto podería ser interpretado como un ataque como xa vimos anteriormente. Codificando a cadea coa función htmlentities isto non é posible.
Cando amosemos o resultado de aplicar a función, non deberemos decodificalo, xa que o que queremos é que o navegador 'amose' a cadea, non que 'interprete' o código e execute o código javascript.


IMPORTANTE: Lembrar que para utilizar esta solución, todos os datos (ou os que nos interesen que poidan ter este tipo de caracteres) deben ser codificados.

Se un usuario fai unha busca sobre algún destes campos, teremos que codificar tamén a cadea escrita polo usuario.



Esta función, aplicada a unha cadea de caracteres, 'escapa' os seguintes caracteres: NUL (ASCII 0), \n, \r, \, ', ", e Control-Z. Isto quere dicir que antepón a ditos caracteres o carácter \ e isto fai que o Mysql interprete o seguinte carácter como o que é.

Nota:Deberemos aplicar sempre esta función para impedir a inxección de código se non aplicamos outra función que 'codifique' as comiñas simples, antes de enviar os datos á base de datos.


Vexamos un exemplo coa seguinte cadea:
$valor = "O'Really";
Queremos facer a seguinte consulta: INSERT INTO AUTORES (nome) VALUES ('$valor')
Se substituímos $valor polo que vale, vemos que temos un problema coas comiñas simples e daría un erro de sintaxe SQL.
  • Para solucionalo,podemos aplicar a función 'real_escape_string' a $valor da forma:
  1. <?php
  2. $mysqli = new mysqli("localhost", "usuario", "contrasinal", "bd");
  3.  
  4. if (mysqli_connect_errno()) {
  5.     printf("Erro na conexión: %s\n", mysqli_connect_error());
  6.     exit();
  7. }
  8.  
  9.  
  10. $valor = "O'Really";
  11.  
  12. /* esta consulta faia debido a que non escapa $cidade */
  13. if (!$mysqli->query("INSERT INTO AUTORES (nome) VALUES ('$valor')")) {
  14.     printf("Erro: %s\n", $mysqli->sqlstate);
  15. }
  16.  
  17. $valor = $mysqli->real_escape_string($valor);
  18.  
  19. /* esta consulta con $valor escapada funcionará */
  20. if (!$mysqli->query("INSERT INTO AUTORES (nome) VALUES ('$valor')")) {
  21.     printf("%d fila inserida.\n", $mysqli->affected_rows);
  22. }
  23.  
  24. $mysqli->close();
  25. ?>

Non queremos que o usuario poida utilizar este tipo de caracteres

Baixa

  • Podemos eliminar unha fila ou un conxunto de filas en base a uns criterios (parte WHERE da cláusula DELETE)
  • Ao igual que fixemos anteriormente, podemos borrar directamente en PHP facendo uso da orde DELETE ou chamar a un procedemento almacenado, enviando os datos necesarios e executando a sentenza.


  • IMPORTANTE: Temos que ter en conta que nas bases de datos relacionais, dar de baixa un rexistro pode implicar borrar rexistros relacionados.
Cando creamos as táboas indicamos as relacións entre elas por medio de claves foráneas, nas que indicamos como queremos que se comporten cando se produce un borrado ou unha modificación. Máis información neste enlace.


Por exemplo, na táboa LIBROS_AUTORES:
  1. CREATE TABLE `LIBROS_AUTORES` (
  2.   `libro_id` int(11) NOT NULL,
  3.   `autor_id` int(11) NOT NULL,
  4.   PRIMARY KEY (`libro_id`,`autor_id`),
  5.   KEY `fk_LIBROS_AUTORES_AUTORES_idx` (`autor_id`),
  6.   CONSTRAINT `fk_LIBROS_AUTORES_AUTORES` FOREIGN KEY (`autor_id`) REFERENCES `AUTORES` (`id_autor`) ON DELETE NO ACTION ON UPDATE CASCADE,
  7.   CONSTRAINT `fk_LIBROS_AUTORES_LIBROS` FOREIGN KEY (`libro_id`) REFERENCES `LIBROS` (`id_libro`) ON DELETE NO ACTION ON UPDATE CASCADE
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish2_ci;
No exemplo estamos a indicar que en caso de borrar unha fila de táboa AUTORES, non se vai permitir se existen filas na táboa LIBROS_AUTORES.
Se actualizamos a clave primaria da táboa AUTORES, actualizará a clave na táboa AUTORES_LIBROS (parte ON UPDATE CASCADE).


  • Polo tanto, será tarefa do programador comprobar se os datos están sendo utilizados noutras táboas e avisar en caso de que o usuario intente borralos ou ben permitir borrados en cascada ou ben capturar o erro que dará o mysql en caso de intentar borrar datos relacionados e informar ao usuario do problema.


Caso Práctico: Baixa

  • Imos implementar esta opción na páxina Xestionar_Estados.php.


Cargaremos nunha <select> todos os estados e premendo o botón 'Baixa' daremos de baixa o estado seleccionado.
Faremos uso dun procedemento almacenado de nome BORRAR_ESTADO que terá como parámetro o id do estado a borrar e fará a sentenza sql DELETE.
Faremos uso do procedemento almacenado 'CONSULTAR_ESTADOS' que devolverá nun select o id e descrición dos estados para cargar na lista.
Nesta solución non comprobamos se xa existen datos relacionados con ese estado antes de borralos e usaremos como base o código feito na solución Xestionar_Estados_Proc.php feito anteriormente.


  • Se estamos a utilizar procedementos almacenados, lembrar que en caso de erro '2014 - Commands out of sync; you can't run this command now', debemos engadir a seguinte liña despois de chamar a un procedemento almacenado:
  1.                     if ($conex->multi_query($proc)){
  2.                         $result = $conex->store_result();
  3.                         if ($result && ($result->num_rows>0)){
  4.                             while($fila=$result->fetch_assoc()){
  5.                                 // Procesamos cada fila
  6.                             }
  7.                             $result->free();
  8.                         }
  9.                         while($conex->next_result() && $conex->store_result());


  • A xestión do erro en caso de querer borrar un rexistro relacionado a imos facer na páxina PHP comprobando que o valor numérico do erro sexa o 1451, que se corresponde co erro 'Cannot delete or update a parent row: a foreign key constraint fails'.
Lembrar que dito control poderíamos facelo no propio procedemento almacenado capturando a excepción ou comprobando cun select previo se existe o rexistro relacionado.


  • Nota: Lembrar que nesta solución estamos a utilizar a chamada a multi_query pero se podería utilizar a chamada a query no caso de que o procedemento non devolva resultados (teña un select que queiramos recoller en PHP).


Posible solución Caso Práctico: Baixa


Procedemento almacenado en Mysql: CONSULTAR_ESTADOS

  1. DELIMITER $$
  2.  
  3. CREATE DEFINER=`root`@`localhost` PROCEDURE `CONSULTAR_ESTADOS`()
  4. BEGIN
  5.         SELECT id_estado,descripcion
  6.         FROM ESTADOS
  7.         ORDER BY descripcion;
  8.  
  9. END


Procedemento almacenado en Mysql: BORRAR_ESTADO

  1. DELIMITER $$
  2.  
  3. CREATE DEFINER=`root`@`localhost` PROCEDURE `BORRAR_ESTADO`(_id_estado int)
  4. BEGIN
  5.  
  6.         DELETE FROM ESTADOS
  7.         WHERE id_estado = _id_estado;
  8.  
  9. END


Arquivo bd.inc.php

  1. define ('PROCEDEMENTO_BORRAR_ESTADO',"BORRAR_ESTADO(%d)");
  2. define ('PROCEDEMENTO_CONSULTAR_ESTADOS',"CONSULTAR_ESTADOS()");



Arquivo Xestionar_Estados_Proc.php

  1. <?php
  2.  
  3. require ('/var/www/confBD/bd.inc.php');
  4. //error_reporting(0);
  5.  
  6. $conex = new mysqli(SERVIDOR,USUARIO,PASSWORD,BD);
  7. $conex->set_charset('utf8');
  8.  
  9. if ($conex->connect_error){
  10.      die('Erro de Conexi&oacute;n (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
  11. }
  12. $erro="";   // Variable que a usaremos para amosar os posibles erros que poidan darse
  13. ?>
  14.  
  15.  
  16. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  17.     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  18. <html xmlns="http://www.w3.org/1999/xhtml" lang="es" xml:lang="es">
  19.     <head>
  20.         <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  21.         <title>Xestionar t&aacute;boa Estados BD</title>
  22.     </head>
  23.  
  24.     <body>
  25.         <!-- Esta parte poderíase facer con javascript -->
  26.         <form action="<?php echo $_SERVER['PHP_SELF'] ?>" method='post'>
  27.             <h2>Escolle a operación a facer sobre a táboa ESTADOS</h2>
  28.             <input <?php echo (isset($_POST['rbtnOperacion']) && $_POST['rbtnOperacion']=='ALTA') ? "checked='checked'" : "";  ?>
  29.                 onclick='javascript:this.form-submit()' type='radio' name="rbtnOperacion" value='ALTA'>ALTA</input>
  30.             <input <?php echo (isset($_POST['rbtnOperacion']) && $_POST['rbtnOperacion']=='BAIXA') ? "checked='checked'" : "";  ?>
  31.                 onclick='javascript:this.form-submit()'  type='radio' name="rbtnOperacion" value='BAIXA'>BAIXA</input>
  32.             <input <?php echo (isset($_POST['rbtnOperacion']) && $_POST['rbtnOperacion']=='MODIFICACION') ? "checked='checked'" : "";  ?>
  33.                 onclick='javascript:this.form-submit()'  type='radio' name="rbtnOperacion" value='MODIFICACION'>MODIFICACI&Oacute;N</input>
  34.         <?php  
  35.  
  36.         // Operación BAIXA => TEN QUE IR ANTES XA QUE DESPOIS CARGAMOS A LISTA COS ESTADOS!!!
  37.         if (!empty($_POST['btnOperacion']) && $_POST['btnOperacion']=='BAIXA'){  
  38.             $id_estado = $_POST['lstEstados'];
  39.             if (!filter_var($id_estado,FILTER_VALIDATE_INT)){
  40.                 $error='<h4>O estado indicado non &eacute; v&aacute;lido...</h4>';
  41.             }
  42.             else {
  43.                 $proc = sprintf(PROCEDEMENTO_BORRAR_ESTADO,$id_estado);
  44.                 $proc = sprintf("CALL %s",$proc);
  45.                 if ($conex->multi_query($proc)){
  46.                     printf("<h4>Num. rexistros borrados:%d</h4>", $conex->affected_rows);
  47.                     echo "<script>alert('Rexistro dado de baixa correctamente');</script>";
  48.                     while($conex->next_result() && $conex->store_result());
  49.                 }
  50.                 else{
  51.                     echo "<h5>Falló CALL: (" . $conex->errno . ") " . $conex->error . "</h5>";
  52.                     if ($conex->errno==1451){   // Clave foránea relacionada
  53.                         echo "<script>alert('O rexistro non se pode dar de baixa. Está sendo utilizado por outras táboas.');</script>";
  54.                     }
  55.                 }
  56.             }
  57.         }  // Fin da operación de BAIXA
  58.        
  59.        
  60.         // CARGAMOS AS DIFERENTES OPCIONS DE FORMULARIO EN BASE A OPERACION ESCOLLIDA DOS RADIOBUTTON: ALTA-BAIXA-MODIFICACION
  61.         if (!empty($_POST['rbtnOperacion'])){  // Premimos unha opción => Xeramos o formulario
  62.             switch ($_POST['rbtnOperacion']){
  63.                 case 'ALTA':
  64.                     printf("<div>Nome do novo estado:");
  65.                     printf("<input name='txtEstado' type='text' size='50' maxlength='100' /></div>");
  66.                     printf("<div><input name='btnOperacion' type='submit' value='ALTA' />");
  67.                     printf("<input type='reset' value='BORRAR' /></div>");
  68.                     break;
  69.                 case 'BAIXA':
  70.                     printf("<div>Lista de estados:");
  71.                     $proc = sprintf("CALL %s",PROCEDEMENTO_CONSULTAR_ESTADOS);
  72.                     printf("<select name='lstEstados'>");
  73.                     if ($conex->multi_query($proc)){
  74.                         $result = $conex->store_result();
  75.                         if ($result && ($result->num_rows>0)){
  76.                             while($fila=$result->fetch_assoc()){
  77.                                 printf("<option value='%d'>%s</option>",$fila['id_estado'],$fila['descripcion']);
  78.                             }
  79.                             $result->free();
  80.                         }
  81.                         while($conex->next_result() && $conex->store_result());
  82.                     }
  83.                     printf("</select>");
  84.                     printf("</div>");
  85.                     printf("<div><input name='btnOperacion' type='submit' value='BAIXA' />");
  86.                     break;
  87.             }
  88.         }
  89.         ?>        
  90.         </form>
  91.        
  92.         <?php
  93.         // Xestionamos a operacion do segundo formulario => Alta
  94.         if (!empty($_POST['btnOperacion']) && $_POST['btnOperacion']=='ALTA'){  
  95.             if (strlen($_POST['txtEstado'])>100){
  96.                 $erro = ">A lonxitude da cadea non pode ter máis de 100 caracteres...";
  97.             }
  98.             else {
  99.                 // Eliminamos caracteres non permitidos
  100.                 $novoEstado = filter_var($_POST['txtEstado'],FILTER_SANITIZE_STRING);
  101.                 // Escapamos as comillas simples e dobres
  102.                 $novoEstado = $conex->real_escape_string($novoEstado);
  103.                 $proc=sprintf(PROCEDEMENTO_INSERIR_ESTADO,$_POST['txtEstado']);
  104.                 $proc = sprintf("CALL %s",$proc);
  105.                 if ($conex->multi_query($proc)){
  106.                     $result = $conex->store_result();
  107.                     if ($result && ($result->num_rows>0)){
  108.                         $fila=$result->fetch_assoc();
  109.                         switch($fila['valor_devolto']){
  110.                             case -1:
  111.                                 $erro = "Ese valor xa est&aacute; dado de alta na base de datos...";
  112.                                 break;
  113.                             default:
  114.                                 printf("<h2>Valor dado de alta correctamente</h2>");
  115.                                 printf("<h3>Nova clave %d</h3>",$fila['valor_devolto']);
  116.                                
  117.                                 break;
  118.                         }
  119.                         $result->free();
  120.                     }
  121.                     else{   // Sempre ten que devolver algo como temos implementado o proc.
  122.                         $erro = 'Houbo un erro na chamada á base de datos';
  123.                     }
  124.                     while($conex->next_result() && $conex->store_result());
  125.                 }
  126.                 else{
  127.                     $erro = 'Houbo un erro na chamada á base de datos';
  128.                 }
  129.             }  
  130.                
  131.         }  // Fin da operacion de ALTA
  132.        
  133.        
  134.        
  135.         ?>
  136.         <?php        
  137.         if ($erro!='') {
  138.             printf("<h3>%s</h3>",$erro);
  139.         }
  140.         $conex->close();  // Pechamos a conexión
  141.        
  142.         ?>        
  143.        
  144.        
  145.     </body>    
  146. </html>

Modificación

  • Ao igual que no caso INSERT, se modificamos as claves primarias, farase o action recollido na creación das táboas.
Normalmente non se debe modificar a clave primaria das táboas e o que modificaremos será algún dos atributos da táboa.


  • A orde SQL UPDATE levará como parámetros os novos valores a modificar e enviarase a clave primaria para que poida buscar a fila concreta que vai modificarse.
Neste exercicio dita función debería devolver sempre 1, pero podería aplicarse a outros exemplo no que queremos informar ao usuario de cantos rexistros foron afectados pola opción.


  • Nota: Lembrar que nesta solución estamos a utilizar a chamada a multi_query pero se podería utilizar a chamada a query no caso de que o procedemento non devolva resultados (teña un select que queiramos recoller en PHP).



Caso Práctico: Modificación

  • Seguindo có noso exemplo imos a rematar a páxina 'Xestionar_Estados_Proc.php' engadindo a funcionalidade para modificar os estados.
Cando se seleccione esta opción aparecerá unha lista. Deberemos escoller un estado da lista e teremos unha caixa de texto para darlle o novo valor ao elemento seleccionado.
Ao premer o botón 'Modificar' modificarase o estado do elemento seleccionado.
Comprobaremos antes de facer a modificación, que estea seleccionado un elemento da lista.


Nota: Neste curso non entramos en temas de 'presentación' e 'javascript/jquery'. Por iso a 'presentación' de cara a un usuario final non é a adecuada.

Neste caso poderíamos implementar, con 'javascript', que o usuario teña que seleccionar un elemento da lista antes de premer o botón 'Modificar'.
Tamén poderíamos implementar con jquery/javascript unha lista 'editable'.


Posible solución Caso Práctico: Modificación

Procedemento almacenado de Mysql MODIFICAR_ESTADO

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `MODIFICAR_ESTADO`(_id_estado int,_descripcion varchar(100))
BEGIN

        UPDATE ESTADOS
        SET descripcion=_descripcion
        WHERE id_estado = _id_estado;

END



Arquivo: bd.inc.php

  1. define ('PROCEDEMENTO_MODIFICAR_ESTADO',"BORRAR_ESTADO(%d,'%s')");



Arquivo: Xestionar_Estados_Proc.php

  1. <?php
  2.  
  3. require ('/var/www/confBD/bd.inc.php');
  4. //error_reporting(0);
  5.  
  6. $conex = new mysqli(SERVIDOR,USUARIO,PASSWORD,BD);
  7. $conex->set_charset('utf8');
  8.  
  9. if ($conex->connect_error){
  10.      die('Erro de Conexi&oacute;n (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
  11. }
  12. $erro="";   // Variable que a usaremos para amosar os posibles erros que poidan darse
  13. ?>
  14.  
  15.  
  16. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  17.     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  18. <html xmlns="http://www.w3.org/1999/xhtml" lang="es" xml:lang="es">
  19.     <head>
  20.         <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  21.         <title>Xestionar t&aacute;boa Estados BD</title>
  22.     </head>
  23.  
  24.     <body>
  25.         <!-- Esta parte poderíase facer con javascript -->
  26.         <form action="<?php echo $_SERVER['PHP_SELF'] ?>" method='post'>
  27.             <h2>Escolle a operación a facer sobre a táboa ESTADOS</h2>
  28.             <input <?php echo (isset($_POST['rbtnOperacion']) && $_POST['rbtnOperacion']=='ALTA') ? "checked='checked'" : "";  ?>
  29.                 onclick='javascript:this.form-submit()' type='radio' name="rbtnOperacion" value='ALTA'>ALTA</input>
  30.             <input <?php echo (isset($_POST['rbtnOperacion']) && $_POST['rbtnOperacion']=='BAIXA') ? "checked='checked'" : "";  ?>
  31.                 onclick='javascript:this.form-submit()'  type='radio' name="rbtnOperacion" value='BAIXA'>BAIXA</input>
  32.             <input <?php echo (isset($_POST['rbtnOperacion']) && $_POST['rbtnOperacion']=='MODIFICACION') ? "checked='checked'" : "";  ?>
  33.                 onclick='javascript:this.form-submit()'  type='radio' name="rbtnOperacion" value='MODIFICACION'>MODIFICACI&Oacute;N</input>
  34.         <?php  
  35.  
  36.         // Operación BAIXA => TEN QUE IR ANTES XA QUE DESPOIS CARGAMOS A LISTA COS ESTADOS!!!
  37.         if (!empty($_POST['btnOperacion']) && $_POST['btnOperacion']=='BAIXA'){  
  38.             $id_estado = $_POST['lstEstados'];
  39.             if (!filter_var($id_estado,FILTER_VALIDATE_INT)){
  40.                 $error='<h4>O estado indicado non &eacute; v&aacute;lido...</h4>';
  41.             }
  42.             else {
  43.                 $proc = sprintf(PROCEDEMENTO_BORRAR_ESTADO,$id_estado);
  44.                 $proc = sprintf("CALL %s",$proc);
  45.                 if ($conex->multi_query($proc)){
  46.                     printf("<h4>Num. rexistros borrados:%d</h4>", $conex->affected_rows);
  47.                     echo "<script>alert('Rexistro dado de baixa correctamente');</script>";
  48.                     while($conex->next_result() && $conex->store_result());
  49.                 }
  50.                 else{
  51.                     echo "<h5>Falló CALL: (" . $conex->errno . ") " . $conex->error . "</h5>";
  52.                     if ($conex->errno==1451){   // Clave foránea relacionada
  53.                         echo "<script>alert('O rexistro non se pode dar de baixa. Está sendo utilizado por outras táboas.');</script>";
  54.                     }
  55.                 }
  56.             }
  57.         }  // Fin da operación de BAIXA
  58.        
  59.         // OPERACION DE MODIFICAR
  60.         if (!empty($_POST['btnOperacion']) && $_POST['btnOperacion']=='MODIFICAR'){  
  61.             if (empty($_POST['lstEstados'])){
  62.                 $erro='É necesario seleccionar un estado da lista...';
  63.             }
  64.             else {
  65.                 $id_estado = $_POST['lstEstados'];
  66.                 if (!filter_var($id_estado,FILTER_VALIDATE_INT)){
  67.                     $erro='<h4>O estado indicado non &eacute; v&aacute;lido...</h4>';
  68.                 }
  69.                 elseif (strlen($_POST['txtEstado'])>100){
  70.                         $erro = ">A lonxitude da cadea non pode ter máis de 100 caracteres...";
  71.                 }
  72.                 else {
  73.                     // Eliminamos caracteres non permitidos
  74.                     $novoEstado = filter_var($_POST['txtEstado'],FILTER_SANITIZE_STRING);
  75.                     // Escapamos as comillas simples e dobres
  76.                     $novoEstado = $conex->real_escape_string($novoEstado);
  77.                     $proc=sprintf(PROCEDEMENTO_MODIFICAR_ESTADO,$id_estado,$_POST['txtEstado']);
  78.                     $proc = sprintf("CALL %s",$proc);
  79.                     if ($conex->multi_query($proc)){
  80.                         printf("<h4>Num. rexistros modificados:%d</h4>", $conex->affected_rows);
  81.                         while($conex->next_result() && $conex->store_result());
  82.                     }
  83.                     else{
  84.                         echo "<h5>Falló CALL: (" . $conex->errno . ") " . $conex->error . "</h5>";
  85.                     }
  86.  
  87.                     }
  88.                 }
  89.         }  // Fin da operación de MODIFICAR
  90.        
  91.  
  92.  
  93.         // CARGAMOS AS DIFERENTES OPCIONS DE FORMULARIO EN BASE A OPERACION ESCOLLIDA DOS RADIOBUTTON: ALTA-BAIXA-MODIFICACION
  94.         if (!empty($_POST['rbtnOperacion'])){  // Premimos unha opción => Xeramos o formulario
  95.             switch ($_POST['rbtnOperacion']){
  96.                 case 'ALTA':
  97.                     printf("<div>Nome do novo estado:");
  98.                     printf("<input name='txtEstado' type='text' size='50' maxlength='100' /></div>");
  99.                     printf("<div><input name='btnOperacion' type='submit' value='ALTA' />");
  100.                     printf("<input type='reset' value='BORRAR' /></div>");
  101.                     break;
  102.                 case 'BAIXA':
  103.                     printf("<div>Lista de estados:");
  104.                     $proc = sprintf("CALL %s",PROCEDEMENTO_CONSULTAR_ESTADOS);
  105.                     printf("<select name='lstEstados'>");
  106.                     if ($conex->multi_query($proc)){
  107.                         $result = $conex->store_result();
  108.                         if ($result && ($result->num_rows>0)){
  109.                             while($fila=$result->fetch_assoc()){
  110.                                 printf("<option value='%d'>%s</option>",$fila['id_estado'],$fila['descripcion']);
  111.                             }
  112.                             $result->free();
  113.                         }
  114.                         while($conex->next_result() && $conex->store_result());
  115.                     }
  116.                     printf("</select>");
  117.                     printf("</div>");
  118.                     printf("<div><input name='btnOperacion' type='submit' value='BAIXA' />");
  119.                     break;
  120.                 case 'MODIFICACION':
  121.                     printf("<div>");
  122.                     $proc = sprintf("CALL %s",PROCEDEMENTO_CONSULTAR_ESTADOS);
  123.                     printf("<select name='lstEstados' size='10'>");
  124.                     if ($conex->multi_query($proc)){
  125.                         $result = $conex->store_result();
  126.                         if ($result && ($result->num_rows>0)){
  127.                             while($fila=$result->fetch_assoc()){
  128.                                 printf("<option value='%d'>%s</option>",$fila['id_estado'],$fila['descripcion']);
  129.                             }
  130.                             $result->free();
  131.                         }
  132.                         while($conex->next_result() && $conex->store_result());
  133.                     }
  134.                     printf("</select>");
  135.                     printf("Novo valor: <input name='txtEstado' type='text' size='50' maxlength='100' />");
  136.                     printf("</div>");
  137.                     printf("<div><input name='btnOperacion' type='submit' value='MODIFICAR' />");
  138.                     break;
  139.                
  140.             }
  141.         }
  142.         ?>        
  143.         </form>
  144.        
  145.         <?php
  146.         // Xestionamos a operacion do segundo formulario => Alta
  147.         if (!empty($_POST['btnOperacion']) && $_POST['btnOperacion']=='ALTA'){  
  148.             if (strlen($_POST['txtEstado'])>100){
  149.                 $erro = ">A lonxitude da cadea non pode ter máis de 100 caracteres...";
  150.             }
  151.             else {
  152.                 // Eliminamos caracteres non permitidos
  153.                 $novoEstado = filter_var($_POST['txtEstado'],FILTER_SANITIZE_STRING);
  154.                 // Escapamos as comillas simples e dobres
  155.                 $novoEstado = $conex->real_escape_string($novoEstado);
  156.                 $proc=sprintf(PROCEDEMENTO_INSERIR_ESTADO,$_POST['txtEstado']);
  157.                 $proc = sprintf("CALL %s",$proc);
  158.                 if ($conex->multi_query($proc)){
  159.                     $result = $conex->store_result();
  160.                     if ($result && ($result->num_rows>0)){
  161.                         $fila=$result->fetch_assoc();
  162.                         switch($fila['valor_devolto']){
  163.                             case -1:
  164.                                 $erro = "Ese valor xa est&aacute; dado de alta na base de datos...";
  165.                                 break;
  166.                             default:
  167.                                 printf("<h2>Valor dado de alta correctamente</h2>");
  168.                                 printf("<h3>Nova clave %d</h3>",$fila['valor_devolto']);
  169.                                
  170.                                 break;
  171.                         }
  172.                         $result->free();
  173.                     }
  174.                     else{   // Sempre ten que devolver algo como temos implementado o proc.
  175.                         $erro = 'Houbo un erro na chamada á base de datos';
  176.                     }
  177.                     while($conex->next_result() && $conex->store_result());
  178.                 }
  179.                 else{
  180.                     $erro = 'Houbo un erro na chamada á base de datos';
  181.                 }
  182.             }  
  183.                
  184.         }  // Fin da operacion de ALTA
  185.        
  186.        
  187.        
  188.         ?>
  189.         <?php        
  190.         if ($erro!='') {
  191.             printf("<h3>%s</h3>",$erro);
  192.         }
  193.         $conex->close();  // Pechamos a conexión
  194.        
  195.         ?>        
  196.        
  197.        
  198.     </body>    
  199. </html>

Transaccións

  • En certas ocasións imos necesitar facer varias operacións á vez nas táboas da base de datos.
  • Por exemplo, no exemplo que estamos a desenvolver:
  • Podemos crear unha páxina PHP cun formulario para dar de alta un libro xunto cos exemplares.
  • Podemos crear unha páxina PHP que relacione libros xunto cos autores que o escribiron.
  • Podemos dar de baixa un libro. Isto leva consigo dar de baixa o libro máis os exemplares máis os datos da táboas LIBROS_AUTORES.
  • Outro caso típico é o carrito da compra. Cando o usuario remata o proceso de compra e procede a realizar o pedido, necesitaremos gardar os produtos que conforman o pedido na nosa base de datos.
Queremos que se garden todos os produtos.


Normalmente imos queres que ditas operacións se fagan todas e en caso de erro, necesitaremos volver ao estado anterior ao comezo do inicio das operacións.
  • Isto o podemos facer grazas as TRANSACCIÓNS.
Unha transacción nos garante que o conxunto de operacións que están dentro dunha transacción se farán todas ou non se fará ningunha.
  • Normalmente cada operación individual é 'considerada' coma unha transacción en si mesma, de tal forma o ou ten éxito ou os cambios non se gardan na base de datos.
  • Máis información sobre Transaccións:



  • Cando falamos de transaccións outro factor a ter en conta é o chamado Nivel de illamento.
Imaxinemos que estamos dentro dunha transacción e estou lendo o número de filas dunha táboa. Se outro usuario engade unha nova fila despois de que eu lera o número, e eu dentro da transacción volvo a ler o número de filas. Terei dúas cantidades diferentes dentro da mesma transacción.
Para evitalo podemos facer que a táboa que estou a utilizar dentro da transacción quede 'bloqueada' e non se poida modificar ata que eu remate.
Este e outros problemas que poden darse no uso de transaccións se intentan evitar cun nivel de illamento determinado.
Cada nivel de illamento soluciona algún dos problemas que se poden dar.
  • Máis información sobre Niveis de illamento en Mysql:



  • Dos niveis de illamento (que se basean en bloquear rexistros de táboas) se pode provocar outro problema, que é o Interbloqueo ou Deadlock.
Isto prodúcese cando unha transacción_1 está esperando o acceso a un recurso que está sendo bloqueado por outra transacción_2 e esta outra transacción_2 está esperando por un recurso que está sendo bloqueado pola transacción_1.


  • En PHP, para poder utilizar transaccións necesitamos:


  • Unha transacción a podemos facer dende a páxina PHP ou dende o xestor Mysql dentro dun procedemento almacenado.
  • Para realizar unha transacción o proceso sempre é o mesmo en todas as linguaxes e xestores.
  1. Iniciar a transacción. Normalmente farase coa orde 'START TRANSACTION' (outros xestores teñen coma orde BEGIN TRANSACTION)
  2. Realizar as operacións que conforman as transaccións.
  3. En caso de erro en algunha das operacións anteriores executar a orde 'ROLLBACK' e saír
  4. En caso de que todo se execute correctamente, entón confirmamos a transacción e os cambios se gardan definitivamente na base de datos, coa orde: 'COMMIT'


Transaccións en PHP

  • Para iniciar unha transacción temos dúas opcións:
  • Inhabilitar as transaccións implícitas:
  1. $conex= new mysqli(HOST, USER, PASSWORD, BD);
  2. $conex->autocommit(FALSE);
Desta forma as operacións que vaian a continuación desta orde non se farán efectivas ata atopar un commit.
NECESITAMOS QUE A VERSIÓN PHP SEXA 5.5 OU SUPERIOR.
  1. $conex= new mysqli(HOST, USER, PASSWORD, BD);
  2. $conex->begin_transaction(MYSQLI_TRANS_START_READ_ONLY);


Esta segunda forma leva como parámetro unha bandeira que vai indicar que dentro da transacción non vai poderse facer operacións que modifiquen os contidos das táboas da base de datos.
Máis información neste enlace.


  • Unha vez iniciada a transacción faremos as operacións necesarias sobre a base de datos. En caso de erro nalgunha delas, teremos que facer un rollback:
  1. $conex = new mysqli(SERVIDOR,USUARIO,PASSWORD,BD);
  2. $conex->set_charset('utf8');
  3.  
  4. if ($conex->connect_error){
  5.      die('Erro de Conexi&oacute;n (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
  6. }
  7.  
  8. $result=$conex->query("INSERT INTO TABLE_1 (col2) VALUES ('valor')");
  9. if ($result){
  10.   // Operación correcta
  11.   printf ("Novo rexistro có id %d.\n", $conex->insert_id);
  12. }
  13. else {
  14.   $conex->rollback();
  15.   $conex->close();
  16.   die 'Erro na execución da operación';
  17. }
  18.  
  19.  
  20. $result=$conex->query("INSERT INTO TABLE_2 (col2) VALUES ('valor')");
  21. if ($result){
  22.   // Operación correcta
  23.   printf ("Novo rexistro có id %d.\n", $conex->insert_id);
  24. }
  25. else {
  26.   $conex->rollback();
  27.   $conex->close();
  28.   die 'Erro na execución da operación';
  29. }


  • Unha vez rematadas as operacións facemos o commit:
  1. $conex->commit();


  • Se establecimos autocommit(false) deberemos volvelo a establecer en True.
  1. $conex->autocommit(true);


  • Ao rematar todas as operacións sobre a base de datos pecharemos a conexión.
  1. $conex->close();

Transaccións en MYSQL dentro de procedementos almacenados

  • Outra forma de realizar as transaccións é que poidamos enviar os datos a un procedemento almacenado de Mysql e que dende o procedemento se faga a transacción.
  • O proceso é moi parecido ao feito dende PHP.
Lembrar que se o facemos dende o propio procedemento NON TEMOS QUE FACELO dende PHP.
  1. DELIMITER $$
  2.  
  3. CREATE PROCEDURE `PROCEDEMENTO_CON_TRANSACCION`(_param1 int,_param2 varchar(50),_param3 varchar(200))
  4. BEGIN
  5.         DECLARE EXIT HANDLER FOR SQLEXCEPTION
  6.         BEGIN
  7.                 ROLLBACK;
  8.                 SELECT -1 as error;
  9.         END;
  10.  
  11.         START TRANSACTION;
  12.  
  13.         INSERT INTO TABLE_1(col1,col2)
  14.         VALUES (_param2,_param3);  -- Supoñemos que esta táboa devolve un valor clave autonumérico.
  15.  
  16.         INSERT INTO TABLE_2(col1,col2)
  17.         VALUES (_param1,last_insert_id());
  18.  
  19.        
  20.         COMMIT;
  21.         SELECT 0 AS error;
  22.  
  23. END
  24. $$
  • Liñas 5-9: Capturamos calquera erro que se produza e nese caso enviamos a PHP un código -1 no select facendo un rollback da transacción.
  • Liña 11: Iniciamos a transacción.
  • Liñas 20 e 21: Confirmamos a transacción se todo foi ben e enviamos un 0 como código ao PHP.


  • Comentar que se facemos as transaccións dende procedementos almacenados teremos que capturar as posibles excepcións que se produzan para facer o rollback.
Máis información: Captura de erros en Mysql.


Caso práctico: Transaccións en MYSQL dentro de procedementos almacenados

  • Imos crear un procedemento almacenado que dea de alta a un único autor xunto co libro.
Partimos da base que xa coñecemos o id do libro e enviamos o nome e apelidos do autor.
Isto leva consigo que o procedemento vai ter que dar de alta datos en dúas táboas: AUTORES e LIBROS_AUTORES.
  1. DELIMITER $$
  2.  
  3. CREATE DEFINER=`root`@`localhost` PROCEDURE `INSERIR_AUTORES_LIBRO`(_id_libro int,_nome varchar(50),_apelido varchar(200))
  4. BEGIN
  5.         DECLARE EXIT HANDLER FOR SQLEXCEPTION
  6.         BEGIN
  7.                 ROLLBACK;
  8.                 SELECT -1 as error;
  9.         END;
  10.  
  11.         START TRANSACTION;
  12.  
  13.         INSERT INTO AUTORES(nome,apelido)
  14.         VALUES (_nome,_apelido);
  15.  
  16.         INSERT INTO LIBROS_AUTORES (libro_id,autor_id)
  17.         VALUES (_id_libro,last_insert_id());
  18.        
  19.         COMMIT;
  20.         SELECT 0 AS error;
  21.  
  22. END
  23. $$

Caso práctico: Transaccións

Exercicio Proposto: Asociar Libros a Autores.

A idea é escoller dunha lista (<select>) un libro.
Unha vez escollido e premido o botón 'AUTORES' desaparecerá a lista.
Se indicará nun header o nome do libro e aparecerá nunha lista na que se van poder seleccionar varios autores, os autores da base de datos.
Seleccionaremos un ou varios autores e premeremos o botón de ALTA.
Isto levará consigo as operacións de alta sobre a táboa LIBROS_AUTORES de cada un dos autores escollidos.


  • Melloras:
  • Posibilidade de dar de alta a novos autores. Estes autores non aparecerán la lista ata que se dean de alta.
  • Polo tanto aparecerán nunha checkboxlist con opción para ser eliminados (non da base de datos xa que aínda non foron engadidos). Poderiades gardar os datos nun array dentro dunha variable de sesión.
  • Ao premer o botón de ALTA, engadiranse á base de datos e se asociarán ao libro seleccionado.
  • Isto levará consigo as operacións de alta sobre a táboa AUTORES e LIBROS_AUTORES de cada un dos autores.



Exercicio proposto/resolto

  • Imos crear unha páxina de nome Alta_Libro_Exemplares.php.
Dita páxina terá un formulario onde indicaremos o título do libro e o número de exemplares que queremos dar de alta.
Ao premer o botón de 'Alta' aparecerán en forma de lista (o podedes implementar cunha táboa) un formulario (unha por cada exemplar) cos datos: imaxe-prezo-estado.
Os datos anteriores (título e número de exemplares) non se poderán cambiar.
  • Os campos do formulario serán dos seguintes tipo:
  • Estado: Será un combobox (select en html) cos estados da táboa estados.
  • Imaxe: cambiaremos isto máis adiante. Por agora deixade un campo de texto.
  • Prezo: Prezo da imaxe.
  • Como sempre deberedes validar os datos e que teñan o tipo/formato correcto.
  • No segundo formulario aparecerá un botón de 'Alta' e outro de 'Cancelar'.
  • Ao premer o botón de alta daremos de alta o libro, obteremos o id autoxerado e usaremos ese id para dar de alta aos exemplares.
  • Se prememos o botón 'Cancelar' desaparecerá o segundo formulario e limparase o primeiro para que poidamos dar de alta un novo libro.


  • POSIBLE SOLUCIÓN (algúns dos procedementos xa foron creados en solucións anteriores)

MYSQL

Procedemento: CONSULTAR_ESTADOS

  1. DELIMITER $$
  2. CREATE DEFINER=`root`@`localhost` PROCEDURE `CONSULTAR_ESTADOS`()
  3. BEGIN
  4.         SELECT id_estado,descripcion
  5.         FROM ESTADOS
  6.         ORDER BY descripcion;
  7.  
  8. END



Procedemento: INSERIR_LIBRO

  1. DELIMITER $$
  2.  
  3. CREATE DEFINER=`root`@`localhost` PROCEDURE `INSERIR_LIBRO`(_titulo varchar(300))
  4. etiqueta:BEGIN  -- Asociamos unha etiqueta ao código do procedemento para que poidamos facer algo parecido a un exit
  5.     declare existe int;
  6.        
  7.         SELECT count(*)
  8.         INTO existe
  9.         FROM LIBROS
  10.         WHERE upper(titulo)=upper(_titulo);
  11.  
  12.         IF existe=1 THEN
  13.                         begin
  14.                            SELECT -1 as valor_devolto;
  15.                            leave etiqueta; -- Para que saia do procedemento
  16.                         end;
  17.     END IF;  -- Fin do IF
  18.  
  19.         INSERT INTO LIBROS (titulo)
  20.         VALUES (_titulo);
  21.  
  22.         SELECT last_insert_id() as valor_devolto;
  23. END



Procedemento: INSERIR_EXEMPLAR

  1. DELIMITER $$
  2. CREATE DEFINER=`root`@`localhost` PROCEDURE `INSERIR_EXEMPLAR`(_libro_id int,_id_exemplar int,_imaxe varchar(100),_prezo decimal(6,2),_estado_id int)
  3. BEGIN
  4.         INSERT INTO EXEMPLARES (libro_id,id_exemplar,imaxe,prezo,estado_id)
  5.         VALUES (_libro_id,_id_exemplar,_imaxe,_prezo,_estado_id);
  6.  
  7. END
  8. $$



Arquivo bd.inc.php:

  1. define ('PROCEDEMENTO_INSERIR_LIBRO',"INSERIR_LIBRO('%s')");
  2. define ('PROCEDEMENTO_INSERIR_EXEMPLAR',"INSERIR_EXEMPLAR(%d,%d,'%s',%f,%d)");



Arquivo Alta_Libro_Exemplares.php

  1. <?php
  2.  
  3. require ('/var/www/confBD/bd.inc.php');
  4. //error_reporting(0);
  5.  
  6. $conex = new mysqli(SERVIDOR,USUARIO,PASSWORD,BD);
  7. $conex->set_charset('utf8');
  8.  
  9. if ($conex->connect_error){
  10.      die('Erro de Conexi&oacute;n (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
  11. }
  12.  
  13. $error='';  // Usado para amosar as mensaxes de erro
  14. // *********************************ALTA ************************************************************************//
  15. if (!empty($_POST['btnAlta'])){ // DAMOS DE ALTA A EXAMPLARES E O LIBRO
  16.     foreach ($_POST['lstEstado'] as $indice => $estado){    // Usamos o índice para buscar os datos no resto dos campos
  17.         $prezo = $_POST['txtPrezo'][$indice];
  18.         $imaxe = $_POST['txtImaxe'][$indice];
  19.  
  20.         // Xa temos os datos de cada un dos exemplares
  21.         // Verifacamos que cumpran os criterios de tipo e formato => PRIMEIRO VALIDAMOS. SE SON CORRECTOS TODOS INICIAMOS A OPERACION DE ALTA
  22.         if (!filter_var($estado,FILTER_VALIDATE_INT) || $estado<0){
  23.             $error .= "O estado é incorrecto...";
  24.             break;
  25.         }
  26.         if (filter_var(str_replace(',', '.', $prezo),FILTER_VALIDATE_FLOAT)==false || $prezo<=0 || !preg_match("/[0-9]{1,6}(,[0-9]{1,2})?/",$prezo)){
  27.             $error .= "O prezo é incorrecto...";
  28.             break;
  29.         }
  30.         $imaxe=filter_var($imaxe,FILTER_SANITIZE_STRING);
  31.         echo strlen($imaxe);
  32.         if(((strlen($imaxe))>100) || (strlen($imaxe)==0)) {    // O número de caracteres está establecido na táboa da base de datos
  33.             $error .= "O nome da imaxe é demasiado grande ou non foi posto...";
  34.             break;
  35.         }
  36.         // Gardamos os datos validados para dalos de alta todos xuntos
  37.         $dato['prezo'] = $prezo;
  38.         $dato['imaxe'] = $imaxe;
  39.         $dato['estado'] = $estado;
  40.         $datos[] = $dato;
  41.     }   // Fin do for
  42.    
  43.    
  44.     if(empty($error)){  // Non houbo error, empezamos a operación de alta con transaccións
  45.         $conex->autocommit(false);  // Iniciamos a transcción
  46.        
  47.         // Damos de alta ao libro e obtemos o id xerado
  48.         $titulo = $conex->real_escape_string($_POST['txtTitulo']);
  49.         $proc=sprintf(PROCEDEMENTO_INSERIR_LIBRO,$titulo);
  50.         $proc = sprintf("CALL %s",$proc);
  51.         if ($conex->multi_query($proc)){
  52.             $result = $conex->store_result();
  53.             if ($result && ($result->num_rows>0)){
  54.                  $fila=$result->fetch_assoc();
  55.                  switch($fila['valor_devolto']){
  56.                      case -1:
  57.                          $error = "Ese libro xa est&aacute; dado de alta na base de datos...<br />";
  58.                          break;
  59.                      default:
  60.                          $id_libro = $fila['valor_devolto'];    // XA TEMOS O ID AUTONUMERICO DO LIBRO
  61.                  }
  62.                  $result->free();
  63.                  while($conex->next_result() && $conex->store_result());
  64.             }
  65.             else{   // Sempre ten que devolver algo como temos implementado o proc.
  66.                  $error = 'Houbo un erro na chamada á base de datos<br />';
  67.             }
  68.         }
  69.         else{
  70.             $error = 'Houbo un erro na chamada á base de datos <br/>';
  71.         }        
  72.        
  73.        
  74.         if(empty($error)){  // Non houbo erro e temos o id do libro xerado
  75.             // Temos que dar de alta aos exemplares
  76.             $cont_num_exemplar=1;   // Número de exemplar
  77.             foreach ($datos as $dato){
  78.                 $prezo = $conex->real_escape_string($dato['prezo']);
  79.                 $estado = $conex->real_escape_string($dato['estado']);
  80.                 $imaxe = $conex->real_escape_string($dato['imaxe']);
  81.                 $proc=sprintf(PROCEDEMENTO_INSERIR_EXEMPLAR,$id_libro,$cont_num_exemplar,$imaxe,$prezo,$estado);
  82.                 $proc = sprintf("CALL %s",$proc);
  83.                 if ($conex->multi_query($proc)){
  84.                     $result = $conex->store_result();   // Neste caso o procedemento non devolve nada => vale 'false'
  85.                     if ($conex->errno>0){
  86.                          $error .= 'Houbo un erro na alta dos exemplares';
  87.                          break;  //Saimos do for
  88.                     }
  89.                     while($conex->next_result() && $conex->store_result());
  90. }                  
  91.                 else{
  92.                     $error = 'Houbo un erro na chamada á base de datos:' . $conex->error;
  93.                     break;  //Saimos do for
  94.                 }        
  95.                 $cont_num_exemplar+=1;
  96.             }   // Fin do For por cada exemplar
  97.         }
  98.         if (!empty($error)){    // Fallos nas operacións
  99.             $conex->rollback();
  100.         }
  101.         else {  // Todo correcto
  102.             $conex->commit();
  103.         }
  104.        
  105.     }   // Fin de (empty($error)
  106. }
  107. // *********************************FIN DE ALTA ************************************************************************//
  108.  
  109. ?>
  110.  
  111.  
  112. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  113.     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  114. <html xmlns="http://www.w3.org/1999/xhtml" lang="es" xml:lang="es">
  115.     <head>
  116.         <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  117.         <title>Transaccións</title>
  118.     </head>
  119.  
  120.     <body>
  121.         <?php
  122.             $titulo='';
  123.             $num_exemplares='';
  124.             if (!empty($_POST['btnEnviar'])) {  // Prememos o botón que indica o título e o número de exemplares
  125.                 $titulo = filter_var($_POST['txtTitulo'],FILTER_SANITIZE_STRING);
  126.                 if (strlen($titulo)>200){   // Non pode ter unha lonxitude maior que o campo da base de datos
  127.                     $error='A lonxitude do título non pode ser maior que 200 caracteres...<br />';
  128.                 }
  129.                 $num_exemplares = $_POST['txtNumExemplares'];
  130.                 if (!filter_var($num_exemplares,FILTER_VALIDATE_INT) || $num_exemplares > 9 || $num_exemplares < 1){
  131.                     $error.="O número de exemplares ten que ser numérico e non pode ser maior que 9<br />";
  132.                 }
  133.                 if (strlen($titulo)==0) {  
  134.                     $error.='O título é obrigatorio...<br />';
  135.                 }
  136.             }
  137.         ?>
  138.        
  139.         <?php
  140.             $inhabilitar='';
  141.             if (!empty($_POST['btnEnviar']) && (empty($error))) {   // Facemos que a caixa de texto e todo e demais quede inhabilitado
  142.                 $inhabilitar="disabled='disabled'";                  // Unha vez enviamos os datos do titulo e num exemplares
  143.             }
  144.         ?>
  145.         <form action="<?php echo $_SERVER['PHP_SELF'] ?>" method='post'>
  146.             <div>Titulo Libro: <input <?php echo $inhabilitar ?> <?php printf(" value='%s'",$titulo); ?> type='text' size='100' maxlength="300" name='txtTitulo' /></div>
  147.             <div>Num. Exemplares: <input <?php echo $inhabilitar ?>  <?php printf(" value='%s'",$num_exemplares); ?> type='number' min='1' max='9' size="1" maxlength="1" name='txtNumExemplares' /> (&lt;10) </div>
  148.             <input <?php echo $inhabilitar ?> type="submit" value='Enviar' name='btnEnviar' />    
  149.         </form>
  150.  
  151.         <?php
  152.             if (!empty($error)) {   // En caso de atopar erros non amosa nada máis que a mensaxe
  153.                 die("<h4>$error</h4>");
  154.             }
  155.         ?>
  156.        
  157.        
  158.         <?php
  159.         if ($titulo && $num_exemplares){    // Enviamos datos
  160.             // NON IMOS A FACER UNHA CONSULTA A TABOA ESTADOS POR CADA UN DOS EXEMPLARES. NON TEN SENTIDO.
  161.             // PODERIAMOS PERCORRER O ARRAY DE RESULTADOS E AO REMATAR VOLVER AO PRINCIPIO: http://php.net/manual/es/mysqli-result.data-seek.php
  162.             $proc = sprintf("CALL %s",PROCEDEMENTO_CONSULTAR_ESTADOS);
  163.             if ($conex->multi_query($proc)){
  164.                 $result = $conex->store_result();
  165.                 if ($result && ($result->num_rows>0)){
  166.                     while($valor = $result->fetch_assoc()){
  167.                         $estados[]=$valor;
  168.                     }
  169.                     $result->free();
  170.                     while($conex->next_result() && $conex->store_result());
  171.                 }
  172.             }
  173.             printf("<form action=\"%s\" method='post'>",$_SERVER['PHP_SELF']);
  174.             printf("<input type='hidden' value='%s' name='txtTitulo' />",$_POST['txtTitulo']); // Como temos dous formularios, temos que gardar o texto do título nun campo oculto
  175.             printf ("<table>");
  176.             for($cont=0;$cont<$num_exemplares;$cont++){
  177.                 echo "<th>Estado</th><th>Imaxe</th><th>Prezo</th>";
  178.                 echo "<tr>";
  179.                 echo "<td>";    // ESTADO
  180.                     printf ("<select name='lstEstado[]>");  // Poñemos o nome en forma de array xa que imos recibir varios exemplares con datos.
  181.                     foreach ($estados as $estado){
  182.                         printf("<option value='%d'>%s</option>",$estado['id_estado'],$estado['descripcion']);
  183.                     }
  184.                     printf ("</select>");
  185.                 echo "</td>";
  186.                 echo "<td>";    // IMAXE
  187.                     printf("<input type='text' size='50' maxlength='70' name='txtImaxe[]' />");
  188.                 echo "</td>";
  189.                 echo "<td>";    // PREZO
  190.                     printf("<input type='text' size='9' maxlength='9' name='txtPrezo[]' /> Euros (XXXXXX,YY)");
  191.                 echo "</td>";
  192.                 echo "</tr>";
  193.             }
  194.            
  195.             printf ("</table>");
  196.             printf("<input type='submit' value='ALTA' name='btnAlta' />");    
  197.             printf("<input type='reset' value='CANCELAR' name='btnCancelar' />");    
  198.             printf("</form>");
  199.            
  200.         }
  201.         ?>
  202.     </body>    
  203. </html>



  • Exercicio proposto
Tedes neste enlace da WIKI a forma de subir unha imaxe ao servidor. Modificade o código anterior para facelo.


  • Exercicio proposto
Amplía a funcionalidade da páxina anterior para poder asociar a os autores cos libros na mesma operación de alta.
Para iso fai que os autores se carguen nunha lista (<select con size en HTML>) ordenados alfabeticamente e que se poidan seleccionar varios.
En caso de que os autores non estean dados de alta, poderás premer nun enlace que abrirá unha xanela nova (sen o toolbar, botóns,...) e que permitirá dar de alta a un novo autor. Ao pechar a xanela deberán refrescarse os datos da lista para amosar o novo autor, mantendo os datos previamente introducidos.



Variante Caso práctico: Transaccións con control de excepcións

  • É o mesmo exercicio que no punto anterior, pero utilizando Excepcións dentro do bloque de instrucións que conforman a transacción.

Arquivo Alta_Libro_Exemplares.php

  1. <?php
  2.  
  3. require ('/var/www/confBD/bd.inc.php');
  4. //error_reporting(0);
  5.  
  6. $conex = new mysqli(SERVIDOR,USUARIO,PASSWORD,BD);
  7. $conex->set_charset('utf8');
  8.  
  9. if ($conex->connect_error){
  10.      die('Erro de Conexi&oacute;n (' . mysqli_connect_errno() . ') ' . mysqli_connect_error());
  11. }
  12.  
  13. $error='';  // Usado para amosar as mensaxes de erro
  14. // *********************************ALTA ************************************************************************//
  15. if (!empty($_POST['btnAlta'])){ // DAMOS DE ALTA A EXAMPLARES E O LIBRO
  16.     foreach ($_POST['lstEstado'] as $indice => $estado){    // Usamos o índice para buscar os datos no resto dos campos
  17.         $prezo = $_POST['txtPrezo'][$indice];
  18.         $imaxe = $_POST['txtImaxe'][$indice];
  19.  
  20.         // Xa temos os datos de cada un dos exemplares
  21.         // Verifacamos que cumpran os criterios de tipo e formato => PRIMEIRO VALIDAMOS. SE SON CORRECTOS TODOS INICIAMOS A OPERACION DE ALTA
  22.         if (!filter_var($estado,FILTER_VALIDATE_INT) || $estado<0){
  23.             $error .= "O estado é incorrecto...";
  24.             break;
  25.         }
  26.         if (filter_var(str_replace(',', '.', $prezo),FILTER_VALIDATE_FLOAT)==false || $prezo<=0 || !preg_match("/[0-9]{1,6}(,[0-9]{1,2})?/",$prezo)){
  27.             $error .= "O prezo é incorrecto...";
  28.             break;
  29.         }
  30.         $imaxe=filter_var($imaxe,FILTER_SANITIZE_STRING);
  31.         if(((strlen($imaxe))>100) || (strlen($imaxe)==0)) {    // O número de caracteres está establecido na táboa da base de datos
  32.             $error .= "O nome da imaxe é demasiado grande ou non foi posto...";
  33.             break;
  34.         }
  35.         // Gardamos os datos validados para dalos de alta todos xuntos
  36.         $dato['prezo'] = $prezo;
  37.         $dato['imaxe'] = $imaxe;
  38.         $dato['estado'] = $estado;
  39.         $datos[] = $dato;
  40.     }   // Fin do for
  41.    
  42.    
  43.     if(empty($error)){  // Non houbo error, empezamos a operación de alta con transaccións
  44.         $conex->autocommit(false);  // Iniciamos a transcción
  45.         try{
  46.                 // Damos de alta ao libro e obtemos o id xerado
  47.            $titulo = $conex->real_escape_string($_POST['txtTitulo']);
  48.            $proc=sprintf(PROCEDEMENTO_INSERIR_LIBRO,$titulo);
  49.            $proc = sprintf("CALL %s",$proc);
  50.            if ($conex->multi_query($proc)){
  51.                $result = $conex->store_result();
  52.                if ($result && ($result->num_rows>0)){
  53.                     $fila=$result->fetch_assoc();
  54.                     switch($fila['valor_devolto']){
  55.                         case -1:
  56.                             throw new Exception("Ese libro xa est&aacute; dado de alta na base de datos...<br />", 1);  // Co 1 (true) indicamos que debemos facer rollback
  57.                         default:
  58.                             $id_libro = $fila['valor_devolto'];    // XA TEMOS O ID AUTONUMERICO DO LIBRO
  59.                     }
  60.                     $result->free();
  61.                     while($conex->next_result() && $conex->store_result());
  62.                }
  63.                else{   // Sempre ten que devolver algo como temos implementado o proc.
  64.                    throw new Exception('Houbo un erro na chamada á base de datos<br />',1);
  65.                }
  66.            }
  67.            else{
  68.                throw new Exception('Houbo un erro na chamada á base de datos <br/>',1);
  69.            }        
  70.  
  71.  
  72.             // Temos que dar de alta aos exemplares
  73.             $cont_num_exemplar=1;   // Número de exemplar
  74.             foreach ($datos as $dato){
  75.                 $prezo = $conex->real_escape_string($dato['prezo']);
  76.                 $estado = $conex->real_escape_string($dato['estado']);
  77.                 $imaxe = $conex->real_escape_string($dato['imaxe']);
  78.  
  79.                 $proc=sprintf(PROCEDEMENTO_INSERIR_EXEMPLAR,$id_libro,$cont_num_exemplar,$imaxe,$prezo,$estado);
  80.                 $proc = sprintf("CALL %s",$proc);
  81.                 if ($conex->multi_query($proc)){
  82.                     $result = $conex->store_result();   // Neste caso o procedemento non devolve nada => vale 'false'
  83.                     if ($conex->errno>0){
  84.                          throw new Exception('Houbo un erro na alta dos exemplares',1);
  85.                     }
  86.                     while($conex->next_result() && $conex->store_result());
  87.                  }                  
  88.                 else{
  89.                     throw new Exception('Houbo un erro na chamada á base de datos:' . $conex->error,1);  // Ao usuario non debemos amosarlle as mensaxes do xestor. Estamos na fase de desenvolvemento
  90.                 }        
  91.                 $cont_num_exemplar+=1;
  92.             }   // Fin do For por cada exemplar
  93.            
  94.            
  95.             $conex->commit();       // Todo correcto, confirmamos a transacción
  96.             $error="Libro e examplares dados de alta correctamente...";
  97.         } catch (Exception $ex) {
  98.             if ($ex->getCode()==1){
  99.                 $conex->rollback();
  100.             }
  101.             $error = $ex->getMessage();
  102.            
  103.         }    
  104.        
  105.        
  106.     }   // Fin de (empty($error)
  107. }
  108. // *********************************FIN DE ALTA ************************************************************************//
  109.  
  110. ?>
  111.  
  112.  
  113. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  114.     "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  115. <html xmlns="http://www.w3.org/1999/xhtml" lang="es" xml:lang="es">
  116.     <head>
  117.         <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  118.         <title>Transaccións</title>
  119.     </head>
  120.  
  121.     <body>
  122.         <?php
  123.             $titulo='';
  124.             $num_exemplares='';
  125.             if (!empty($_POST['btnEnviar'])) {  // Prememos o botón que indica o título e o número de exemplares
  126.                 $titulo = filter_var($_POST['txtTitulo'],FILTER_SANITIZE_STRING);
  127.                 if (strlen($titulo)>200){   // Non pode ter unha lonxitude maior que o campo da base de datos
  128.                     $error='A lonxitude do título non pode ser maior que 200 caracteres...<br />';
  129.                 }
  130.                 $num_exemplares = $_POST['txtNumExemplares'];
  131.                 if (!filter_var($num_exemplares,FILTER_VALIDATE_INT) || $num_exemplares > 9 || $num_exemplares < 1){
  132.                     $error.="O número de exemplares ten que ser numérico e non pode ser maior que 9<br />";
  133.                 }
  134.                 if (strlen($titulo)==0) {  
  135.                     $error.='O título é obrigatorio...<br />';
  136.                 }
  137.             }
  138.         ?>
  139.        
  140.         <?php
  141.             $inhabilitar='';
  142.             if (!empty($_POST['btnEnviar']) && (empty($error))) {   // Facemos que a caixa de texto e todo e demais quede inhabilitado
  143.                 $inhabilitar="disabled='disabled'";                  // Unha vez enviamos os datos do titulo e num exemplares
  144.             }
  145.         ?>
  146.         <form action="<?php echo $_SERVER['PHP_SELF'] ?>" method='post'>
  147.             <div>Titulo Libro: <input <?php echo $inhabilitar ?> <?php printf(" value='%s'",$titulo); ?> type='text' size='100' maxlength="300" name='txtTitulo' /></div>
  148.             <div>Num. Exemplares: <input <?php echo $inhabilitar ?>  <?php printf(" value='%s'",$num_exemplares); ?> type='number' min='1' max='9' size="1" maxlength="1" name='txtNumExemplares' /> (&lt;10) </div>
  149.             <input <?php echo $inhabilitar ?> type="submit" value='Enviar' name='btnEnviar' />    
  150.         </form>
  151.  
  152.         <?php
  153.             if (!empty($error)) {   // En caso de atopar erros non amosa nada máis que a mensaxe
  154.                 die("<h4>$error</h4>");
  155.             }
  156.         ?>
  157.        
  158.        
  159.         <?php
  160.         if ($titulo && $num_exemplares){    // Enviamos datos
  161.             // NON IMOS A FACER UNHA CONSULTA A TABOA ESTADOS POR CADA UN DOS EXEMPLARES. NON TEN SENTIDO.
  162.             // PODERIAMOS PERCORRER O ARRAY DE RESULTADOS E AO REMATAR VOLVER AO PRINCIPIO: http://php.net/manual/es/mysqli-result.data-seek.php
  163.             $proc = sprintf("CALL %s",PROCEDEMENTO_CONSULTAR_ESTADOS);
  164.             if ($conex->multi_query($proc)){
  165.                 $result = $conex->store_result();
  166.                 if ($result && ($result->num_rows>0)){
  167.                     while($valor = $result->fetch_assoc()){
  168.                         $estados[]=$valor;
  169.                     }
  170.                     $result->free();
  171.                     while($conex->next_result() && $conex->store_result());
  172.                 }
  173.             }
  174.             printf("<form action=\"%s\" method='post'>",$_SERVER['PHP_SELF']);
  175.             printf("<input type='hidden' value='%s' name='txtTitulo' />",$_POST['txtTitulo']); // Como temos dous formularios, temos que gardar o texto do título nun campo oculto
  176.             printf ("<table>");
  177.             for($cont=0;$cont<$num_exemplares;$cont++){
  178.                 echo "<th>Estado</th><th>Imaxe</th><th>Prezo</th>";
  179.                 echo "<tr>";
  180.                 echo "<td>";    // ESTADO
  181.                     printf ("<select name='lstEstado[]>");  // Poñemos o nome en forma de array xa que imos recibir varios exemplares con datos.
  182.                     foreach ($estados as $estado){
  183.                         printf("<option value='%d'>%s</option>",$estado['id_estado'],$estado['descripcion']);
  184.                     }
  185.                     printf ("</select>");
  186.                 echo "</td>";
  187.                 echo "<td>";    // IMAXE
  188.                     printf("<input type='text' size='50' maxlength='70' name='txtImaxe[]' />");
  189.                 echo "</td>";
  190.                 echo "<td>";    // PREZO
  191.                     printf("<input type='text' size='9' maxlength='9' name='txtPrezo[]' /> Euros (XXXXXX,YY)");
  192.                 echo "</td>";
  193.                 echo "</tr>";
  194.             }
  195.            
  196.             printf ("</table>");
  197.             printf("<input type='submit' value='ALTA' name='btnAlta' />");    
  198.             printf("<input type='reset' value='CANCELAR' name='btnCancelar' />");    
  199.             printf("</form>");
  200.            
  201.         }
  202.         ?>
  203.     </body>    
  204. </html>

Permisos mínimos

  • Sempre debemos ter en mente a seguridade de acceso aos datos gardados nunha base de datos.
Ata o de agora, o usuario que usamos para acceder á base de datos tiña asinado todos os permisos sobre a mesma, pero esta práctica non é aconsellable.
O ideal é que teña asinado os permisos mínimos necesarios para que poida utilizar a aplicación.


  • Cando creamos unha aplicación WEB podemos ter varios supostos no que se refire ao acceso á base de datos.
  • Temos creado en MYSQL un usuario diferente por cada usuario, de tal forma que a cadea de conexión á base de datos a crearemos en base ao usuario/password introducido dende unha páxina PHP.
  • Utilizamos o mesmo usuario MYSQL para acceder á base de datos e temos unha táboa de usuario/password. En función do usuario validado, este terá acceso a diferentes páxinas PHP con diferentes funcionalidades.
  • Utilizamos o mesmo usuario MYSQL para acceder á base de datos e todos os usuarios do sitio web poden ter acceso ás mesmas páxinas.
  • En calquera destes supostos, o usuario que empreguemos para acceder a MYSQL debe ter os permisos mínimos para poder funcionar no noso sitio web.



  • Partimos de que temos creado un usuario en Mysql, que vai ser o usuario que imos utilizar no noso sitio web para conectarnos a MYSQL e queremos darlle os permisos mínimos.


  • CASO 1: A nosa aplicación fai as operacións contra a base de datos empregando sentenzas SQL (SELECT/INSERT/UPDATE/DELETE) deberemos outorgar os permisos e so os permisos necesarios para facer as operacións sobre as táboas da nosa base de datos.
A primeira (grant) serve para outorgar un permiso a un usuario.
A segunda (revoke) serve para quitar un permiso a un usuario.
  • No caso de SELECT e UPDATE, podemos outorgar permisos a nivel de columnas, de tal forma que podemos especificar que columnas dunha táboa vai ter permiso para poder ser seleccionadas ou actualizadas.


Se estamos a utilizar o WorkBench, podemos asociar os permisos graficamente.
Php Perm Minimos 1.jpg
  • Prememos na sección 'User and privileges' (1).
  • Prememos o botón 'Add entry' e escollemos o schema (base de datos) sobre a que queremos outorgar os permisos (2).
  • Aparecerá o schema na lista (3)^.
  • Agora na parte baixa aparecerán os permisos que podemos outorgar. Fixarse que os marcados con (4) son os permisos de SELECT/INSERT/UPDATE/DELETE
  • Se eliximos este 'tipo de permisos' estaremos dando o permiso sobre todas as táboas creadas na base de datos.


  • Se queremos dar un permiso individual a unha táboa usaremos a orde grant:
Nota: A orde grant, cando fai referencia a un obxecto (coma unha táboa) pode indicarse o nome da base de datos e o nome do obxecto desta forma: bd.obxecto
Nos exemplos seguintes omitimos o nome da base de datos e supoñemos que xa está seleccionada (activa) a base de datos sobre a que imos a executar as ordes.
Permiso 'select' sobre unha táboa.
  1. grant select on TABOA to 'novo_usuario';
Permiso 'select' sobre unha columna dunha táboa.
  1. grant select (columna) on TABOA to 'novo_usuario';
Permiso 'update' sobre unha táboa.
  1. grant update on TABOA to 'novo_usuario';
Permiso 'update' sobre unha columna dunha táboa.
  1. grant update (columna) on TABOA to 'novo_usuario';
Para os permisos DELETE e INSERT soamente é necesario poñer na orde grant a operación igual que nos casos anteriores.



  • CASO 2: A nosa aplicación fai as operacións contra a base de datos empregando procedementos almacenados.
  • No caso anterior, aínda que melloramos a seguridade, o usuario aínda ten acceso ás táboas directamente e pode ver a estrutura das mesmas.
Se queremos aumentar a seguridade podemos utilizar procedementos almacenados.


  • Xa vimos anteriormente que empregar ditos procedementos leva consigo:
  • Mellor rendemento xa que as sentenzas xa están compiladas e analizadas polo xestor.
  • Mellor seguridade xa que é imposible facer ataques de inxección de código.
  • Mellor seguridade xa que o usuario non pode ver a estrutura de táboas.
  • Mantemento máis doado, xa que podo cambiar a estrutura de táboas sempre que o procedemento continúe devolvendo o mesmo ao usuario.


  • IMPORTANTE: Se utilizamos este tipo de 'seguridade' xa non necesitamos outorgar ningún tipo de permiso sobre as táboas.
  • O permiso que debemos outorgar para que poida executar un procedemento almacenado é 'EXECUTE'.
  • Podemos facelo como no punto anterior facendo uso do WORKBENCH (neste caso daremos permiso de execución sobre todos os procedementos almacenados).
  • Usar a orde grant:
Dando permiso de execución a todos os procedementos almacenados.
  1. grant execute on *.* to 'novo_usuario'
Dando permiso de execución a un procedemento almacenado.
  1. grant execute on procedure NOME_PROCEDEMENTO to 'novo_usuario'

Optimización: Sentenzas Prepared

  • A idea das sentenzas 'prepared' é a de mandar previamente ao Mysql a sentenza que imos executar pero sen datos.
Despois asociaremos a dita sentenza 'prepared' os datos e a executaremos.
Por exemplo:
  1. select col1 from TABOA1
  2. where col2=?
Neste caso, a sentenza a executar leva un parámetro (o indicado pola interrogación).
Este parámetro se asociará posteriormente e se executará a sentenza. Podemos executar a sentenza moitas veces cambiando o valor do parámetro.


  • Preparar as sentenzas ten as vantaxes:
  • É máis rápido que enviar as sentenzas da forma 'tradicional' sobre todo cando repetimos a mesma sentenza (con valores diferentes) varias veces. Isto é debido a que cando 'preparamos' a sentenza a estamos a enviar ao xestor MYSQL o cal xa fai as operacións de análise da sentenza e outras optimizacións. Isto soamente o terá que facer unha vez.
  • Maior seguridade, xa que fai imposible a inxección de código (o mesmo que pasa cos procedementos almacenados)


PHP

Enlace.


MySQL

Enlace.

  • Dende o propio xestor tamén se poden 'executar' sentenzas 'prepared'.
  • Esta forma pode sernos moi útil se queremos executar sentenzas SQL que se cren dinamicamente dentro dun procedemento almacenado.
  • Por exemplo, se temos unha páxina de busca en base a varios criterios (por exemplo, nome e idade). A sentenza SQL terá unha parte WHERE que pode ser:
  • nome=valor1
  • idade=valor2
  • nome=valor1 and idade=valor2
Vai depender se enviamos ou non o dato teremos que executar unha instrución ou outra.
En vez de ter 3 instrucións SQL dentro do procedemento almacenado en base a se enviamos ou non o dato, podemos 'construír' a sentenza SQL coma unha cadea e cando a teñamos executala con 'PREPARED'
  1. DELIMITER $$
  2.  
  3. CREATE DEFINER=`root`@`localhost` PROCEDURE `proba`(_apelido varchar(100),_nome varchar(50))
  4. BEGIN
  5.        
  6.         set @cadea = 'select nome,apelido from AUTORES where 1=1';
  7.         if (_apelido is not null) then
  8.                 set @cadea=concat(@cadea,' and apelido=','\'', _apelido,'\'');
  9.         end if;
  10.         if (_nome is not null) then
  11.                 set @cadea=concat(@cadea,' and nome=','\'', _nome,'\'');
  12.         end if;
  13.  
  14.         prepare stmt from @cadea;
  15.  
  16.         execute stmt;
  17.  
  18.         deallocate prepare stmt;
  19.  
  20.  
  21. END
  • Neste caso o procedemento buscará nunha táboa AUTORES por un nome, apelido ou nome e apelido concreto. En caso de enviar null nos dous parámetros, seleccionará todas as filas.
Fixarse nos espazos en branco cando concatenedes xa que tedes que formar a frase: WHERE 1=1 and cond=valor. Entre o 1=1 e a condición ten que ir un espazo en branco e o mesmo se engadides máis condicións.

Gardando campos a protexer na base de datos

  • En moitas ocasións imos necesitar gardar nunha base de datos información que non queremos que sexa 'visible' en caso de que alguén poida acceder a nosa base de datos.
  • Un exemplo típico é o das contrasinais dos usuarios do noso sitio web, no caso de implantar un sitio web cunha validación de usuarios.
Como normas, nunca debe estar baseada nun dicionario.
Debe levar caracteres alfanuméricos, maiúsculas e minúsculas e algún carácter non alfanumérico.
Debe ser de a lo menos 10 ou máis caracteres


  • Para gardar a información dunha forma segura temos que aplicar un algoritmo de hash.
En PHP temos dispoñible a partires da versión 5, a función de hash.
  1. <?php
  2. echo hash('ripemd160', 'The quick brown fox jumped over the lazy dog.');
  3. ?>
Dará como resultado:
  1. ec457d0a974c48d5685a7efa03d137dc8bbde7e3


  • En PHP temos funcións de hash específicas dun tipo de algoritmo. Por exemplo a función md5.
  1. <?php
  2. echo md5('The quick brown fox jumped over the lazy dog.')           // As dúas funcións devolven o mesmo
  3. echo hash('md5', 'The quick brown fox jumped over the lazy dog.');  // As dúas funcións devolven o mesmo
  4. ?>


  • Pero esta forma de 'tratar' a información non chega, xa que se podería descubrir o valor orixinal pola forza bruta.
Para complicalo un pouco máis debemos facer uso de SALT'.
  • Salt é unha cadea que se aplica no proceso de hash e fai que sexa practicamente imposible utilizar mecanismos de forza bruta ou Táboas Rainbow para obter a cadea orixinal.


  • Exemplo de como utilizar salt na función md5.
  1. <?php
  2. $password='q121ahgsha..'; // Password introducida polo usuario. Viría dun formulario con $_POST['txtPassword'].
  3. $cadeaSalt='unha cadea ao chou';
  4.  
  5. echo md5($cadeaSalt,$password)
  6. ?>
Cabe sinalar que se a contrasinal é débil, é posible chegar a rompela, polo que é moi importante que a contrasinal sexa 'forte'.
A cadea SALT pode ser calquera cadea e mellor que non estea baseada en ningún dicionario.


Opción recomendada

  1. $password = password_hash('contrasinal_a_codificar', PASSWORD_DEFAULT);
Codifica a contrasinal utilizando o algoritmo bcrypt e o resultado é unha cadea de 60 caracteres de lonxitude. Seguramente en versións posteriores, o algoritmo vai devolve lonxitudes máis grandes, polo que é recomendable gardar a contrasinal nun campo de 256 caracteres de lonxitede.
Esta forma de codificar fai que o valor SALT cambie de cada vez.


O resultado será o valor que teremos que gardar na base de datos.


  • Para realizar o proceso contrario, e comprobar se o password introducido por un usuario se corresponde con o gardado na base de datos, teremos que facer uso da función password_verify().
  1. $orixinal = 'password_introdicada_polo_usuario';
  2. $codificado = '2y$10$ETpCoo11Gd3w2kx4x1me1OQtx.p67c6eRzg6OSRgPxUsBGaQnff4W';
  3.  
  4. $iguais = password_verify($orixinal, $codificado);
  5.  
  6. if ($iguais) {
  7.     echo 'Password correcto';
  8. } else {
  9.     echo 'Password incorrecto';
  10. }








-- Ángel D. Fernández González -- (2017).