PHP Operacións BD Mysql

Sumario

1 Introdución
2 Conexión
- 2.1 Gardando os datos de conexión nun lugar seguro
- 2.2 O xogo de caracteres
3 Creando as táboas en Mysql
- 3.1 Regras de nomeado de táboas e columnas
4 Consulta
5 Alta
6 Baixa
7 Modificación
8 Transaccións
9 Permisos mínimos

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:

Interfaz orientada a obxectos
Soporte para declaracións preparadas.
Soporte para Transaccións

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:

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.

Utilizando procedementos: mysqli_connect
Utilizando obxectos: mysql::_construct

Sintaxe (se indica a sintaxe do construtor e polo tanto será equivalente a facer un 'new' da clase mysqli):

1 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:

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:

Facendo uso da constante __DIR__.
Facendo uso do array global $_SERVER e dentro deste a entrada $_SERVER['DOCUMENT_ROOT'.

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).

Creando o cartafol onde se vai gardar o arquivo de configuración
Facemos un cd /var/www. Creamos o cartafol: sudo mkdir confBD. Movemos o arquivo de include creado no paso anterior ao cartafol: mv /cartafol_arquivo_include/bd.inc.php /var/www/confBD.
Cambiamos os permisos: sudo chmod -R 550 /var/www/confBD e cambiamos o propietario: sudo chown -R www-data:www-data /var/www/confBD. Nota: O usuario e grupo pode variar dependendo do S.O. En Linux podedes sabelo mirando este enlace da wiki. En Windows o podedes saber neste enlace.

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.

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

Unha vez temos a base de datos teremos que facer o Modelo EE/R, pasalo ao Modelo Relacional e facer un Proceso de Normalización ata polo menos a forma normal de Boyce-Codd.

Imos implementar unha base de datos para xestionar a venda de libros de segunda mano.

O modelo EE/R é o seguinte:

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 impplementando 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`,`examplar_id`),
  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,0),(2,0);

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/

Nome: amis.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.

Consulta

Nota: Na cadea de conexión xa indicamos a base de datos á que queríamos conectarnos.

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 (!$mysqli->select_db('OutraBD')){
5      die('Erro ao cambiar de base de datos');
6 }

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.

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.

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.

int $mysqli_result->num_rows: Devolve o número de filas devoltas en caso de realizar unha consulta.
array mysqli_result::fetch_assoc(void): Devolve nun array asociativo unha das filas do resultado da consulta e 'pasa' á fila seguinte. Cando remate e xa non teña máis filas devolve NULL.

Aplicado ao exemplo que estamos a seguir:

 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:

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.

Imos facer agora unha modificación e amosar os datos nunha combobox (<select> en html), de tal forma que cando o usuario escolla un libro, amose os exemplares do mesmo.

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->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>

Resultado:

Como vemos no código (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...

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.

Seguindo con exemplo, imos facer que cando o usuario seleccione un libro 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.
Facer que cando o usuario escolla un libro 'active' o formulario e polo tanto provoque o 'submit' do mesmo.
Manter seleccionado o libro na combo.

Nota: Ao principio ides ter problema de tipo 'non amosa ningún dato'. O primeiro que tedes que descargar é 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.

Definimos no arquivo bd.inc.php:

A consulta que vai consultar os exemplares dun libro seleccionado:

define ('CONSULTAR_EXEMPLARES_POR_LIBRO','select libro_id,id_exemplar,imaxe,prezo,estado_id from EXEMPLARES INNER JOIN LIBROS ON (LIBROS.id_libro=EXEMPLARES.libro_id) where id_libro=%d order by titulo,estado_id');

Como vemos imos enviar o código por medio dun parámetro de tipo numérico e utilizando 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->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 INNER JOIN LIBROS ON (LIBROS.id_libro=EXEMPLARES.libro_id) where id_libro=%d order by titulo,estado_id');

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         <form action="<?php echo $_SERVER['PHP_SELF'] ?>" method='post'>
25 
26         <?php        
27         $result=$conex->query(CONSULTAR_LIBROS);
28         if ($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 LIBROS ON (LIBROS.id_libro=EXEMPLARES.libro_id)'
3         .                 ' INNER JOIN ESTADOS ON (ESTADOS.id_estado=EXEMPLARES.estado_id)'
4         . ' where id_libro=%d order by titulo,desc_estado');

Arquivo: Consultar_Libros.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).

Consulta enviando parámetros de tipo cadea

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

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 $ciudad = "'s Hertogenbosch";
 2 
 3 /* esta consulta fallará debido a que no escapa $ciudad */
 4 if (!$mysqli->query("INSERT into miCiudad (Name) VALUES ('$ciudad')")) {
 5     printf("Error: %s\n", $mysqli->sqlstate);
 6 }
 7 
 8 $ciudad = $mysqli->real_escape_string($ciudad);
 9 
10 /* esta consulta con $ciudad escapada funcionará */
11 if ($mysqli->query("INSERT into miCiudad (Name) VALUES ('$ciudad')")) {
12     printf("%d fila insertada.\n", $mysqli->affected_rows);
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:

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 Busqueda_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:

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.

Máis información neste enlace.

O primeiro que temos que facer é crear o procedemento almacenado.
A orde SQL é CREATE PROCEDURE.

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:

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 `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...

Debemos cambiar a chamada a 'query' pola chamada 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).

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->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 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 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 son unha combinación dos dous anteriores.

Máis información neste enlace.

Seguindo có noso exemplo...imos crear unha nova páxina de nome Busqueda_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 Busqueda_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.

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

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `CONSULTAR_NUM_LIBROS`(OUT num_libros int) BEGIN SELECT COUNT(*) INTO num_libros FROM LIBROS;

END $$

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>

PHP Operacións BD Mysql

Sumario

Introdución

Conexión

Gardando os datos de conexión nun lugar seguro

O xogo de caracteres

Creando as táboas en Mysql

Regras de nomeado de táboas e columnas

Consulta

Consulta enviando parámetros de tipo numérico

Consulta enviando parámetros de tipo cadea

Consulta chamando a procedementos almacenados

Consulta chamando a procedementos almacenados con parámetros de entrada

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

Alta

Baixa

Modificación

Transaccións

Permisos mínimos

Menú de navegación

Herramientas personales

Espacios de nombres

Variantes

Vistas

Más

Buscar

Navegación

Herramientas

Imprimir/exportar